How to create a nested lookup in mongodb?

Question

I want to join 3 tables. Here is my aggregate, its just joining the 1st and 2nd table.

 const products = await ProductInventory.aggregate([
    {
      $lookup: {
        from: 'productdetails',
        localField: 'prodId',
        foreignField: '_id',
        as: 'product',
      },
    },
    {
      $unwind: '$product',
    },
    {
      $match: { 'product.isActive': 'Yes' },
    },
  ]);

This is the result of the code above. In the product I have a category and its the id of the category. I want to join it also in categories table. So I can get the category name.

"products": [
{
  "_id": "60a0e77b02a58f3038598301",
  "prodId": "TB-057",
  "stock_onhand": "30",
  "date_created": "2021-05-16T09:35:55.565Z",
  "date_updated": "2021-05-16T09:35:55.565Z",
  "__v": 0,
  "product": {
    "_id": "TB-057",
    "isActive": True,
    "product_name": "TEST2",
    "unit_price": "1.23",
    "markup_price": "1.23",
    "SRP": "2.46",
    "description": "test2",
    "category": "609521209d10ac4774748c74",
    "date_updated": "2021-05-16T09:35:55.562Z",
    "date_created": "2021-05-16T09:35:55.565Z",
    "__v": 0
  }
},

should I use lookup again after I use $unwind: '$product'.?

//SAMPLE DOCUMENTS
//This is the productinventories
 "_id" : ObjectId("609d398829be8e2bd8509d6e"),
    "prodId" : "TB-056",
    "stock_onhand" : "12",
    "date_created" : ISODate("2021-05-13T14:36:56.730Z"),
    "date_updated" : ISODate("2021-05-13T14:36:56.730Z"),
    "__v" : 0

//This is the productdetails
  "_id" : "TB-056",
    "isActive" : True,
    "product_name" : "TEST",
    "unit_price" : "1.23",
    "markup_price" : "1.23",
    "SRP" : "2.46",
    "description" : "maiba 1",
    "category" : ObjectId("609521209d10ac4774748c74"),
    "date_updated" : ISODate("2021-05-13T14:36:56.728Z"),
    "date_created" : ISODate("2021-05-13T14:36:56.730Z"),
    "__v" : 0

//this is the categories
 "_id" : ObjectId("609521209d10ac4774748c74"),
    "isActive" : True,
    "category_name" : "TABLET",
    "category_abbreviation" : "TB",
    "date_updated" : ISODate("2021-05-07T11:14:40.544Z"),
    "date_created" : ISODate("2021-05-07T11:14:40.544Z"),
    "__v" : 0

Does this answer your question? [MongoDB nested lookup with 3 levels](https://stackoverflow.com/questions/36019713/mongodb-nested-lookup-with-3-levels) — turivishal, May 27 '21 at 11:17

score 0 · Answer 1 · answered Jun 02 '21 at 07:24

Yes, you need to run $lookup again if you want to get data from a third collection.

In your case:

const products = await ProductInventory.aggregate([
  {
    $lookup: {
      from: 'productdetails',
      localField: 'prodId',
      foreignField: '_id',
      as: 'product',
    },
  },
  {
    $unwind: '$product',
  },
  {
    $match: { 'product.isActive': 'Yes' },
  },
  {
    $lookup: {
      from: 'categories',
      localField: 'product.category',
      foreignField: '_id',
      as: 'category',
    },
  },
  {
    $unwind: '$category',
  }
]);

How to create a nested lookup in mongodb?

1 Answers1