MongoDB $lookup for embedded collections

user_2217418 0
Viewed 95

I have 2 collections:

UserCollection :

[
   {
      "_id" : ObjectId("3a9ccf7de6348936d88b3601"),
      "first_name" : "John",
   },
   {
       "_id" : ObjectId("3a9ccf7de6348936d88b3602"),
       "first_name" : "Jane",
   }
]

CommentCollection (with embedded ReplyCollection) :

[
   {
      "_id" : ObjectId("3a9ccf7de6348936d88b3601"),
      "user_id": ObjectId("3a9ccf7de6348936d88b3601"),
      "body" : "Hello World",
      "replies":
      [
          {
             "_id" : ObjectId("3a9ccf7de6348936d88b3441"),
             "user_id": ObjectId("3a9ccf7de6348936d88b3601"),
             "body" : "World said, Hello Back", 
          }
      ]
   },
   {
       "_id" : ObjectId("3a9ccf7de6348936d88b3602"),
       "user_id": ObjectId("3a9ccf7de6348936d88b3601"),
       "body" : "Hello Stack",
       "replies":
       [
          {
             "_id" : ObjectId("3a9ccf7de6348936d88b3602"),
             "body" : "Stack said Overflow",
             "user_id": ObjectId("3a9ccf7de6348936d88b3601"),
          }
       ]
   }
]

Query to join Comments & Users :

db.comments.aggregate([
   { "$lookup": {
     "from": "Users",
     "localField": "user_id",
     "foreignField": "_id",
     "as": "user" } 
   },
   {"$unwind": "$user"}
]);

Behaviour: comments and user table were joined as expected.

Question: Is it possible to join also the reply-user relation within the same query?

Thx in advance.

aggregation-framework mongodb-query mongodb
1 Answers

Yes you can do that using uncorrelated-subquery in $lookup :

db.comments.aggregate([
  {
    $lookup: {
      from: "Users",
      let: {
        repliesUserId: "$replies.user_id",
        userId: "$user_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $or: [
                { $eq: ["$_id", "$$userId"] },
                { $in: ["$_id", "$$repliesUserId"] }
              ]
            }
          }
        }
      ],
      as: "user"
    }
  }
]);

Test : MongoDB-Playground

user_72376130
Related