Command Mongodb

CommandMongoDB.md

Command MongoDB

- aggregate
- $unwind
- $match -> where
    - $regex -> like
    - $or -> ||
    - $and -> &&
    - $eq -> =
    - $ne -> !=
    - $nin -> NOT IN
    - $in -> IN
- cond -> condition
- gt -> > มากกว่า
- lt -> < น้อยกว่า
- gte -> >= มากกว่าเท่ากับ
- lte -> <= น้อยกว่าเท่ากับ
- addFields
- $concat -> ต่อคำ
- $lookup -> Join
    - let -> สร้างตัวแปร
    - $expr -> compare ข้อมูล
- $project -> select, rename (Fields)
- $group

aggreate

db.company.aggregate();

$unwind

db.company.aggregate([
    {
        $unwind: "$USER"
    }
]);

$match

db.company.aggregate([
{
    $unwind: "$USER"
},
{
    $match: {
        "USER.FIRST_NAME": "ไดแอน"
    }
}
]);

$regex

db.company.aggregate([
{
    $unwind: "$USER"
},
{
    $match: {
       "USER.FIRST_NAME": {
			'$regex': 'ไดมอน',
			'$options': 'i'
		}
    }
}
]);

$or, $in

db.company.aggregate([
{
    $unwind: "$USER"
},
{
    $match: {
       $or: [
            {
                "USER.FIRST_NAME": {$in: ["ไดแอน", "dds"]}
            },
            {
                "USER.FIRST_NAME": "my tree"
            }
        ]
    }
}
]);

$addFields

db.company.aggregate([
    {
        $unwind: "$USER"
    },
    {
		$addFields: {"newNameField": "$USER.EMAIL"}
	}
]);

$concat

db.company.aggregate([
    {
        $unwind: "$USER"
    },
    {
        $addFields: {
            "FULLNAME": {
                $concat: [
                   '$USER.FIRST_NAME',
                    ' ',
                    '$USER.LAST_NAME'
                ]
            }
        }
    }
]);

$project

db.company.aggregate([
    {
        $unwind: "$USER"
    },
    {
        $project: {
            _id: 1,
            company_name: "$COMPANY_NAME"
        }
    }
]);

$lookup

db.company.aggregate([
    {
        $unwind: "$USER"
    },
	{
		$lookup: {
			from: "c1",
			localField: "USER.EMP_CODE",
			foreignField: "emp_code",
			as: "c1_data"
		}
	},
	{
		$match: {
			"c1_data": {$ne : []}
		}
	},
	{
		$project: {
			_id: 1,
			company_name: "$COMPANY_NAME"
			c1: { $arrayElemAt: ["$c1_data", 0] }
		}
	}
]);

$lookup [compare data]

db.company.aggregate([
    {
        $unwind: "$USER"
    },
    {
        $lookup: {
            from: "emp",
            let: {
                emp_code: "$USER.EMP_CODE" // create new variable, USER.EMP_CODE
            },
            pipeline: [
                {
                    $match: {
                        text: "xxxxx" // emp.text
                    }
                },
                {
                    $unwind: "$data" // newEmp.data
                },
                {
                    $match: {
                        $expr: {
                            $and: [
                                {
                                    $eq: ["$data.emp_id", "$$emp_code"] // $eq: [localField, let emp_code]
                                }
                            ]
                        }
                    }
                }
            ],
            as: "newEmp"
        }
    },
    {
        $group: {
            _id: {
                'COMPANY_NAME': "$COMPANY_NAME",
				EMAIL: "$EMAIL"
            },
			total: { $sum: 1 },
			total_salary: { $sum: "$USER.salary" },
			total_salaryx: {
                $sum: {
                    $cond: [{
                        $gt: ["$USER.salary", 12000]
                    }, 1, 0]
                }
            },
            total_salaryz: {
				$sum: {
					$cond: {
						if: {
							$gt: ["$USER.salary", 12000]
						},
						then: 1,
						else: 0
					}
				}
			},
			data: { $push: "$USER.EMP_CODE" }
        }
    },
    {
		$project: {
			_id: 0,
			cn: "$_id.COMPANY_NAME",
			email: "$_id.EMAIL",
			total: 1,
			total_salary: 1,
		}
	}
]);

updateMany

db.test.updateMany({
	"roomid": "b184824b-aee1-bd02-9447-b3203fd3ec80",
	"sid": "IGEN007",
	"rid": "IGEN004"
},
{
	"$set": { "read": true, "readtime": new Date() }
})