Mongodb aggregation json format by month result

Question

Hello I am working with the reporting api which will going to use in highcharts and I am new to mongodb.

Below is my aggregation query (suggest me modification) :

db.product_sold.aggregate({

    $group: {
        _id: { year: { $year: "$solddate" }, month: { $month: "$solddate" }, productid: "$productid" },
        totalQty: { $sum: "$qty" }
    }

})

Output:

{ 
    "_id" : {
        "year" : NumberInt(2019), 
        "month" : NumberInt(2), 
        "productid" : "11"
    }, 
    "totalQty" : 6.0
}
// ----------------------------------------------
{ 
    "_id" : {
        "year" : NumberInt(2019), 
        "month" : NumberInt(2), 
        "productid" : "14"
    }, 
    "totalQty" : 7.0
}
// ----------------------------------------------
{ 
    "_id" : {
        "year" : NumberInt(2019), 
        "month" : NumberInt(1), 
        "productid" : "13"
    }, 
    "totalQty" : 3.0
}
// ----------------------------------------------
{ 
    "_id" : {
        "year" : NumberInt(2019), 
        "month" : NumberInt(2), 
        "productid" : "10"
    }, 
    "totalQty" : 6.0
}
// ----------------------------------------------
{ 
    "_id" : {
        "year" : NumberInt(2018), 
        "month" : NumberInt(2), 
        "productid" : "12"
    }, 
    "totalQty" : 5.0
}
// ----------------------------------------------
{ 
    "_id" : {
        "year" : NumberInt(2019), 
        "month" : NumberInt(2), 
        "productid" : "15"
    }, 
    "totalQty" : 8.0
}
// ----------------------------------------------
{ 
    "_id" : {
        "year" : NumberInt(2019), 
        "month" : NumberInt(1), 
        "productid" : "11"
    }, 
    "totalQty" : 2.0
}
// ----------------------------------------------

What I want in output is something like :

status: 200,
msg: "SUCCESS"
data: [{
    1:[
        {
            "productid": 11,
            "totalQty": 3
        },
        {
            "productid": 12,
            "totalQty": 27
        }
    ],

    2:[
        {
            "productid": 11,
            "totalQty": 64
        },
        {
            "productid": 12,
            "totalQty": 10
        }
    ]   
}]

For reference attaching the image of the collection

Is there any way to achieve it using aggregation or anything else or I will have to do it manually by code ?

mickl · Accepted Answer · 2019-02-04 21:16:06Z

1

You can append below aggreagation stages to your current pipeline:

db.product_sold.aggregate([
    // your current $group stage
    {
        $group: {
            _id: "$_id.month",
            docs: { $push: { productid: "$_id.productid", totalQty: "$totalQty" } }
        }
    },
    {
        $project: {
            _id: 0,
            k: { $toString: "$_id" },
            v: "$docs"
        }
    },
    {
        $group: {
            _id: null,
            data: { $push: "$$ROOT" }
        }
    },
    {
        $project: {
            _id: 0,
            data: { $arrayToObject: "$data" }
        }
    }
])

The idea here is that you can use $group with _id set to null to get all the data into single document and then use $arrayToObject to get month number as key and all the aggregates for that month as value.

answered Feb 4, 2019 at 21:16

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Casual Coder Over a year ago

I will give it a try

mickl Over a year ago

@SagarVirpara that's because you're using MongoDB below 4.0, you can use k: { $substr:["$_id", 0, -1 ]} as a workaround instead of k: { $toString: "$_id" }

Casual Coder Over a year ago

Okay.. what If I want to get given year's data like 2019 ?

Casual Coder Over a year ago

I am using mongodb version 3.2 so I am also not able to use $arrayToObject.

mickl Over a year ago

@SagarVirpara any possibilities of upgrading your MongoDB ? I'm afraid it's the only way to dynamically generate your keys

|

Collectives™ on Stack Overflow

Mongodb aggregation json format by month result

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related