CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
HIlo
HIlo

Reputation: 54

MongoDB, how to group documents

My collection has data like this:

[
  {
    "code": 1,
    "location": "1 QUEEN STREET",
    "_id": "1",
  },
  {
    "code": 1,
    "location": "2 KING STREET",
    "_id": "2"
  },
  {
    "code": 2,
    "location": "1 QUEEN STREET",
    "_id": "3"
  },
  {
    "code": 2,
    "location": "2 KING STREET",
    "_id": "4"
  },
  {
    "code": 2,
    "location": "2 KING STREET",
    "_id": "5"
  }

]

Looking for out that would group documents by "location" and have a set of codes added to each document that contains the count for each unique code.

Output would be like this:

[
  { "location": "1 QUEEN STREET", "codes":["code1":1,"code2":1]},
  { "location": "2 KING STREET", "codes":["code1":1,"code2":2]}
]

I tried queries like these:

aggregate([{ $group : { _id : "$location", codes: { $addToSet: "$code",count: { $sum: 1 } } } }])

aggregate([{ $group : { _id : "$location", codes: {$group:{_id:"$_code",count:{$sum :1}}} } }])

Thanks for any help.

Upvotes: 2

Views: 3428

Answers (2)

sergiuz
sergiuz

Reputation: 5529

aggregate(
[
    { $group : { 
            _id : { location: "$location", code: "$code"},
            count: { $sum:1 }
        } 
    },
    { $group: {
            _id: "$_id.location", 
            codes: {$addToSet: {"code":"$_id.code", "count":"$count"}}
        } 
    },
    { $project: { location: "$_id", codes: 1, _id: 0}},
]
)

Result:

{ "codes" : [ { "code" : 1, "count" : 1 }, { "code" : 2, "count" : 1 } ], "location" : "1 QUEEN STREET" }
{ "codes" : [ { "code" : 1, "count" : 1 }, { "code" : 2, "count" : 2 } ], "location" : "2 KING STREET" }

Note: Youre desired output cannot be achived, thhere is a format error on the elements in the list, you should use a dict, {}.

Upvotes: 3

hyades
hyades

Reputation: 3160

You can use two $group steps inside an aggregate operation. In first you are counting the number of docs per code and per location. Next one you only look at location, and $push the count along with the corresponding code into an array

[
{$group: {_id: {code: '$code', location: '$location'}, count: {$sum: 1}}},
{$group: {_id: '$_id.location', codes: {$push: {code: '$_id.code', count: '$count'}}}}
]

Upvotes: 0

Related Questions