$lookup 多集合之间关联 --- 2023-04-20

两个集合之间关联：简单示例

db.post.aggregate([
  { "$match": { "rating": "important" } },
  { "$sort": { "date": -1 } },
  { "$limit": 20 },
  { "$lookup": {
    "localField": "user_id",  //当前集合关联的字段
    "from": "user", //关联那个集合
    "foreignField": "_id",  //关联集合的关联字段
    "as": "userinfo" //最终结果放在items里
  } },
  { "$unwind": "$userinfo" },
  { "$project": {
    "text": 1,
    "date": 1,
    "userinfo.name": 1,
    "userinfo.country": 1
  } }
]);

参考：https://www.cnblogs.com/duhuo/p/6068879.html

多个集合可参考：
https://www.5axxw.com/questions/content/7945v1

复杂示例：两集合涉及多个匹配条件

//两集合查询 第二步

db.pm_sys_facebook_creative_material.aggregate([
     {
    
    '$match': {
         'pm_creative_id': {
        "$in": [4177927,
4177924,
4177923,
3932179,
3932165,
3932159,
3932151,
3663395,
3663394,
3663391,
3663390,
3663389,
3663388,
3585550,
3571106,
3571094,
3571085,
3571082,
3571077,
3571074,
3562993,
3562992,
3562991,
3562990,
3562989,
3521367,
3395651,
3559068,
3562395,
3562229,
3562228,
3582015,
3561942,
3561937,
3561933,
3561853,
3538177,
3405656,
3559020,
3558277,
3557816,
3521375,
3557813,
3557812,
3557140,
3582014,
3556837,
3556836,
3556835,
3556834,
3556831,
3556830,
3556829,
3556828,
3556827,
3556825,
3556824,
3556823,
3556822,
3556820,
3377412,
3396261,
3582013,
3501193,
3463143,
3461181,
3377412,
3377406,
3412544,
3404780,
3431695,
3404179,
3396240,
3394016,
3394009,
3394005,
3393998,
3393997,
3390772,
3387294,
3379006,
3378973,
3376700,
3369083,
3369077,
3214127,
3187146,
3420058,
3420057,
3431159,
3419619,
3461158,
3419618
        ]
      },
            'account_id':{'$ne': ''},
    }
  },
    
   {
      $lookup: {
         from: "pm_sys_facebook_material_library",
         let: { 
                                cm_account_id: "$account_id",
                cm_hash_video_id: "$hash_video_id" 
                            },
         pipeline: [ {
            $match: {
               $expr: {
                  $and: [
                     { $eq: [ "$$cm_account_id", "$account_id" ] },
                     { $eq: [ "$$cm_hash_video_id", "$material_key" ] }
                  ]
               }
            }
                        
         }],
         as: "m"
      }
   },
     {
    $unwind: "$m" 
  },
         {
    "$project": {
      "id": 1,
      "m.hash": 1,
      "m.pm_material_uri": 1,
      "m.pm_thumbnail_uri": 1,
      "m.material_name": 1,
      "m.material_size": 1,
      "m.material_type": 1,
            "m.material_key": 1,
    }
  }
])