Styjun

I want to find the frequency counter of all unique values of a nested field in Mongo Document.

To be more specific, if my collection say db['sample'], consists of the following documents -

'a' : 1, 'b' : 'c' : 25, 'd' : "x", 'e' : 36,

'a' : 2, 'b' : 'c' : 5, 'd' : "xx", 'e' : 36,

'a' : 33, 'b' : 'c' : 25, 'd' : "xx", 'e' : 36,

'a' : 17, 'b' : 'c' : 25, 'd' : "xxx", 'e' : 36,

how can I get the frequency counter of all unique values for the field 'd' ? i.e. my output should be 'd' : "xx" : 2, "x" : 1, "xxx" : 1

Is this even possible ? Appreciate any help on this. Thank you.

I looked up the documentation for aggregation and objectToArray transformation to convert the map to array and tried the following in PyMongo

1)

db['sample'].aggregate([ "$addFields" : "b" : "$objectToArray" : "$b",
 "$unwind" : "$b",
 "$group" : "_id" : "$b.k",
 "count" : "$sum" : "$b.v" ])

This gives the cumulative count of each of the fields where it it possible - 'c' : 25 + 5 + 25 + 25 for example.

2)

db['sample'].aggregate([ "$addFields" : "b" : "$objectToArray" : "$b",
 "$unwind" : "$b", 
 "$group" : "_id" : "$b.k", 
 "count" : "$sum" : 1 ])

This gives the total number of times the fields are present in the document - 'c' : 4, 'd' : 4 etc.

You are basically approaching this the wrong way around. You have a clear path to "b.d" as the key you want to aggregate on, there is no need to convert this to an array:

cursor = db.sample.aggregate([
 "$group": 
 "_id": "$b.d",
 "count": "$sum": 1 
 ,
 "$group": 
 "_id": None,
 "data": "$push": "k": "$_id", "v": "$count" 
 ,
 "$replaceRoot": 
 "newRoot": "$arrayToObject": "$data" 
 
])

for doc in cursor:
 print(doc)

Returns

 'x': 1, 'xx': 2, 'xxx': 1 

But that is actually overkill, since in reality all of the work was done in that initial $group statement. All you really need to do is run that and fetch the results and combine them into a single dictionary as the desired output:

cursor = db.sample.aggregate([
 "$group": 
 "_id": "$b.d",
 "count": "$sum": 1 
 
])

data = list(cursor)

result = reduce(
 lambda x,y:
 dict(x.items() + y['_id']: y['count'] .items()), data,)

print(result)

Which returns exactly the same thing:

 'x': 1, 'xx': 2, 'xxx': 1 

Moreover it does so without the gymnastics required by adding the other aggregation stages and operators, and you did not change what really comes back from the server since the initial $group response is basically:

 "_id" : "xxx", "count" : 1 
 "_id" : "xx", "count" : 2 
 "_id" : "x", "count" : 1 

So the real lesson here is whilst you can do fancy manipulation within an aggregation pipeline, what you really should be considering is you probably should not when the alternative is cleaner and much more readable code.

For reference though all that happens is the additional $group uses $push to create an array with k and v keys as would be expected in the next pipeline stage. Where that next stage uses $replaceRoot to take the output of $arrayToObject from that array created in the previous stage, and basically transforms that into an Object/Dictionary.

By contrast, the reduce is doing exactly the same thing. We basically take the cursor results into a list so python functions can act on that list. Then it's just a matter of traversing the doucments in that list which always have _id as the key and another named property for the "counted" output ( here we used count ) and simply transforming those as the key and value pairs for the final dictionary output.

Just for fun, something based off of your initial attempts might be:

db.sample.aggregate([
 "$addFields": "b": "$objectToArray": "$b" ,
 "$unwind": "$b" ,
 "$group": 
 "_id": 
 "_id": "$b.k",
 "k": "$b.v"
 ,
 "count": "$sum": 1 

 ,
 "$group": 
 "_id": "$_id._id",
 "data": "$push": "k": "$toString": "$_id.k" , "v": "$count" 
 ,
 "$addFields": 
 "data": "$arrayToObject": "$data" 
 
])

Which would return:

 "_id" : "c", "data" : "25" : 3, "5" : 1 
 "_id" : "e", "data" : "36" : 4 
 "_id" : "d", "data" : "xxx" : 1, "xx" : 2, "x" : 1 

Again, the same result without the additional pipeline stages to transform comes from using map and reduce with python:

cursor = db.sample.aggregate([
 "$addFields": "b": "$objectToArray": "$b" ,
 "$unwind": "$b" ,
 "$group": 
 "_id": 
 "_id": "$b.k",
 "k": "$b.v"
 ,
 "count": "$sum": 1 
 ,
 "$group": 
 "_id": "$_id._id",
 "data": "$push": "k": "$_id.k", "v": "$count" 
 
])

data = list(cursor)


result = map(lambda d: 
 '_id': d['_id'],
 'data': reduce(lambda x,y: 
 dict(x.items() + y['k']: y['v'] .items()), d['data'],
 )
,data)