1

Currently I'm using aggregation in MongoDB. There is a field with province and religion in my collections. I'm doing this

const data = await submit.aggregate([ { "$group": { _id: { province: "$province" ,religion:"$religion"}, count: { $sum: 1 } } }, ])

My output Looks like this:

[ { _id: { religion: 'a', province: 'aa' }, count: 1 }, { _id: { religion: b, province: 'bb' }, count: 2 }, { _id: { religion: 'c', province: 'bb'}, count: 2 }, { _id: { religion: 'd', province: 'cc' }, count: 1 } ]

Expect Output:

[ { _id: { religion: 'a ' }, count: 1 }, { _id: { religion: 'a' }, count: 1 }, { _id: { religion: null }, count: 6 }, { _id: { religion: 'c' }, count: 1 }, { _id: { religion: 'd' }, count: 2 }, { _id: { religion: 'e' }, count: 6 }, { _id: { religion: 'f' }, count: 15 }, { _id: { religion: 'g' }, count: 2 }, ] [ { _id: { province: 'aa' }, count: 19 }, { _id: { province: 'bb' }, count: 2 }, { _id: { province: 'cc' }, count: 21 }, ]
Improve this question
2
  • 1
    Share sample docs. Your expected o/p is invalid json. There should be a key. Commented May 24, 2022 at 16:12
  • Key is religion and province Commented May 24, 2022 at 16:15

1 Answer 1

Reset to default
1

You seek 2 different $group at the same time -- this is exactly what $facet is for. Think of $facet like "multi-group." Given an input set similar to the following:

 { religion: 'a', province: 'aa' }, { religion: 'b', province: 'aa' }, { religion: 'c', province: 'aa' }, { religion: 'c', province: 'bb' }, { religion: 'd', province: 'bb' }, { religion: 'e', province: 'cc' }, { religion: 'f', province: 'aa' }, { religion: 'f', province: 'aa' }, { religion: 'f', province: 'aa' }, { religion: 'f', province: 'cc' }

Then this pipeline:

db.foo.aggregate([ {$facet: { "by_religion": [ {$group: {_id: '$religion', N:{$sum:1}}} ], "by_province": [ {$group: {_id: '$province', N:{$sum:1}}} ], }} ]);

yields this output:

{ "by_religion" : [ { "_id" : "b", "N" : 1 }, { "_id" : "e", "N" : 1 }, { "_id" : "d", "N" : 1 }, { "_id" : "a", "N" : 1 }, { "_id" : "f", "N" : 4 }, { "_id" : "c", "N" : 2 } ], "by_province" : [ { "_id" : "bb", "N" : 2 }, { "_id" : "cc", "N" : 2 }, { "_id" : "aa", "N" : 6 } ] }

The OP seeks to further refine the output by doing some data-as-LVAL workup and although this is in general considered a poor design practice, it has certain useful applications. Add this stage after $facet:

 ,{$project: { // Reading this from insider-out: // We use $map to turn the array of objects: // [ {_id:'d',N:1},{_id:'f',N:4}, ... ] // into an array of K-v pairs (array of array): // [ ['d',1] , ['f',4] , ... ] // That sets us up for $arrayToObject which will take // that array of arrays and turn it into an object: // {'d':1, 'f':4, ... } // The target field name is the same as the input so // we are simply overwriting the field. "by_religion": {$arrayToObject: {$map: { input: '$by_religion', in: [ '$$this._id', '$$this.N' ] }} }, "by_province": {$arrayToObject: {$map: { input: '$by_province', in: [ '$$this._id', '$$this.N' ] }} } }}

to yield:

{ "by_religion" : { "d" : 1, "b" : 1, "c" : 2, "f" : 4, "a" : 1, "e" : 1 }, "by_province" : { "bb" : 2, "cc" : 2, "aa" : 6 } }

A variation on the lval/rval workup uses this $project instead of the one immediately above:

 ,{$project: { "by_religion": {$map: { input: '$by_religion', in: {$arrayToObject: [ [{k:'$$this._id',v:'$$this.N'}] ]} }}, "by_province": {$map: { input: '$by_province', in: {$arrayToObject: [ [{k:'$$this._id',v:'$$this.N'}] ]} }}, }}

which yields an array:

{ "by_religion" : [ {"b" : 1}, {"c" : 2}, {"a" : 1}, {"f" : 4}, {"d" : 1}, {"e" : 1} ], "by_province" : [ {"cc" : 2}, {"aa" : 6}, {"bb" : 2} ] }
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

What if I just want to get output like this: "by_religion" : [ { "b" : 1 }, { "d" : 2 }, ] i.e, b and d is the name of religion and 1 and 2 is the count
If you really want the label by_religion then simply remove the second $group (by_province) in the $facet above. To simplify it further, for a single grouping, just do db.foo.aggregate([ {$group: {_id: '$religion', N:{$sum:1}}} ])
What if I want to show my output like this: [ { "by_religion": [ { "hello": 63 }, { "hey": 9 } ], "by_province": [ { "abcdef": 6 }, { "ghij": 1 }]}]
That is a poor data design practice. Field names should be things like "count", "region", etc. and the values things like "hey" and "hello" and 9. Making "hello" a field name is perhaps convenient for ease of display purposes but it clobbers the meaning of the key:value pair. I will update the answer, however, to show it.
[stackoverflow.com/questions/72403502/…. Check this out, This is what I want to do.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.