How to join three (multipe) collections with $lookup in mongodb?

Question

How to join three (multipe) collections with $lookup in mongodb?

Hi I am looking to join data from three collection

users collection:

[
{
_id:0,
name:"abc",
phone:999999999
},
{
_id:1,
name:"xyz",
phone:888888888
},
]

product collection:

[
{
_id:"p01",
name:"product-name",
price:1200
},
{
_id:"p02",
name:"product-name1",
price:100
}
]

productreviews collection:

[
{
_id:"pr0",
userId:0,
productId:"p01",
star:4
},
{
_id:"pr1",
userId:1,
productId:"p01",
star:3
}
]

mongodb query:

product.aggregate([
      {
        $lookup: {
          from: "productreviews",
          localField: "_id",
          foreignField: "productId",
          as: "review",
        },
        
      },
      {
        $lookup: {
          from: "users",
          localField: "review.userId",
          foreignField: "_id",
          as: "review.userInfo",
        },
        
      },
    ])

I am not able to get that output which i need. How can i get this following output:

{
  product: [
    {
      _id: "p01",
      name: "product-name",
      price: 1200,
      review: [
        {
          _id: "pr0",
          userId: 0,
          productId: "p01",
          star: 4,
          "userInfo": {
            name: "abc",
            phone: 999999999
          }
        },
        {
          _id: "pr1",
          userId: 1,
          productId: "p01",
          star: 3,
          "userInfo": {
            "name": "xyz",
            "phone": 888888888,
          }
        },
      ]
    },
    {
      _id: "p02",
      name: "product-name1",
      price: 100,
    },
  ]
}

Any help appreciated!. Thank You...

Answer 1

db.product.aggregate([
  {
    $lookup: {
      from: "review",
      localField: "_id",
      foreignField: "productId",
      as: "review",
      
    },
    
  },
  {
    $lookup: {
      from: "users",
      localField: "review.userId", //See here
      foreignField: "_id",
      as: "review.userInfo",
      
    },
    
  },
  
])

Local field name is userId in the second lookup.

playground

EDIT:

To preserve reviews also

Add a unwind stage

db.product.aggregate([
  {
    $lookup: {
      from: "review",
      localField: "_id",
      foreignField: "productId",
      as: "review",
      
    },
    
  },
  {
    "$unwind": "$review"
  },
  {
    $lookup: {
      from: "users",
      localField: "review.userId",
      foreignField: "_id",
      as: "review.userInfo",
      
    },
    
  },
  
])

Update

To keep the docs where there is no match, preserve null arrays at unwind stage as below

  {
    $unwind: {
      path: "$review",
      "preserveNullAndEmptyArrays": true
    }
  }