"Structured" grouping query in MongoDB

Question

I have the following items collection :

[{
    "_id": 1,
    "manufactureId": 1,
    "itemTypeId": "Type1"
},
{
    "_id": 2,
    "manufactureId": 1,
    "itemTypeId": "Type2"
},
{
    "_id": 3,
    "manufactureId": 2,
    "itemTypeId": "Type1"
}]

I would like to create a query that will return the amount of items for each item type that each manufacturer have in the following structure (or something similar) :

[
    {
        _id:1, //this would be the manufactureId
        itemsCount:{
            "Type1":1, //Type1 items count
            "Type2":1  //...
        }
    },
    {
        _id:2,
        itemsCount:{
            "Type1":1
        }
    }
]

I have tried to use the aggregation framework but i couldn't figure out if there is a way to create a "structured" groupby queries with it. I can easily achieve the desired result by post-processing this simple aggregation query result :

db.items.aggregate([{$group:{_id:{itemTypeId:"$itemTypeId",manufactureId:"$manufactureId"},count:{$sum:1}}}])

but if possible I prefer not to post-process the result.

Possible duplicate of count array occurrences across all documents with mongo — Sede
– Sede, Commented Dec 12, 2015 at 12:41

Dmytro Shevchenko · Accepted Answer · 2015-12-12 13:46:58Z

Data stays data

I would rather use this query which, I believe, will give you the closest data structure to what you want, without post-processing.

Query

db.items.aggregate(
{
    $group:
    {
        _id:
        {
            itemTypeId: "$itemTypeId",
            manufactureId: "$manufactureId"
        },
        count:
        {
            $sum: 1
        }
    },
},
{
    $group:
    {
        _id: "$_id.manufactureId",
        itemCounts:
        {
            "$push":
            {
                itemTypeId: "$_id.itemTypeId",
                count: "$count"
            }
        }
    }
})

Output

{
    "_id" : 1,
    "itemCounts" : [
        {
            "itemTypeId" : "Type1",
            "count" : 1
        },
        {
            "itemTypeId" : "Type2",
            "count" : 1
        }
    ]
},
{
    "_id" : 2,
    "itemCounts" : [
        {
            "itemTypeId" : "Type1",
            "count" : 1
        }
    ]
}

Data transformed to object fields

This is actually an approach that I wouldn't advice in general. It is harder to manage in your application, because the field names between different objects will be inconsistent and you won't know what object fields to expect in advance. This would be a crucial point if you use a strongly typed language—automatic data binding to your domain objects will become impossible.

Anyway, the only way to get the exact data structure you want is to apply post-processing.

Query

db.items.aggregate(
{
    $group:
    {
        _id:
        {
            itemTypeId: "$itemTypeId",
            manufactureId: "$manufactureId"
        },
        count:
        {
            $sum: 1
        }
    },
},
{
    $group:
    {
        _id: "$_id.manufactureId",
        itemCounts:
        {
            "$push":
            {
                itemTypeId: "$_id.itemTypeId",
                count: "$count"
            }
        }
    }
}).forEach(function(doc) {
    var obj = {
        _id: doc._id,
        itemCounts: {}
    };

    doc.itemCounts.forEach(function(typeCount) {
        obj.itemCounts[typeCount.itemTypeId] = typeCount.count;
    });

    printjson(obj);
})

Output

{ "_id" : 1, "itemCounts" : { "Type1" : 1, "Type2" : 1 } }
{ "_id" : 2, "itemCounts" : { "Type1" : 1 } }

Collectives™ on Stack Overflow

"Structured" grouping query in MongoDB

1 Answer 1

Data stays data

Query

Output

Data transformed to object fields

Query

Output

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Data stays data

Query

Output

Data transformed to object fields

Query

Output

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related