Here I have three collections.

Category

category: [
    {
      id: 1,
      name: "cat 1",
      status:true
    },
    {
      id: 2,
      name: "cat 2",
      status:true
    }
  ]

Item

item: [
    {
      id: 1,
      name: "item 1"
    },
    {
      id: 2,
      name: "item 2"
    },
    {
      id: 3,
      name: "item 3"
    },
    {
      id: 4,
      name: "item 4"
    }
  ]

Package

[
    {
      id: 1,
      name: "package 1",
      categoryId: 1,
      items: [
        {
          _id: 1,
          itemId: 1
        },
        {
          _id: 2,
          itemId: 2
        }
      ]
    },
    {
      id: 2,
      name: "package 2",
      categoryId: 2,
      items: [
        {
          _id: 1,
          itemId: 3
        },
        {
          _id: 2,
          itemId: 4
        }
      ]
    }
  ]

I want to get all packages along with items object on category wise, and each category status should be true. I want to list all packages based on the category.

Column CategoryId of collection package is a foreign key.

Column ItemId of collection package is a foreign key.

Expected output

[
    {
        name:'cat 1',
        status:true,
        package:[
            {
                id: 1,
                name: "package 1",
                categoryId: 1,
                items: [
                    {
                        id: 1,
                        name: "item 1"
                    },
                    {
                        id: 2,
                        name: "item 2"
                    },
                ]
            }
        ]
    },
    {
        name:'cat 2',
        status:true,
        package:[
            {
                id: 2,
                name: "package 2",
                categoryId: 2,
                items: [
                    {
                        id: 3,
                        name: "item 3"
                    },
                    {
                        id: 4,
                        name: "item 4"
                    },
                ]
            }
        ]
    }
]

for reference : https://mongoplayground.net/p/83z0Hcgh04i

CodePudding user response：

To achieve the expected result we must perform some steps on the data, we will explain each of this steps. You can see the full query here.

The operation tha we must use on the category DB is an aggregation

db.category.aggregate([])

Inside this function we will put each step inside the array. The first one is a lookup, we will get the packages that matches each category an stored them in the packagesfield

{
    "$lookup": {
      "from": "package",
      "localField": "id",
      "foreignField": "categoryId",
      "as": "packages"
    }
}

Since we need the items info to populate each one and the items are inside each package, we must separe them. We will unwind the packages and the items.

  {
    "$unwind": "$packages"
  },
  {
    "$unwind": "$packages.items"
  }

Now we can match each item with his information, with another lookup and stored them inside each package.

  {
    "$lookup": {
      "from": "items",
      "localField": "packages.items.itemId",
      "foreignField": "id",
      "as": "packages.items"
    }
  }

Because doing a lookup give us an array, we will use another unwind to make sure there is only one item per document before groping.

  {
    "$unwind": "$packages.items"
  }

At this moment we have all the date that we wanted, but we have to group it again, becouse we separated it before with the unwind. To perfomr this we will use group on the packages.id . We will format the fields that we want, push all items together and save some aux data in the root field for later.

  {
    "$group": {
      "_id": "$packages.id",
      name: {
        "$first": "$packages.name"
      },
      "brandId": {
        "$first": "$packages.brandId"
      },
      "categoryId": {
        "$first": "$packages.categoryId"
      },
      root: {
        "$first": "$$ROOT"
      },
      "items": {
        "$push": "$packages.items"
      }
    }
  }

At this step we have all the packages together, we must groupa gain by category. To do this we will use the aux data store in root.

  {
    "$group": {
      "_id": "$root.id",
      name: {
        "$first": "$root.name"
      },
      "packages": {
        "$push": "$$ROOT"
      }
    }
  }

Finally we will erase the aux root field.

  {
    "$project": {
      "packages.root": 0
    }
  }