I have an aggregate query where I am grouping based on lots of fields. I want to take all the properties from the _id and have them on the top level objects. What's the best way to do that? I have created a playground showing a simplified version of the problem:

https://mongoplayground.net/p/BjZKXYkH9Jb

I can get it to work by repeating each the _id value as fields in the $group and using $project to remove the _id. Is there a better way to achieve the same result without listing each field twice? I have many fields in the _id.

Here is an example:

Data:

[
  {
    "name": "Adam",
    "country": "Argentina",
    "colour": "Red"
  },
  {
    "name": "Betty",
    "country": "Belgium",
    "colour": "Blue"
  },
  {
    "name": "Clive",
    "country": "Argentina",
    "colour": "Red"
  },
  {
    "name": "Daniel",
    "country": "Argentina",
    "colour": "Blue"
  },
  {
    "name": "Edna",
    "country": "Belgium",
    "colour": "Blue"
  }
]

Query:

    db.collection.aggregate([
  {
    "$group": {
      _id: {
        "country": "$country",
        "colour": "$colour"
      },
      "names": {
        "$push": "$name"
      },
      "country": {
        "$first": "$country"
      },
      "colour": {
        "$first": "$colour"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "country": 1,
      "colour": 1,
      "names": 1
    }
  }
])

Result:

[
  {
    "colour": "Blue",
    "country": "Belgium",
    "names": [
      "Betty",
      "Edna"
    ]
  },
  {
    "colour": "Red",
    "country": "Argentina",
    "names": [
      "Adam",
      "Clive"
    ]
  },
  {
    "colour": "Blue",
    "country": "Argentina",
    "names": [
      "Daniel"
    ]
  }
]

CodePudding user response：

You can access properties of _id by dot notation in $project stage,

db.collection.aggregate([
  {
    "$group": {
      _id: {
        "country": "$country",
        "colour": "$colour"
      },
      "names": { "$push": "$name" }
    }
  },
  {
    $project: {
      _id: 0,
      country: "$_id.country",
      colour: "$_id.colour",
      names: 1
    }
  }
])

Playground

The second option, if you don't want to merge manually, try merge operation,

• $mergeObjects to merge _id object with required fields
• $replaceRoot to replace object root

db.collection.aggregate([
  {
    "$group": {
      _id: {
        "country": "$country",
        "colour": "$colour"
      },
      "names": { "$push": "$name" }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: ["$_id", { names: "$names" }]
      }
    }
  }
])

Playground