mongodb: counting unique array elements

Question

I'm trying to get an overall count of array elements across an entire collection.

The input collection is

 db.test.insert(    { "_id" : 1, "item" : "A", "hx" : [ { "label" : "new"    , "price" : 10.99 , "category" : "P1"} , 
                                                { "label" : "active" , "price" : 12.99 , "category" : "P2"} ] } )

 db.test.insert(    { "_id" : 2, "item" : "B", "hx" : [ { "label" : "new"    , "price" : 10.99 , "category" : "P2"} , 
                                                { "label" : "active" , "price" :  8.99 , "category" : "P3"} ] } )

 db.test.insert(    { "_id" : 3, "item" : "C", "hx" : [ { "label" : "new"    , "price" : 10.99 , "category" : "P1"} , 
                                                { "label" : "active" , "price" : 15.99 , "category" : "P4"} ] } )

I've tried variations of

 a = db.test.aggregate(
    [ { 
          $group: { 
                   _id : "$cat", 
                   cat : { $addToSet : "$hx.category" } 
                  } 
      },  
      { $unwind : "$hx.category" } , 
      { $group : { _id : "$cat", count: { $sum : 1 } } }
    ])

to get to

  db.res.insert({ "category" : "P1" , "count" : 2 })
  db.res.insert({ "category" : "P2" , "count" : 2 })
  db.res.insert({ "category" : "P3" , "count" : 1 })
  db.res.insert({ "category" : "P4" , "count" : 1 })

but the result I get is

 > a
 { "result" : [ ], "ok" : 1 }

Eventually I'd like to run this within pymongo: any hint towards a pymongo implementation is much appreciated.

score 2 · Accepted Answer · answered Apr 13 '15 at 15:37

You could try the following aggregation:

>>> from bson.son import SON
>>> pipeline = [
...     {"$unwind": "$hx"},
...     {"$group": {"_id": "$hx.category", "count": {"$sum": 1}}},
...     {"$project": SON([("count", -1), ("_id", -1)])}
... ]
>>> list(db.test.aggregate(pipeline))
[{u'count': 1, u'_id': u'P3'}, {u'count': 2, u'_id': u'P2'}, {u'count': 1, u'_id': u'P4'}, {u'count': 2, u'_id': u'P1'}]

score 1 · Answer 2 · answered Apr 13 '15 at 15:37

1

I assume that your category field is a set with unique entries.

db.test.aggregate([ 
    {$unwind: "$hx"},
    {$group: {_id: "$hx.category", count:{$sum:1}}}
])

answered Apr 13 '15 at 15:37

RomanKonz

1,027
1
8
15

score 0 · Answer 3 · answered Apr 13 '15 at 15:31

0

Looks like a task for a Map-Reduce

answered Apr 13 '15 at 15:31

Azee

1,809
17
23

mongodb: counting unique array elements

3 Answers3