MongoDB data grouping with aggregation

Question

I have data collection like this:

> db.LogBuff.find() { "_id" : ObjectId("578899d5d2b76f77d083f16c"), "SUBJECT" : "DD", "SYS" : "A" } { "_id" : ObjectId("578899d5d2b76f77d083f16d"), "SUBJECT" : "AA", "SYS" : "B" } { "_id" : ObjectId("578899d5d2b76f77d083f16e"), "SUBJECT" : "BB", "SYS" : "A" } { "_id" : ObjectId("578899d5d2b76f77d083f16f"), "SUBJECT" : "AA", "SYS" : "C" } { "_id" : ObjectId("578899d5d2b76f77d083f170"), "SUBJECT" : "BB", "SYS" : "A" } { "_id" : ObjectId("578899d5d2b76f77d083f171"), "SUBJECT" : "BB", "SYS" : "A" } { "_id" : ObjectId("578899d5d2b76f77d083f172"), "SUBJECT" : "CC", "SYS" : "B" }

And I want to extract output as below (with distinct "SYS" values)

{"SUBJECT" : "AA", "SYS" : ["A","B","C","D"]} {"SUBJECT" : "BB", "SYS" : ["A","B","C","D"]} {"SUBJECT" : "CC", "SYS" : ["A","B","C"]}

Here is my code and I am stuck in the middle, please help me to sort this

db.LogBuff.aggregate([{ "$unwind": "$SYS" }, { "$group": { _id: { "_id": "$SUBJECT" }, SYST: { $addToSet: "$SYS" } } }, { "$unwind": "$SYST" }, { "$group": { _id: { "SUBJECT": "$_id", "SYST":"$SYST" } } }])

What's the use of the $unwind operator in the first pipeline when the SYS field is not an array? Why don't you just run db.LogBuff.aggregate([ { "$group": { "_id": "$SUBJECT", "sys": { "$addToSet": "$SYS" } } } ])? — chridam
– chridam, Commented Jul 22, 2016 at 7:55

sergiuz · Accepted Answer · 2016-07-22 07:59:04Z

Just group by the _id, and addToSet the SYS values:

db.LogBuff.aggregate([ { "$group": { _id: { "_id": "$SUBJECT" }, SYST: { $addToSet: "$SYS" } } } ])

No need for unwind, one group should get you the desired result.

Result of group aggregation on your example data:

{ "_id" : { "_id" : "CC" }, "SYST" : [ "B" ] } { "_id" : { "_id" : "BB" }, "SYST" : [ "A" ] } { "_id" : { "_id" : "AA" }, "SYST" : [ "C", "B" ] } { "_id" : { "_id" : "DD" }, "SYST" : [ "A" ] }

Collectives™ on Stack Overflow

MongoDB data grouping with aggregation

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related