Mongo aggregation by array elements
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 aggregation of the location (different number of areas). something like
Hyderabad 2,
Kannur 1,
Chennai 2,
Bengaluru 1,
Mysore 1,
Ballary 1
source to share
By using aggregation, you cannot get the exact result you want. One of the limitations of the aggregation pipeline is that it cannot be converted values
to keys
into an output document.
For example, Kannur
is one of the field values location
in the input document. In your desired output structure, it should be key("kannur":1)
. This is not possible with aggregation. While this can be used to achieve map-reduce
, you can get a very closely related and useful structure using aggregation.
-
Unwind
an array of locations. -
Group
by location fields, get the number of distinct locations using the $ sum operator. -
Group
all the documents 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}}
])
O / p example:
{
"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
}
]
}
source to share