Home > Software design >  MongoDB - How to write a nested group aggregation query
MongoDB - How to write a nested group aggregation query

Time:03-31

I have a collection in this format:

{
  "place":"land",
  "animal":"Tiger",
  "name":"xxx"
},
{
  "place":"land",
  "animal":"Lion",
  "name":"yyy"
}

I want to result to be something like this:

{
  "place":"land".
  "animals":{"Lion":"yyy", "Tiger":"xxx"}
}

I wrote the below query. I think there needs to be another group stage but not able to write it.

db.collection.aggregate({
  '$group': {
  '_id':{'place':'$place', 'animal':'$animal'},
  'animalNames': {'$addToSet':'$name'}
  }
})

What changes need to be made to get the required result?

CodePudding user response:

  1. $group - Group by animals. Push objects with { k: "animal", v: "name" } type into animals array.
  2. $project - Decorate output document. Convert animals array to key-value pair via $arrayToObject.
db.collection.aggregate([
  {
    "$group": {
      "_id": "$place",
      "animals": {
        "$push": {
          k: "$animal",
          v: "$name"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      place: "$_id",
      animals: {
        "$arrayToObject": "$animals"
      }
    }
  }
])

Sample Mongo Playground

CodePudding user response:

If you are on version >=4.4, a reasonable alternative is to use the $function operator:

db.foo.aggregate([
    {$project: {
        'income_statement.annual': {
            $function: {
                body: function(arr) {                      
                    return arr.sort().reverse();
                },
                args: [ "$income_statement.annual" ],
                lang: "js"
            }}
    }}
]);
  • Related