CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Kiran
Kiran

Reputation: 1185

Mongo DB Aggregation pipeline help for nested document building

Tried many ways in mongodb aggregation and pipeline concepts but not able to achieve exactly the below format required for my project.

My data in Mongodb collection is in the below format

[{
    "title" : "Product Title 1",
    "desc" : "Product Description 1",
    "year" : 2019,
    "productkey" : "A"
},
{
    "title" : "Product Title 2",
    "desc" : "Product Description 2",
    "year" : 2019,
    "productkey" : "A"
},,
{
    "title" : "Product Title 3",
    "desc" : "Product Description 3",
    "year" : 2018,
    "productkey" : "A"
},
{
    "title" : "Product Title 4",
    "desc" : "Product Description 4",
    "year" : 2018,
    "productkey" : "A"
},
[{
    "title" : "Product Title 5",
    "desc" : "Product Description 5",
    "year" : 2019,
    "productkey" : "B"
},
{
    "title" : "Product Title 6",
    "desc" : "Product Description 6",
    "year" : 2019,
    "productkey" : "B"
},
{
    "title" : "Product Title 7",
    "desc" : "Product Description 7",
    "year" : 2018,
    "productkey" : "B"
},
{
    "title" : "Product Title 8",
    "desc" : "Product Description 8",
    "year" : 2018,
    "productkey" : "B"
},
{
    "title" : "Product Title 9",
    "desc" : "Product Description 9",
    "year" : 2019,
    "productkey" : "C"
},
{
    "title" : "Product Title 10",
    "desc" : "Product Description 10",
    "year" : 2019,
    "productkey" : "C"
},
{
    "title" : "Product Title 11",
    "desc" : "Product Description 11",
    "year" : 2018,
    "productkey" : "C"
}]

I am trying to achieve the below format using aggregation and pipelines

[{
    "productkey" : "A",
    "details":
    [
        {
            "year": 2019,
            "subdetails":[
                {
                    "title" : "Product Title 1",
                    "desc" : "Product Description 1",
                },
                {
                    "title" : "Product Title 2",
                    "desc" : "Product Description 2",
                }           
            ]
        },
        {
            "year": 2018,
            "subdetails":[
                {
                    "title" : "Product Title 3", "desc" : "Product Description 3",
                },
                {
                    "title" : "Product Title 4",
                    "desc" : "Product Description 4",
                }           
            ]

        } 
    ]
},
{
    "productkey" : "B",
    "details":
    [
        {
            "year": 2019,
            "subdetails":[
                {
                    "title" : "Product Title 5",
                    "desc" : "Product Description 5",
                },
                {
                    "title" : "Product Title 6",
                    "desc" : "Product Description 6",
                }           
            ]
        },
        {
            "year": 2018,
            "subdetails":[
                {
                    "title" : "Product Title 7",
                    "desc" : "Product Description 7",
                },
                {
                    "title" : "Product Title 8",
                    "desc" : "Product Description 8",
                }           
            ]

        } 
    ]
},
{
    "productkey" : "C",
    "details":
    [
        {
            "year": 2019,
            "subdetails":[
                {
                    "title" : "Product Title 9",
                    "desc" : "Product Description 9",
                },
                {
                    "title" : "Product Title 10",
                    "desc" : "Product Description 10",
                }           
            ]
        },
        {
            "year": 2018,
            "subdetails":[
                {
                    "title" : "Product Title 11",
                    "desc" : "Product Description 11",
                }       
            ]

        } 
    ]
}]

So how can I achieve this?

Upvotes: 1

Views: 39

Answers (1)

Ashh
Ashh

Reputation: 46441

You can use below aggregation

db.collection.aggregate([
  { "$group": {
    "_id": { "productkey": "$productkey", "year": "$year" },
    "subDetails": { "$push": { "title": "$title", "desc": "$desc" }}
  }},
  { "$group": {
    "_id": "$_id.productkey",
    "details": { "$push": { "year": "$_id.year", "subDetails": "$subDetails" }}
  }}
])

MongoPlayground

Upvotes: 1

Related Questions