i have two collection

collection1 : [
 {
  _id:"90b992b7-b85f-4e1c-9080-fc3e2dba0db0",
  pIds: [ "47c9124d-f027-4221-8d60-9f491993d923", "08c89e83-ad62-443c-a731-3c6ccd9ca3af" ]
 }
]

and

collection2 : [
 {
  _id: "47d44016-ff84-44dc-b650-abab36d5f661",
  userId: "47c9124d-f027-4221-8d60-9f491993d923",
  isOnline: true
 }
]

collection2 has data for "47c9124d-f027-4221-8d60-9f491993d923" this userId but there is no data in collection2 for "08c89e83-ad62-443c-a731-3c6ccd9ca3af" this userId.

now want the output like bellow

output : [
 {
  _id:"90b992b7-b85f-4e1c-9080-fc3e2dba0db0",
  pIds: [ 
   {
    userId: "47c9124d-f027-4221-8d60-9f491993d923",
    isOnline: true
   },
   {
    userId: "08c89e83-ad62-443c-a731-3c6ccd9ca3af",
    isOnline: false
   },
  ]
 }
]

tried

$lookup: {
 from: "collection2",
 let: { pIds: "$pIds" },
 pipeline: [
  { $match: { $expr: { $in: ["$userId", "$$pIds"] } } },
  { $project: { _id: 0 } },
 ],
 as: "pId",
}

any solution will help lot....

thanks in advance

CodePudding user response：

db.collection1.aggregate([
  {
    "$unwind": "$pIds"
  },
  {
    "$lookup": {
      "from": "collection2",
      "localField": "pIds",
      "foreignField": "userId",
      "as": "docs"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "pids": {
        "$push": {
          userId: "$pIds",
          isOnline: { "$ifNull": [ { $first: "$docs.isOnline" }, false ] }
        }
      }
    }
  }
])

mongoplayground

CodePudding user response：

Here's another way to do it. I wonder if there is any improvement over the accepted answer. I suppose it comes down to the performance of "$unwind", "$group" versus "$map", "$mergeObjects", "$reduce", "$filter". I'm not sure how to measure that.

db.collection1.aggregate([
  {
    "$lookup": {
      "from": "collection2",
      "localField": "pIds",
      "foreignField": "userId",
      "as": "docs"
    }
  },
  {
    "$project": {
      "_id": 1,
      "pIds": {
        "$map": {
          "input": "$pIds",
          "as": "pId",
          "in": {
            "$mergeObjects": [
              { "userId": "$$pId" },
              { "isOnline": {
                  "$reduce": {
                    "input": "$docs",
                    "initialValue": false,
                    "in": {
                      "$or": [
                        false,
                        {
                          "$getField": {
                            "field": "isOnline",
                            "input": {
                              "$first": {
                                "$filter": {
                                  "input": "$docs",
                                  "as": "doc",
                                  "cond": { "$eq": [ "$$pId", "$$doc.userId" ] }
                                }
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.