MongoDB - How to write a nested group aggregation query

Question

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?

Yong Shun · Accepted Answer · 2022-03-30 12:03:57Z

2

$group - Group by animals. Push objects with { k: "animal", v: "name" } type into animals array.
$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

answered Mar 30, 2022 at 12:03

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

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

Comments

Buzz Moschetti · Accepted Answer · 2022-03-30 14:08:49Z

0

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"
            }}
    }}
]);

answered Mar 30, 2022 at 14:08

Buzz Moschetti

7,6433 gold badges27 silver badges41 bronze badges

1 Comment

rickhg12hs Over a year ago

Animals need lots of help determining their annual income. 8-)

Collectives™ on Stack Overflow

MongoDB - How to write a nested group aggregation query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related