Mongo aggregation on array elements

Question

I have a mongo document like

{ "_id" : 12, "location" : [ "Kannur","Hyderabad","Chennai","Bengaluru"] }
{ "_id" : 13, "location" : [ "Hyderabad","Chennai","Mysore","Ballary"] }

From this how can I get the location aggregation (distinct area count). some thing like

Hyderabad 2, 
Kannur 1, 
Chennai 2, 
Bengaluru 1, 
Mysore 1, 
Ballary 1

Answer 1

Using aggregation you cannot get the exact output that you want. One of the limitations of aggregation pipeline is its inability to transform values to keys in the output document.

For example, Kannur is one of the values of the location field, in the input document. In your desired output structure it needs to be the key("kannur":1). This is not possible using aggregation. While, this can be used achieving map-reduce, you can however get a very closely related and useful structure using aggregation.

• Unwind the location array.
• Group by the location fields, get the count of individual locations using the $sum operator.
• Group again all the documents once again to get a consolidated array of results.

Code:

db.collection.aggregate([
{$unwind:"$location"},
{$group:{"_id":"$location","count":{$sum:1}}},
{$group:{"_id":null,"location_details":{$push:{"location":"$_id",
                                               "count":"$count"}}}},
{$project:{"_id":0,"location_details":1}}
])

Sample o/p:

{
        "location_details" : [
                {
                        "location" : "Ballary",
                        "count" : 1
                },
                {
                        "location" : "Mysore",
                        "count" : 1
                },
                {
                        "location" : "Bengaluru",
                        "count" : 1
                },
                {
                        "location" : "Chennai",
                        "count" : 2
                },
                {
                        "location" : "Hyderabad",
                        "count" : 2
                },
                {
                        "location" : "Kannur",
                        "count" : 1
                }
        ]
}