CODEWITHSUNDEEP
mongodbaggregation-framework
Afeela Ashraf
Afeela Ashraf

Reputation: 45

Multiple Grouping in Mongo Aggregation

My mongo db data is like

{ "_id" : ObjectId("58e879052b614ce778fb7af2"), "email" : "[email protected]", "phone" : 1234 }
{ "_id" : ObjectId("58e879052b614ce778fb7af3"), "email" : "[email protected]", "phone" : 2346 }
{ "_id" : ObjectId("58e879052b614ce778fb7af4"), "email" : "[email protected]", "phone" : 7896 }
{ "_id" : ObjectId("58e879052b614ce778fb7af5"), "phone" : 5789, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af6"), "phone" : 7896, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af7"), "phone" : 3492, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af8"), "phone" : 5555, "email" : "[email protected]" }
{ "_id" : ObjectId("58e87ea96774f88e108b4567"), "phone" : 5789, "email" : "[email protected]" }
{ "_id" : ObjectId("58e880db6774f8130f8b4567"), "phone" : 5789, "email" : "" }
{ "_id" : ObjectId("58e880e56774f81f108b4567"), "phone" : 1234, "email" : "" }
{ "_id" : ObjectId("58e880f96774f83b108b4567"), "phone" : 9846, "email" : "" }
{ "_id" : ObjectId("58e881016774f83b108b4568"), "phone" : 1012, "email" : "" }
{ "_id" : ObjectId("58e8812a6774f8c0108b4567"), "phone" : 1258 }
{ "_id" : ObjectId("58e881496774f80e108b4567"), "phone" : 1012, "email" : "" }

I want to group first based on the email anf then by using phone.

{ "_id" : ObjectId("58e879052b614ce778fb7af2"), "email" : "[email protected]", "phone" : 1234 }
{ "_id" : ObjectId("58e879052b614ce778fb7af3"), "email" : "[email protected]", "phone" : 2346 }
{ "_id" : ObjectId("58e879052b614ce778fb7af6"), "phone" : 7896, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af7"), "phone" : 3492, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af8"), "phone" : 5555, "email" : "[email protected]" }
{ "_id" : ObjectId("58e880f96774f83b108b4567"), "phone" : 9846, "email" : "" }
{ "_id" : ObjectId("58e881016774f83b108b4568"), "phone" : 1012, "email" : "" }
{ "_id" : ObjectId("58e8812a6774f8c0108b4567"), "phone" : 1258 }

I want all the email ids except the null values with correcsponding phone numbers. Also want Phone numbers which doesn't have the same email ids as of the group email. Phone numbers with null email id or without key email also should be included in the output.

Thanking you.

Upvotes: 0

Views: 137

Answers (1)

Julien TASSIN
Julien TASSIN

Reputation: 5212

You need $group and $first aggregate command, to group by email and then phone you must use $cond with a final $project to clean up.

First solution without duplicate suppression

db.data.aggregate([       {$group: {_id: {$cond: {if: {"$eq": ['$email', '']}, else: {email: '$email'}, then: {phone: '$phone', email: '$email'}}}, firstId: {$first: '$_id'}, phone: {$first: '$phone'}}},        {$project: {_id: '$firstId', phone: '$phone', email: '$_id.email'}}     ])
{ "_id" : ObjectId("58e880f96774f83b108b4567"), "phone" : 9846, "email" : "" }
{ "_id" : ObjectId("58e881016774f83b108b4568"), "phone" : 1012, "email" : "" }
{ "_id" : ObjectId("58e880e56774f81f108b4567"), "phone" : 1234, "email" : "" }
{ "_id" : ObjectId("58e880db6774f8130f8b4567"), "phone" : 5789, "email" : "" }
{ "_id" : ObjectId("58e879052b614ce778fb7af8"), "phone" : 5555, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af6"), "phone" : 7896, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af7"), "phone" : 3492, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af3"), "phone" : 2346, "email" : "[email protected]" }
{ "_id" : ObjectId("58e8812a6774f8c0108b4567"), "phone" : 1258 }
{ "_id" : ObjectId("58e879052b614ce778fb7af2"), "phone" : 1234, "email" : "[email protected]" }

Second solution with duplicate suppression

db.data.aggregate([
  {$group: {_id: '$phone', emails: {$addToSet: {email: '$email', _id: '$_id'}}, countNonBlank: {$sum: {$cond: [{$eq: ['$email', '']}, 0,  1]}}}}, {$unwind: {path: '$emails', preserveNullAndEmptyArrays: true}}, 
  {$match: {$or: [{'emails.email': {$ne: ''}}, {"countNonBlank": {$lt: 1}, "emails.email": {$eq: ''}}]}}, 
  {$project: {phone: '$_id', email: '$emails.email', _id: '$emails._id'}}, 
  {$group: {_id: {email: '$email', phone: '$phone'}, firstId: {$first: '$_id'}}}, 
  {$project: {_id: '$firstId', phone: '$_id.phone', email: '$_id.email'}}, 
  {$group: {_id: {$cond: {if: {"$eq": ['$email', '']}, else: {email: '$email'}, then: {phone: '$phone', email: '$email'}}}, firstId: {$first: '$_id'}, phone: {$first: '$phone'}}},        
  {$project: {_id: '$firstId', phone: '$phone', email: '$_id.email'}}     
])
{ "_id" : ObjectId("58e881496774f80e108b4567"), "phone" : 1012, "email" : "" }
{ "_id" : ObjectId("58e880f96774f83b108b4567"), "phone" : 9846, "email" : "" }
{ "_id" : ObjectId("58e879052b614ce778fb7af3"), "phone" : 2346, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af7"), "phone" : 3492, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af6"), "phone" : 7896, "email" : "[email protected]" }
{ "_id" : ObjectId("58e879052b614ce778fb7af8"), "phone" : 5555, "email" : "[email protected]" }
{ "_id" : ObjectId("58e8812a6774f8c0108b4567"), "phone" : 1258 }
{ "_id" : ObjectId("58e879052b614ce778fb7af2"), "phone" : 1234, "email" : "[email protected]" }

Upvotes: 1

Related Questions