I want to aggregate data array inside another array in mongodb

Question

I want to aggregate MongoDB documents which is having arrays inside of an array. my document was like the below.

{
  "_id": "6257e31d11a9d5231c05c084",
  "name": "Test Name 1",
  "phone": "1234567891",
  "visits": [
    {
      "_id": "6257e31d11a9d5231c05c069",
      "date": "2-7-2021",
      "samples": [
        "6257f8855197613b641d494e",
        ....
      ],
      "products_detailed": [
        "5d725cd2c4ded7bcb480eab2",
        .....

      ]
    },
    ...........
  ]
}

and I want to get the output line below

{
  "_id": "6257e31d11a9d5231c05c084",
  "name": "Test Name 1",
  "phone": "1234567891",
  "visits": [
    {
      "_id": "6257e31d11a9d5231c05c069",
      "date": "2-7-2021",
      "samples": [
        {
          "_id": "6257f8855197613b641d494e",
          "product_name": "Samor",
          "price": 250
        },
        ........
      ],
      "products_detailed": [
        {
          "_id": "5d725cd2c4ded7bcb480eab2",
          "product_name": "Pahad",
          "price": 100
        },
        ............
      ]
    },
    .........................
  ]
}

how can I get like this? I tried to use $lookup & group to get the output, but I am not getting the output as required me.

Answer 1

Since you have a list of visits on each document, one way to go is to $unwind and then $group at the end, like this:

db.Main.aggregate([
  {
    $unwind: "$visits"
  },
  {
    "$lookup": {
      "from": "Samples",
      "localField": "visits.samples",
      "foreignField": "_id",
      "as": "samples"
    }
  },
  {
    "$lookup": {
      "from": "Product Detailed",
      "localField": "visits.products_detailed",
      "foreignField": "_id",
      "as": "products_detailed"
    }
  },
  {
    $project: {
      name: 1,
      phone: 1,
      "visits._id": 1,
      "visits.date": 1,
      "visits.products_detailed": "$products_detailed",
      "visits.samples": "$samples"
    }
  },
  {
    $group: {
      _id: 0,
      name: {$first: "$name"},
      visits: {$push: "$visits"}
    }
  }
])

As you can see on the playground, on your data sample it will return:

[
  {
    "_id": 0,
    "name": "Test Name 1",
    "visits": [
      {
        "_id": "6257e31d11a9d5231c05c069",
        "date": "2-7-2021",
        "products_detailed": [
          {
            "_id": "5d725cd2c4ded7bcb480eab2",
            "price": 100,
            "product_name": "Pahad"
          }
        ],
        "samples": [
          {
            "_id": "6257f8855197613b641d494e",
            "price": 250,
            "product_name": "Samor"
          }
        ]
      }
    ]
  }
]