Single MongoDB query to aggregate count

Question

I have a collection peopleColl containing records with people data. Each record is uniquely indexed by id and has a managers field of type array.

Example:

{
   id: 123,
   managers: [456, 789]
},
{
   id: 321,
   managers: [555, 789]
}

I want to write a single query to find all people with the same manager, for several ids (managers). So given [456, 555, 789] the desired output would be:

I can do it (slowly) in a for-loop in Python as follows:

idToCount = {id: peopleColl.count({"managers": id}) for id in ids}

Edit: I am primarily interested in solutions <= MongoDB 3.4

Ashh · Accepted Answer · 2018-08-29 11:41:16Z

2

You can try below aggregation in mongodb 3.4.4 and above

db.collection.aggregate([
  { "$unwind": "$managers" },
  { "$group": { "_id": "$managers", "count": { "$sum": 1 }}},
  { "$group": {
    "_id": null,
    "data": {
      "$push": {
        "k": { "$toLower": "$_id" },
        "v": "$count"
      }
    }
  }},
  { "$replaceRoot": { "newRoot": { "$arrayToObject": "$data" }}}
])

Output

[
  {
    "456": 1,
    "555": 1,
    "789": 2
  }
]

edited Aug 29, 2018 at 11:41

answered Aug 29, 2018 at 2:47

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan J McCall Over a year ago

NB. ids are changed from int to string, however, this is a fast workable soln. ty

daemon24 · Accepted Answer · 2018-08-29 03:29:47Z

1

You can try below pipeline.

db.collection.aggregate([
  { "$unwind": "$managers" },
  { "$group": { "_id": "$managers", "count": { "$sum": 1 }}}
])

Output:

{'_id': 456, 'count': 1},
{'_id': 555, 'count': 1},
{'_id': 789, 'count': 2}

So you can loop through and create the Id-Count mapping

result = db.collection.aggregate([
      { "$unwind": "$managers" },
      { "$group": { "_id": "$managers", "count": { "$sum": 1 }}}
    ])

iD_Count = {}
result.forEach(function(d, i) {
iD_Count[d._id] = d.count;
})

iD_Count:

edited Aug 29, 2018 at 3:29

answered Aug 29, 2018 at 3:06

daemon24

1,4483 gold badges12 silver badges24 bronze badges

Comments

s7vr · Accepted Answer · 2018-08-29 14:01:23Z

1

You can try below aggregation in 3.6.

db.colname.aggregate([
  {"$unwind":"$managers"},
  {"$group":{"_id":"$managers","count":{"$sum":1}}},
  {"$group":{
    "_id":null,
    "managerandcount":{"$mergeObjects":{"$arrayToObject":[[["$_id","$count"]]]}}
  }},
  {"$replaceRoot":{"newRoot":"$managerandcount"}}
])

answered Aug 29, 2018 at 14:01

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Collectives™ on Stack Overflow

Single MongoDB query to aggregate count

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related