I'm trying to lookup query from nested array in mongodb and I'm getting stuck.

I have total threee collections. (1) Channel (Parent) (2) ChannelThreads (Children) (3) Users

Channel Collection:

{
    "_id" : ObjectId("61efcbdc1aa27f83da47c93f"),
    "tags" : [],
    "slug_history" : [ 
        "iny1Xik"
    ],
    "title" : "Pirate Chat",
    "settingId" : ObjectId("61408586b719c8ce89f08674"),
    "status" : "published",
    "lockedPageContent" : "",
    "slug" : "iny1Xik",
    "createdAt" : ISODate("2022-01-25T10:07:24.144Z"),
    "updatedAt" : ISODate("2022-01-25T10:07:24.144Z"),
    "__v" : 0
}

Channel Thread Collection:

{
    "_id" : ObjectId("61efcd5df82318884746eb80"),
    "threadImage" : [],
    "parentId" : null,
    "channelId" : ObjectId("61efcbdc1aa27f83da47c93f"),
    "authorId" : ObjectId("6177de8f8a5fd72a4f37b7db"),
    "threadText" : "New Message",
    "reactions" : [ 
        {
            "authors" : [ 
                ObjectId("3687de8f8a5fd72a4f37b7bg")
            ],
            "_id" : ObjectId("61ef856432753c196382c37d"),
            "icon" : "&#128528"
        }
    ],
    "createdAt" : ISODate("2022-01-25T10:13:49.033Z"),
    "updatedAt" : ISODate("2022-01-25T10:13:49.033Z"),
    "__v" : 0
}

User Collection:

{
    "_id" : ObjectId("6177de8f8a5fd72a4f37b7db"),
    "image" : "",
    "tags" : [],
    "pushTokens" : [],
    "lastLogin" : ISODate("2022-01-25T10:08:19.055Z"),
    "firstName" : "dinesh",
    "lastName" : "patel",
    "email" : "[email protected]",
    "infusionSoftId" : "784589",
    "role" : "user",
    "__v" : 0,
    "settings" : {
        "commentNotification" : false,
        "commentReplyNotification" : true
    }
}

I'm trying to implement lookup for authors of thread reactions.

Expected Output:

{
    "_id": ObjectId("61efcbdc1aa27f83da47c93f"),
    "tags": [],
    "slug_history": [
        "iny1Xik"
    ],
    "title": "Pirate Chat",
    "settingId": ObjectId("61408586b719c8ce89f08674"),
    "status": "published",
    "lockedPageContent": "",
    "slug": "iny1Xik",
    "createdAt": ISODate("2022-01-25T10:07:24.144Z"),
    "updatedAt": ISODate("2022-01-25T10:07:24.144Z"),
    "__v": 0,
    "threads": [
        {
            "_id": ObjectId("61efcd5df82318884746eb80"),
            "threadImage": [],
            "parentId": null,
            "channelId": ObjectId("61efcbdc1aa27f83da47c93f"),
            "authorId": {
                "_id": ObjectId("6177de8f8a5fd72a4f37b7db"),
                "image": "",
                "tags": [],
                "pushTokens": [],
                "lastLogin": ISODate("2022-01-25T10:08:19.055Z"),
                "firstName": "dinesh",
                "lastName": "patel",
                "email": "[email protected]",
                "infusionSoftId": "something",
                "role": "user",
                "__v": 0,
                "settings": {
                    "commentNotification": false,
                    "commentReplyNotification": true
                }
            },
            "threadText": "New Message",
            "reactions": [
                {
                    "authors": [ 
                        {
                            "_id": ObjectId("3687de8f8a5fd72a4f37b7bg"),
                            "image": "",
                            "tags": [],
                            "pushTokens": [],
                            "lastLogin": ISODate("2022-01-25T10:08:19.055Z"),
                            "firstName": "kayle",
                            "lastName": "hell",
                            "email": "[email protected]",
                            "infusionSoftId": "8475151",
                            "role": "user",
                            "__v": 0,
                            "settings": {
                                "commentNotification": false,
                                "commentReplyNotification": true
                            }
                        }
                    ],
                    "_id": ObjectId("61ef856432753c196382c37d"),
                    "icon": "&#128528"
                }
            ],
            "createdAt": ISODate("2022-01-25T10:13:49.033Z"),
            "updatedAt": ISODate("2022-01-25T10:13:49.033Z"),
            "__v": 0
        }
    ]
}

How can write lookup query for reaction authors. Thanks in advance!!

CodePudding user response：

You can try nested lookup,

• $lookup with channel thread collection, pass channel id in let
• $match to match channelId condition
• $lookup with user collection to get author info for authorId
• $lookup with user collection to get reactions's authors info
• $arrayElemAt to get first element from authorId
• $map to iterate loop of reactions array, $filter to iterate loop of users and get matching author user info from users array,
• $mergeObjects to merge authors and current object properties
• $$REMOVE to remove users field because it is not needed now

db.channel.aggregate([
  {
    $lookup: {
      from: "channelThread",
      let: { channelId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$channelId", "$channelId"] } } },
        {
          $lookup: {
            from: "user",
            localField: "authorId",
            foreignField: "_id",
            as: "authorId"
          }
        },
        {
          $lookup: {
            from: "user",
            localField: "reactions.authors",
            foreignField: "_id",
            as: "users"
          }
        },
        {
          $addFields: {
            authorId: { $arrayElemAt: ["$authorId", 0] },
            reactions: {
              $map: {
                input: "$reactions",
                as: "r",
                in: {
                  $mergeObjects: [
                    "$$r",
                    {
                      authors: {
                        $filter: {
                          input: "$users",
                          cond: { $in: ["$$this._id", "$$r.authors"] }
                        }
                      }
                    }
                  ]
                }
              }
            },
            users: "$$REMOVE"
          }
        }
      ],
      as: "threads"
    }
  }
])

Playground