https://docs.mongodb.com/v4.4/reference/operator/aggregation-pipeline
条件过滤和表连接
db.user.aggregate([
{ $match: { "uuid": "b040904e-8a30-11ec-917a-17ee5f1bcd74" } },
{ $lookup: { from: "team", localField: "uuid", foreignField: "user_uuid", as: "team" } },
{ $lookup: { from: "role", localField: "uuid", foreignField: "user_uuid", as: "role" } }
]).pretty()
/*
{
"_id": ObjectId("6205d104bb59bd04fe0bd0d1"),
"uuid": "b040904e-8a30-11ec-917a-17ee5f1bcd74",
"user_name": "user10",
"real_name": "user10",
"mobile": "13900000010",
"email": "user10@abc.com",
"password": "$2a$10$j3uhq9MetVT16B7i0C5Sve936iiB6FpcG0wghJ85Px7wzdEcqUicC",
"create_at": ISODate("2022-02-11T02:59:16.599Z"),
"update_at": ISODate("2022-02-11T02:59:16.599Z"),
"team": [
{
"_id": ObjectId("620b40de72a79c20ad4d8dc2"),
"uuid": "a6b00dca-8adf-11ec-846a-834d4eb728d2",
"team_name": "第1团队",
"describe": "第1团队",
"user_uuid": [
"b040904e-8a30-11ec-917a-17ee5f1bcd74",
"b047d96c-8a30-11ec-86bb-47637cc95add",
"b0491d68-8a30-11ec-bb2c-af6b5601ac06",
"b04a60a6-8a30-11ec-93f7-43f76283897b"
],
"create_at": ISODate("2022-02-15T05:57:50.472Z"),
"update_at": ISODate("2022-02-15T05:57:50.472Z")
}
],
"role": [
{
"_id": ObjectId("62061fa43a7761790c29f26d"),
"uuid": "0fe1b9f2-8ae4-11ec-a5b4-87315c29590e",
"team_name": "团队经理",
"describe": "读写",
"user_uuid": [
"b040904e-8a30-11ec-917a-17ee5f1bcd74",
"b0533aa0-8a30-11ec-b9c3-dbc28ee83f8c"
],
"create_at": ISODate("2022-02-11T08:34:44.518Z"),
"update_at": ISODate("2022-02-11T08:34:44.518Z")
}
]
}
*/
数组变更为对象
db.user.aggregate([
{ $match: { "uuid": "b040904e-8a30-11ec-917a-17ee5f1bcd74" } },
{ $lookup: { from: "team", localField: "uuid", foreignField: "user_uuid", as: "team" } },
{ $lookup: { from: "role", localField: "uuid", foreignField: "user_uuid", as: "role" } },
{ $unwind : "$team" },
{ $unwind : "$role" }
]).pretty()
/*
{
"_id": ObjectId("6205d104bb59bd04fe0bd0d1"),
"uuid": "b040904e-8a30-11ec-917a-17ee5f1bcd74",
"user_name": "user10",
"real_name": "user10",
"mobile": "13900000010",
"email": "user10@abc.com",
"password": "$2a$10$j3uhq9MetVT16B7i0C5Sve936iiB6FpcG0wghJ85Px7wzdEcqUicC",
"create_at": ISODate("2022-02-11T02:59:16.599Z"),
"update_at": ISODate("2022-02-11T02:59:16.599Z"),
"team": {
"_id": ObjectId("620b40de72a79c20ad4d8dc2"),
"uuid": "a6b00dca-8adf-11ec-846a-834d4eb728d2",
"team_name": "第1团队",
"describe": "第1团队",
"user_uuid": [
"b040904e-8a30-11ec-917a-17ee5f1bcd74",
"b047d96c-8a30-11ec-86bb-47637cc95add",
"b0491d68-8a30-11ec-bb2c-af6b5601ac06",
"b04a60a6-8a30-11ec-93f7-43f76283897b"
],
"create_at": ISODate("2022-02-15T05:57:50.472Z"),
"update_at": ISODate("2022-02-15T05:57:50.472Z")
},
"role": {
"_id": ObjectId("62061fa43a7761790c29f26d"),
"uuid": "0fe1b9f2-8ae4-11ec-a5b4-87315c29590e",
"role_name": "团队经理",
"describe": "读写",
"user_uuid": [
"b040904e-8a30-11ec-917a-17ee5f1bcd74",
"b0533aa0-8a30-11ec-b9c3-dbc28ee83f8c"
],
"create_at": ISODate("2022-02-11T08:34:44.518Z"),
"update_at": ISODate("2022-02-11T08:34:44.518Z")
}
}
*/
对象元素复制到根
db.user.aggregate([
{ $match: { "uuid": "b040904e-8a30-11ec-917a-17ee5f1bcd74" } },
{ $lookup: { from: "team", localField: "uuid", foreignField: "user_uuid", as: "team" } },
{ $lookup: { from: "role", localField: "uuid", foreignField: "user_uuid", as: "role" } },
{ $unwind: "$team" },
{ $unwind: "$role" },
{ $replaceWith: { $mergeObjects: [{ team_name: "$team.team_name" }, "$$ROOT"] } },
{ $replaceWith: { $mergeObjects: [{ role_name: "$role.role_name" }, "$$ROOT" ] } }
]).pretty()
/*
{
"role_name": "团队经理",
"team_name": "第1团队",
"_id": ObjectId("6205d104bb59bd04fe0bd0d1"),
"uuid": "b040904e-8a30-11ec-917a-17ee5f1bcd74",
"user_name": "user10",
"real_name": "user10",
"mobile": "13900000010",
"email": "user10@abc.com",
"password": "$2a$10$j3uhq9MetVT16B7i0C5Sve936iiB6FpcG0wghJ85Px7wzdEcqUicC",
"create_at": ISODate("2022-02-11T02:59:16.599Z"),
"update_at": ISODate("2022-02-11T02:59:16.599Z"),
"team": {
"_id": ObjectId("620b40de72a79c20ad4d8dc2"),
"uuid": "a6b00dca-8adf-11ec-846a-834d4eb728d2",
"team_name": "第1团队",
"describe": "第1团队",
"user_uuid": [
"b040904e-8a30-11ec-917a-17ee5f1bcd74",
"b047d96c-8a30-11ec-86bb-47637cc95add",
"b0491d68-8a30-11ec-bb2c-af6b5601ac06",
"b04a60a6-8a30-11ec-93f7-43f76283897b"
],
"create_at": ISODate("2022-02-15T05:57:50.472Z"),
"update_at": ISODate("2022-02-15T05:57:50.472Z")
},
"role": {
"_id": ObjectId("62061fa43a7761790c29f26d"),
"uuid": "0fe1b9f2-8ae4-11ec-a5b4-87315c29590e",
"role_name": "团队经理",
"describe": "读写",
"user_uuid": [
"b040904e-8a30-11ec-917a-17ee5f1bcd74",
"b0533aa0-8a30-11ec-b9c3-dbc28ee83f8c"
],
"create_at": ISODate("2022-02-11T08:34:44.518Z"),
"update_at": ISODate("2022-02-11T08:34:44.518Z")
}
}
*/
移除原始数据
db.user.aggregate([
{ $match: { "uuid": "b040904e-8a30-11ec-917a-17ee5f1bcd74" } },
{ $lookup: { from: "team", localField: "uuid", foreignField: "user_uuid", as: "team" } },
{ $lookup: { from: "role", localField: "uuid", foreignField: "user_uuid", as: "role" } },
{ $unwind: "$team" },
{ $unwind: "$role" },
{ $replaceWith: { $mergeObjects: [{ team_name: "$team.team_name" }, "$$ROOT"] } },
{ $replaceWith: { $mergeObjects: [{ role_name: "$role.role_name" }, "$$ROOT" ] } },
{ $project: { "team": 0 , "role": 0}}
]).pretty()
/*
{
"role_name": "团队经理",
"team_name": "第1团队",
"_id": ObjectId("6205d104bb59bd04fe0bd0d1"),
"uuid": "b040904e-8a30-11ec-917a-17ee5f1bcd74",
"user_name": "user10",
"real_name": "user10",
"mobile": "13900000010",
"email": "user10@abc.com",
"password": "$2a$10$j3uhq9MetVT16B7i0C5Sve936iiB6FpcG0wghJ85Px7wzdEcqUicC",
"create_at": ISODate("2022-02-11T02:59:16.599Z"),
"update_at": ISODate("2022-02-11T02:59:16.599Z")
}
*/
