$lookup inside $lookup in array of objects inside array-CodePudding

I'm new to mongoDB. Maybe this is a simple question. Kindly help me with this.

What is the mongo query to populate inside a populated value?

There are 3 tables users, bookHistory and books. And I need to lookup in all three.

This is the structure I need.

"userList": [
  {
    "_id": "NT-723DD65BE100-444B-B441-2B2804ED7348",
    "name": "AMC",
    "uid": "NT-723DD65BE100-444B-B441-2B2804ED7348",
    "bookHistory": [
      {
        "_id": "SHNH-06E723698ED8-420F-AB6D-6574633F5984",
        "bookStocksId": "SHNC-4559F09A7F4B-4508-9F61-2A2CD248D3F9",
        "bookId": "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850",
        "uid": "NT-723DD65BE100-444B-B441-2B2804ED7348",
        "bookDetails": {
          "_id": "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850",
          "nameOfBook": "test",
          "nameOfAuthor": "test authou",
        }
      },
      {
        "_id": "617e6c068bf26821f955b034",
        "bookStocksId": "SHNC-694DF63AE953-40A8-B49B-71E46D0A9463",
        "bookId": "SHN-5B5A2712CE88-46BF-8A5F-F01174316025",
        "uid": "NT-723DD65BE100-444B-B441-2B2804ED7348",
        "bookDetails": {
          "_id": "SHN-5B5A2712CE88-46BF-8A5F-F01174316025",
          "nameOfBook": "test",
          "nameOfAuthor": "test authou",
        }
      }
    ]
  }
]

From "users" table need to populate "bookHistory" table using uid
From bookHistory table need to populate "books" table using bookId

Is it possible? I'm trying this code

const pipeline = [
  { $match: query },
  { $project: projection },
  {
    $lookup: {
      from: 'bookHistory',
      localField: 'uid',
      foreignField: 'uid',
      as: 'bookHistory',
    }
  },
];

The below is the sample data for 3 tables

user table

{ 
  "_id" : "NT-723DD65BE100-444B-B441-2B2804ED7348", 
  "name" : "AMC", 
  "uid" : "NT-723DD65BE100-444B-B441-2B2804ED7348", 
  "updatedBy" : "NT-723DD65BE100-444B-B441-2B2804ED7348", 
  "updatedOn" : ISODate("2021-10-22T02:45:31.320 0000"), 
}

bookHistory

{ 
  "_id" : "SHNH-F10ED64A7F98-480B-8D8F-B7587594FB10", 
  "bookStocksId" : "SHNC-7D60FF3D3E15-4C43-AB24-7E3EA1C0D79A", 
  "bookId" : "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850", 
  "uid" : "NT-723DD65BE100-444B-B441-2B2804ED7348", 
  "tookOn" : ISODate("2021-10-31T12:59:24.895 0000"), 
  "dueOn" : ISODate("2021-11-07T12:59:24.895 0000"), 
  "addedOn" : ISODate("2021-10-31T12:59:24.895 0000")
}

books table

{ 
"_id" : "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850", 
"nameOfBook" : "test", 
"nameOfAuthor" : "test authou", 
"yearOfPublication" : "1998", 
"publication" : "test", 
"addedOn" : ISODate("2021-10-27T08:39:00.147 0000")
}

Could anyone please help me with this?

CodePudding user response：

Simply perform another $lookup from bookHistory to books in the sub-pipeline of $lookup from user to bookHistory

db.user.aggregate([
  {
    "$lookup": {
      "from": "bookHistory",
      let: {
        uid: "$uid"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$uid",
                "$uid"
              ]
            }
          }
        },
        {
          "$lookup": {
            "from": "books",
            "localField": "bookId",
            "foreignField": "_id",
            "as": "bookDetails"
          }
        },
        {
          "$unwind": "$bookDetails"
        }
      ],
      "as": "bookHistory"
    }
  }
])

Here is the Mongo playground for your reference.