CODEWITHSUNDEEP
mongodbgroup-bypymongo
Bhavani Ravi
Bhavani Ravi

Reputation: 2291

Mongo groupby an object key but preserve the whole object/dict

I am using mongodb and trying to construct a groupby query. A single document looks something like this

{
    "_id" : "ca5ff110697611e89c2d9a0012d6e110",
    "CreatedAt" : ISODate("2018-06-06T16:15:55.000Z,
    "CreatedBy" : {
        "Email" : "[email protected]",
        "Name" : "Neo",
        "_id" : "User001"
    }, 
    },
    "Salary" : 200000
    "Branch" : "C2"
}

and my groupby query looks something like this 

[{'$project': {'Branch': 1, 'CreatedAt.year':{$year:"$CreatedAt"}, 'CreatedBy': 1, 'Salary': 1}}, 
{'$group': {'_id': {'Branch': '$Branch', 
                    'Salary': '$Salary', 
                    'CreatedAt.year': "$CreatedAt.year" , 
                    'CreatedBy': '$CreatedBy._id'}, 
            'Salary': {'$sum': '$Salary'},
            'CreatedBy': "$CreatedBy"
            }}, 
           ]

As you see the CreatedBy is a nested field and grouping on its _id field results in just _id field but I want to preserve the name and email while grouping. Grouping by the whole CreatedBy because the user may change their name at some point and the old documents will have old name.

Upvotes: 0

Views: 42

Answers (1)

felix
felix

Reputation: 9295

The $group stage needs an accumulator operator for each field. To keep the CreatedBy field, you can use $first for example:

the new query: 

db.collection.aggregate([
  {
    "$project": {
      "Branch": 1,
      "CreatedAt.year": {
        $year: "$CreatedAt"
      },
      "CreatedBy": 1,
      "Salary": 1
    }
  },
  {
    "$group": {
      "_id": {
        "Branch": "$Branch",
        "Salary": "$Salary",
        "CreatedAtYear": "$CreatedAt.year",
        "CreatedBy": "$CreatedBy._id"
      },
      "Salary": {
        "$sum": "$Salary"
      },
      "CreatedBy": {
        "$first": "$CreatedBy"
      }
    }
  }
])

result: 

[
  {
    "CreatedBy": {
      "Email": "[email protected]",
      "Name": "Neo",
      "_id": "User001"
    },
    "Salary": 200000,
    "_id": {
      "Branch": "C2",
      "CreatedAtYear": 2018,
      "CreatedBy": "User001",
      "Salary": 200000
    }
  }
]

you can try it online here: mongoplayground.net/p/cVNHRD5Rv21

Upvotes: 1

Related Questions