'Create a mongodb aggregate with many collections and nested levels

I need to populate a menu using aggregate. I have 2 collections: categories and items. I need to call a collection within the other and I need to present the content of items according to the offer or options, which is within the offer. The categories part is working, but I can't load the items and distribute them correctly in the offers and options. I made the following query, but the items are all together within the offer. Can someone help me?

This is an example of populate then I hope:

[{
  "_id": "61d74b0fa9ca1f30eb351176"
  "__v": 0,
  "availability": [
    {
      "startDate": "01/12/21",
      "endDate": "01/01/22",
    }
  ],
  "categories": [
    {
      "name": {
        "pt": "category1"
      },
      "enabled": true,
      "availability": [
        {
          "startDate": "01/12/21",
          "endDate": "01/01/22",
        }
      ],
      "offers": [
        {
          "enabled": true,
          "_id": "61e70573e4dce48796270ec6"
          "item": {
            "_id":"61d74e93bc45a13bca4ea418"
            "name": {
            "lg": "Beef"
            },
            "enabled": true,
       },
          "price": {
            "value": 2990,
            "originalValue": 3490
          },
          "availability": [
            {
              "startDate": "01/12/21",
              "endDate": "01/01/22",
            }
          ],
          "optionGroup": [
            {
              "maxPermitted": 2,
              "minPermitted": 0,
              "enabled": true,
              "name": {
                "pt": "Combo"
              },
              "options": [
                {
                  "maxPermitted": 1,
                  "price": 0,
                  "enabled": true,
          "item": {
                "_id":"61dc6e0f9acb8e39d7ae6e57"
                "name": {
            "lg": "fries"
                },
                "enabled": true,
           },
                },
                {
                  "maxPermitted": 1,
                  "price": 0,
                  "enabled": true,
          "item": {
                "_id":"61d753da6fb31063132eb717"
                "name": {
            "lg": "coke"
                },
                "enabled": true,
           },
                }
              ]
            }
          ],
        },
        ],
}]

And This, the query I trying:

[{$match: {
 _id: ObjectId('61d74b0fa9ca1f30eb351176')
}}, {$lookup: {
 from: 'Categories',
 pipeline: [
  {
   $project: {
    _id: 0,
    date: {
     name: '$name',
     enabled: '$enabled',
     offers: '$offers'
    }
   }
  },
  {
   $replaceRoot: {
    newRoot: '$date'
   }
  }
 ],
 as: 'categories'
}}, {$lookup: {
 from: 'Items',
 'let': {
  items: '$categories.offers.item'
 },
 pipeline: [
  {
   $project: {
    date: {
     _id: '$_id',
     name: '$name',
     enabled: '$enabled'
    }
   }
  },
  {
   $replaceRoot: {
    newRoot: {
     $mergeObjects: [
      '$date'
     ]
    }
   }
  }
 ],
 as: 'items'
}}, {$addFields: {
 'categories.offers.item': {
  $map: {
   input: {
    $zip: {
     inputs: [
      '$items',
      '$categories.offers.item'
     ]
    }
   },
   'in': {
    $mergeObjects: []
   }
  }
 }
}}]

Thanks for any help!



Sources

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

Source: Stack Overflow

Solution Source