'MongoDB lookup with multiple nested levels

In my application, I have a section of comments and replies under some documents. Here's how my database schema looks like

db.updates.insertOne({
  "_id": "62347813d28412ffd82b551d",
  "documentID": "17987e64-f848-40f3-817e-98adfd9f4ecd",
  "stream": [
    {
      "id": "623478134c449b218b68f636",
      "type": "comment",
      "text": "Hey @john, we got a problem",
      "authorID": "843df3dbbdfc62ba2d902326",
      "taggedUsers": [
        "623209d2ab26cfdbbd3fd348"
      ],
      "replies": [
        {
          "id": "623478284c449b218b68f637",
          "type": "reply",
          "text": "Not sure, let's involve @jim here",
          "authorID": "623209d2ab26cfdbbd3fd348",
          "taggedUsers": [
            "26cfdbbd3fd349623209d2ab"
          ]
        }
      ]
    }
  ]
})
db.users.insertMany([
  {
    "_id": "843df3dbbdfc62ba2d902326",
    "name": "Manager"
  },
  {
    "_id": "623209d2ab26cfdbbd3fd348",
    "name": "John"
  },
  {
    "_id": "26cfdbbd3fd349623209d2ab",
    "name": "Jim"
  },
])

I want to join those two collections, and replace user ids with complete user information on all levels. So the final JSON should look like this

{
  "_id": "62347813d28412ffd82b551d",
  "documentID": "17987e64-f848-40f3-817e-98adfd9f4ecd",
  "stream": [
    {
      "id": "623478134c449b218b68f636",
      "type": "comment",
      "text": "Hey @john, we got a problem",
      "author": {
        "_id": "843df3dbbdfc62ba2d902326",
        "name": "Manager"
      },
      "taggedUsers": [
        {
          "_id": "623209d2ab26cfdbbd3fd348",
          "name": "John"
        }
      ],
      "replies": [
        {
          "id": "623478284c449b218b68f637",
          "type": "reply",
          "text": "Not sure, let's involve @jim here",
          "author": {
            "_id": "623209d2ab26cfdbbd3fd348",
            "name": "John"
          },
          "taggedUsers": [
            {
              "_id": "26cfdbbd3fd349623209d2ab",
              "name": "Jim"
            }
          ]
        }
      ]
    }
  ]
}

I know how to do the $lookup on the top-level fields, including pipelines, but how can I do with the nested ones?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source