Mongo: get hashmap in aggregate

Question

I have collection db.problems. Each document contains nested area and category objects. Document's example:

{
    _id: 1,
    text: 'Hello',
    area: {_id: 23, title: 'Area 61'},
    category: {_id: 12, title: 'Just a category'}
}

I'm trying to count problems in every area by category and get something like this:

[
    {
        area: {_id: 2, title: 'Area 61'},
        categoriesStats: {
            12: {title: 'Just a category', problemCount: 123},
            42: {title: 'Another category', problemCount: 11}
        }
   },
   {...}
]

The main thing is categoriesStats must be a hash (with category's id as a key)

What I've come up at the moment:

db.problems.aggregate([
    {$group: {
        _id: {areaId: '$area._id', categoryId: '$category._id'},
        problemCount: {$sum: 1},
        area: {$first: '$area'},
        category: {$first: '$category'}
    }},
    {$group: {
        _id: '$_id.areaId',
        area: {$first: '$area'},
        categoriesStats: {
            $push: {
                problemCount: '$problemCount',
                category: '$category'
            }
        }
    }},
    {$project: {_id: 0, area: 1, categoriesStats: 1}}
])

The result of this query:

{
    "result": [ 
        {
            "area": {"_id": 37, "name": "Some area"},
            "categoriesStats": [
                {
                    "problemCount": 1,
                    "category": {"_id": 4, "title": "Just a cat"}
                },
                {
                    "problemCount": 1,
                    "category": {"_id": 3, "title": "Misc"}
               }
            ]
        }, 
        {
            "area": {"_id": 36, "name": "wow such area"},
            "categoriesStats": [ 
                {
                    "problemCount": 1,
                    "category": {"_id": 4, "title": "Just a cat"}
                }, 
                {
                    "problemCount": 2,
                    "category": {"_id": 3, "title": "Misc"}
                }
            ]
        }
    ],
    "ok": 1
}

As you can see, I've managed to get almost needed result, but I can't get categoriesStats as hash.

I've tried queries in $project stage like {$project: {'$category._id': '$categories'}, but

"$expressions are not allowed at the top-level of $project"

Also I've tried to predefine query like this:

(3 is _id of some category)

{$group: ...},
{$project: {
 'categoriesStats.3': {$cond: [{$eq: ['$category._id', 3]}, '$category', null]}}, 
 //Same field for every category _id
{$group: ...}

but in this case I can't get this hash through $group stage

---

So, the question is, is there anyway to get categoriesStats in hashmap form?

Answer 1

You can modify the result from the aggregation by using the cursor method forEach() to iterate the cursor and access the documents, as in the following example:

var cur = db.problems.aggregate([
    {$group: {
        _id: {areaId: '$area._id', categoryId: '$category._id'},
        problemCount: {$sum: 1},
        area: {$first: '$area'},
        category: {$first: '$category'}
    }},
    {$group: {
        _id: '$_id.areaId',
        area: {$first: '$area'},
        categoriesStats: {
            $push: {
                problemCount: '$problemCount',
                category: '$category'
            }
        }
    }},
    {$project: {_id: 0, area: 1, categoriesStats: 1}}
]),
results = [];

cur.forEach(function (res){
    var obj = { 
        "categoriesStats": {} 
    };
    var category = {};
    obj.area = res.area;
    res.categoriesStats.forEach(function(c){
        var cat = {};        
        cat["title"] = c.category.title;
        cat["problemCount"] = c.problemCount;        
        obj["categoriesStats"][c.category._id.toString()] = cat;
    });

    results.push(obj);
});

Check the demo below

var cur = {
    "result": [ 
        {
            "area": {"_id": 37, "name": "Some area"},
            "categoriesStats": [
                {
                    "problemCount": 1,
                    "category": {"_id": 4, "title": "Just a cat"}
                },
                {
                    "problemCount": 1,
                    "category": {"_id": 3, "title": "Misc"}
               }
            ]
        }, 
        {
            "area": {"_id": 36, "name": "wow such area"},
            "categoriesStats": [ 
                {
                    "problemCount": 1,
                    "category": {"_id": 4, "title": "Just a cat"}
                }, 
                {
                    "problemCount": 2,
                    "category": {"_id": 3, "title": "Misc"}
                }
            ]
        }
    ],
    "ok": 1
};
var results = [];
cur.result.forEach(function (doc){
    var obj = { 
        "categoriesStats": {} 
    };
    var category = {};
    obj.area = doc.area;
    doc.categoriesStats.forEach(function(c){
        var cat = {};        
        cat["title"] = c.category.title;
        cat["problemCount"] = c.problemCount;        
        obj["categoriesStats"][c.category._id.toString()] = cat;
    });
    
    results.push(obj);
});

pre.innerHTML = JSON.stringify(results);

<pre id="pre"></pre>