mongodb aggregate sum item as nested data

Question

Here is my some sample data in collection sale

[ 
 {group:2, item:a, qty:3 },
 {group:2, item:b, qty:3 },
 {group:2, item:b, qty:2 },
 {group:1, item:a, qty:3 },
 {group:1, item:a, qty:5 },
 {group:1, item:b, qty:5 }
]

and I want to query data like below and sort the popular group to the top

[
 { group:1, items:[{name:'a',total_qty:8},{name:'b',total_qty:5} ],total_qty:13 },
 { group:2, items:[{name:'a',total_qty:3},{name:'b',total_qty:5} ],total_qty:8 },
]

Actually we can loop in server script( php, nodejs ...) but the problem is pagination. I cannot use skip to get the right result.

Himanshu Sharma · Accepted Answer · 2019-09-20 10:05:18Z

The following query can get us the expected output:

db.collection.aggregate([
    {
        $group:{
            "_id":{
                "group":"$group",
                "item":"$item"
            },
            "group":{
                $first:"$group"
            },
            "item":{
                $first:"$item"
            },
            "total_qty":{
                $sum:"$qty"
            }
        }
    },
    {
        $group:{
            "_id":"$group",
            "group":{
                $first:"$group"
            },
            "items":{
                $push:{
                    "name":"$item",
                    "total_qty":"$total_qty"
                }
            },
            "total_qty":{
                $sum:"$total_qty"
            }
        }
    },
    {
        $project:{
            "_id":0
        }
    }
]).pretty()

Data set:

{
    "_id" : ObjectId("5d84a37febcbd560107c54a7"),
    "group" : 2,
    "item" : "a",
    "qty" : 3
}
{
    "_id" : ObjectId("5d84a37febcbd560107c54a8"),
    "group" : 2,
    "item" : "b",
    "qty" : 3
}
{
    "_id" : ObjectId("5d84a37febcbd560107c54a9"),
    "group" : 2,
    "item" : "b",
    "qty" : 2
}
{
    "_id" : ObjectId("5d84a37febcbd560107c54aa"),
    "group" : 1,
    "item" : "a",
    "qty" : 3
}
{
    "_id" : ObjectId("5d84a37febcbd560107c54ab"),
    "group" : 1,
    "item" : "a",
    "qty" : 5
}
{
    "_id" : ObjectId("5d84a37febcbd560107c54ac"),
    "group" : 1,
    "item" : "b",
    "qty" : 5
}

Output:

{
    "group" : 2,
    "items" : [
        {
            "name" : "b",
            "total_qty" : 5
        },
        {
            "name" : "a",
            "total_qty" : 3
        }
    ],
    "total_qty" : 8
}
{
    "group" : 1,
    "items" : [
        {
            "name" : "b",
            "total_qty" : 5
        },
        {
            "name" : "a",
            "total_qty" : 8
        }
    ],
    "total_qty" : 13
}

Ashh · Accepted Answer · 2019-09-20 09:20:28Z

1

You need to use $group aggregation with $sum and $push accumulator

db.collection.aggregate([
  { "$group": {
    "_id": "$group",
    "items": { "$push": "$$ROOT" },
    "total_qty": { "$sum": "$qty" }
  }},
  { "$sort": { "total_qty": -1 }}
])

answered Sep 20, 2019 at 9:20

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

2 Comments

Dara Vichit Over a year ago

thanks it helpful for me, but will show duplicate item name and large array in each items, can we distinct item and sum duplicate item together? however I can use two times of group to get my wanted. Just want to do in the better ways

Ashh Over a year ago

No better way. You have to use two $group stages here.

Collectives™ on Stack Overflow

mongodb aggregate sum item as nested data

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related