Group by multiple fields and format result in mongodb?

Question

I have a collection that stores data like below.

{ 
    "_id" : ObjectId("5c52ba3177c895e98b3f41f7"), 
    "email" : "[email protected]", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26B", 
    "leadName" : "LeadB", 
    "text" : "call client AEVUSSMS26B", 
    "reminderAt" : ISODate("2019-02-22T11:54:24.123+0000")
}
{ 
    "_id" : ObjectId("5c52bdc277c895e98b3f41f8"), 
    "email" : "[email protected]", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26A", 
    "leadName" : "LeadA", 
    "text" : "call client AEVUSSMS26A", 
    "reminderAt" : ISODate("2019-02-22T11:55:24.123+0000")
}
{ 
    "_id" : ObjectId("5c52bdcd77c895e98b3f41f9"), 
    "email" : "[email protected]", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26B",
    "leadName" : "LeadB",  
    "text" : "call client AEVUSSMS26B", 
    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
}
{ 
    "_id" : ObjectId("5c5403c7407b4aefc00dae39"), 
    "email" : "[email protected]", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26C", 
    "leadName" : "LeadC", 
    "text" : "call client AEVUSSMS26C", 
    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
}
{ 
    "_id" : ObjectId("5c54040f407b4aefc00dae3a"), 
    "email" : "[email protected]", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26C", 
    "leadName" : "LeadC", 
    "text" : "call client AEVUSSMS26C", 
    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
}
{ 
    "_id" : ObjectId("5c540426407b4aefc00dae3b"), 
    "email" : "[email protected]", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26C",
    "leadName" : "LeadC",  
    "text" : "call client AEVUSSMS26C", 
    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
}
//I have 1000s such users

One user can have multiple leads. One lead can have multiple reminders.

I want to query and format result such that One document represent one user. Each doc will have a lead field which further stores all reminders. I am expecting something like below :

{ 
    "userId" : "59b94212e4b0a459d044cd31", 
    "email":"[email protected]",
    "mobile":"1234567890"
    "leads" : [
        {
            "_id" : {
                "leadId" : "AEVUSSMS26C", 
                "leadName" : "LeadC"
            }, 
            "leadData" : [
                {
                    "_id" : ObjectId("5c5403c7407b4aefc00dae39"), 
                    "text" : "call client AEVUSSMS26C", 
                    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
                }, 
                {
                    "_id" : ObjectId("5c54040f407b4aefc00dae3a"), 
                    "text" : "call client AEVUSSMS26C", 
                    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
                }, 
                {
                    "_id" : ObjectId("5c540426407b4aefc00dae3b"), 
                    "text" : "call client AEVUSSMS26C", 
                    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
                }
            ]
        }, 
        {
            "_id" : {
                "leadId" : "AEVUSSMS26A", 
                "leadName" : "LeadA"
            }, 
            "leadData" : [
                {
                    "_id" : ObjectId("5c52bdc277c895e98b3f41f8"), 
                    "text" : "call client AEVUSSMS26A", 
                    "reminderAt" : ISODate("2019-02-22T11:55:24.123+0000")
                }
            ]
        }, 
        {
            "_id" : {
                "leadId" : "AEVUSSMS26B", 
                "leadName" : "LeadB"
            }, 
            "leadData" : [
                {
                    "_id" : ObjectId("5c52ba3177c895e98b3f41f7"), 
                    "text" : "call client AEVUSSMS26B", 
                    "reminderAt" : ISODate("2019-02-22T11:54:24.123+0000")
                }, 
                {
                    "_id" : ObjectId("5c52bdcd77c895e98b3f41f9"), 
                    "text" : "call client AEVUSSMS26B", 
                    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
                }
            ]
        }
    ]
}

This is what I have done so far. Problems I am facing : 1.how do I set email , mobile at the root level? 2.How to pass limited data as in response instead of $$ROOT?

db.getCollection("MyCollection").aggregate([
{ 
  "$group": {
        "_id": {
            "leadId": "$leadId",
            "userId": "$userId"   
        },
        "leadData": { $push: "$$ROOT" } 
    }
},
{ 
  "$group": {
        "_id": "$_id.userId",
        "leads": { 
            "$push": "$$ROOT",
        }
    }
}

])

Ashh · Accepted Answer · 2019-02-07 12:03:33Z

2

You are forgetting the use $first aggregation in the $group stage

db.collection.aggregate([
  { "$group": {
    "_id": { "leadId": "$leadId", "userId": "$userId", "leadName": "$leadName" },
    "email": { "$first": "$email" },
    "mobile": { "$first": "$mobile" },
    "leadData": {
      "$push": {
        "text": "$text",
        "_id": "$_id",
        "reminderAt": "$reminderAt"
      }
    }
  }},
  { "$group": {
    "_id": "$_id.userId",
    "email": { "$first": "$email" },
    "mobile": { "$first": "$mobile" },
    "leads": {
      "$push": {
        "_id": { "leadName": "$_id.leadName", "leadId": "$_id.leadId" },
        "leadData": "$leadData"
      }
    }
  }}
])

edited Feb 7, 2019 at 12:03

answered Feb 7, 2019 at 11:49

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

HIRA THAKUR Over a year ago

for leadName, should I add field at first group in the pipeline or second?

HIRA THAKUR Over a year ago

Thanks man. But I am really confused about passing three fields while grouping. do I need to groupby fields just to pass on the data to next stage in pipeline? Is it going to groupby three times in first pipeline?

Ashh Over a year ago

$group aggregation just knows the expression you pass in the _id: <expression> and doesn't know about the others. So to access the other fields you have to use group accumulator operators

Collectives™ on Stack Overflow

Group by multiple fields and format result in mongodb?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related