mongoDB 对嵌套对象数组的聚合查找 [英] mongoDB aggregate lookup on nested array of objects

问题描述

我有一个如下所示的集合，我在执行 $lookup 并按照开始时的方式返回它时遇到了麻烦，但使用了填充的字段:

I have a collection that looks like the following and i'm having troubles to perform a $lookup and return it the way it was at the beginning but with the populated fields:

我对要填充的字段发表了评论，这些字段是 (agent,missions.clients.client)

i have made comments on the fields i want to populate which are (agent, missions.clients.client)

 {  
   "title":"Tournée libre",
   "agent":"5d811943d2a2100017667228", // needs to be populated
   "missions":[  
      {  
         "_id":"5d8a075346f10d679ab4383e",
         "title":"Journée 3",
         "clients":[  
            {  
               "_id":"5d8a075346f10d679ab4383f",
               "valid":true,
               "client":"5d1bc39aa2af623b94363b33", // this needs to be populated
               "visit_time":"2019-09-24T12:03:38.383Z"
            },
            {  

               "_id":"5d8a0dc446f10d679ab43888",
               "valid":true,
               "client":"5d8a0c8346f10d679ab43886",
               "visit_time":"2019-09-24T12:34:23.210Z"
            },

         ]
      }
   ],
   "created_at":"2019-09-24T12:08:51.928Z",
   "__v":2
}

结果应该是这样:

 {  
   "title":"Tournée libre",
   "agent": {firstname: 'something', lastname: 'something else'}
   "missions":[  
      {  
         "_id":"5d8a075346f10d679ab4383e",
         "title":"Journée 3",
         "clients":[  
            {  
               "_id":"5d8a075346f10d679ab4383f",
               "valid":true,
               "client": {firstname: 'something', lastname: 'something else'},
               "visit_time":"2019-09-24T12:03:38.383Z"
            },
            {  

               "_id":"5d8a0dc446f10d679ab43888",
               "valid":true,
               "client":{firstname: 'something', lastname: 'something else'},
               "visit_time":"2019-09-24T12:34:23.210Z"
            },

         ]
      }
   ],
   "created_at":"2019-09-24T12:08:51.928Z",
   "__v":2
}

推荐答案

您可以使用以下聚合管道.

You could use below aggregation pipeline.

$lookup 填充代理，然后 $reduce 和 $concatArrays 收集所有客户端 ID，$lookup 获取客户端详细信息.

$lookup to populate agent followed by $reduce and $concatArrays to collect all the client ids and $lookup to get the client details.

$addFields 和 $map 迭代任务数组，并为每个客户端映射上一阶段的客户端信息，通过客户端 ID 和 $mergeObjects 查找以保留其他字段.$project 阶段删除额外的字段.

$addFields with $map to iterate mission array and for each client map the client info from previous stage by lookup by client id and $mergeObjects to keep the other fields. $project stage to remove the extra fields.

Mongo db 3.6 及以上

Mongo db 3.6 and above

Model.aggregate([
 {"$lookup":{
  "from":"agents",
  "localField":"agent",
  "foreignField":"_id",
  "as":"agent"
 }},
 {"$addFields":{"agent":{"$arrayElemAt":["$agent",0]}}},
 {"$addFields":{
   "client_ids":{
     "$reduce":{
       "input":"$missions",
       "initialValue":[],
       "in": {"$concatArrays":["$$value","$$this.clients.client"]}
     }
   }
 }},
 {"$lookup":{
   "from":"clients",
   "localField":"client_ids",
   "foreignField":"_id",
   "as":"client_info"
 }},
 {"$addFields":{
   "missions":{
     "$map":{
       "input":"$missions",
       "in":{
         "$mergeObjects":[
           "$$this",
           {"clients":{"$map":{
             "input":"$$this.clients",
             "in":{"$mergeObjects":[
               "$$this",
              {"client":{"$arrayElemAt":[
                "$client_info",
                {"$indexOfArray":["$client_ids","$$this._id"]}
              ]}}
            ]}
           }}}
         ]
       }
     }
   }
 }},
 {"$project":{"client_ids":0,"client_info":0}}
])

Mongo db 小于 3.6

Mongo db less than 3.6

$lookup 填充代理，然后 $unwind 到达客户端并查找以获取客户端详细信息.使用 $group 回退以返回具有填充值的原始结构.

$lookup to populate agent followed by $unwind to reach client and look up to get the client details. Rewind with $group to bring back to original structure with populated values.

Model.aggregate([
 {"$lookup":{
  "from":"agents",
  "localField":"agent",
  "foreignField":"_id",
  "as":"agent"
 }},
 {"$addFields":{"agent":{"$arrayElemAt":["$agent",0]}}},
 {"$unwind":"$missions"},
 {"$unwind":"$missions.clients"},
 {"$lookup":{
   "from":"clients",
   "localField":"missions.clients.client",
   "foreignField":"_id",
   "as":"missions.clients.client"
 }},
 {"$addFields":{"missions.clients.client":{"$arrayElemAt":["$missions.clients.client",0]}}},
 {"$group":{
   "_id":{"_id":"$_id","mission_id":"$missions._id"},
   "agent":{"$first":"$agent"},
   "title":{"$first":"$missions.title"},
   "clients":{"$push":"$missions.clients"}
 }},
 {"$group":{
   "_id":"$_id._id",
   "agent":{"$first":"$agent"},
   "missions":{
     "$push":{
       "_id":"$_id.mission_id",
       "title":"$title",
       "clients":"$clients"
      }
    }
 }}
])

这篇关于mongoDB 对嵌套对象数组的聚合查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！