javascript - 多聚合查询 MongoDB

Question

我有以下情况:

• 集合部门:

  { "_id" : 0, "name" : "Dep_A_1", "description" : "Department A1" }
  { "_id" : 1, "name" : "Dep_B_1", "description" : "Department B1" }
  { "_id" : 2, "name" : "Dep_C_1", "description" : "Department C1" }
  

• 收集技能:

  { 
      "_id" : 0, 
      "name" : "Creativity", 
      "description" : "description", 
      "type" : "soft", 
      "categories" : [ 0, 2 ] 
  }
  

• 集合 Angular 色:

  { 
      "_id" : 0,
      "name" : "Software Developer Automation and Control",
      "description" : "Automation and Control expert",
      "departments" : [ 0, 2 ],
      "skills" : [ { "_id" : 18, "weight" : 30 } ]
  }
  

我需要这样的结果:

{
    "_id" : 0, 
    "name" : "Software Developer Automation and Control", 
    "description" : "Automation and Control expert",
    "departments" : [ 
        { 
            "_id" : 0, 
            "name" : "Dep_A_1",
            "description" : "Department A1" 
        }, 
        { 
            "_id" : 2, 
            "name" : "Dep_C_1", 
            "description" : "Department C1" 
        }
    ], 
    "skills" : [ 
        {
            "_id" : 0, 
            "name" : "Creativity", 
            "description" : "description", 
            "type" : "soft", 
            "weight" : 30
        }
    ]
}

我需要用 departments 和 roles< 中的相应对象替换 role.departments 和 role.skills 数组 集合。有没有办法查询 Mongo 并得到这样的结果？

无论如何，我正在使用 Mongo 3.6 和 Pymongo。谢谢。

Answer 1

为了避免昂贵的 $unwind 和 $group 阶段，您可以这样做:

db.roles.aggregate([{
    $lookup: {
        from: 'departments',
        localField: 'departments',
        foreignField: '_id',
        as: 'departments'
    }
}, {
    $lookup: {
        from: 'skills',
        let: {
            "skills": "$skills" // keep a reference to the "skills" field of the current document and make it accessible via "$$skills"
        },
        pipeline: [{
            $match: {
                $expr: {
                    $in: [ "$_id", "$$skills._id" ] // this just does the "joining"
                }
            }
        }, {
            $addFields: { // add a new field
                "weight": { // called "weight"
                    $arrayElemAt: [ // which shall be set to the correct "weight"
                        "$$skills.weight", // that can be found in the "weight" field of the "skills" array
                        { $indexOfArray: [ "$$skills._id", "$_id" ] } // at the position that has the matching "_id" value
                    ]
                }
            }
        }],
        as: 'skills'
    }
}])