How to use group aggregation for nested array in MongoDB

Question

I am trying to the $group aggregation from MongoDB. There is a document with a nested array as field: "children". I would like to group the children by their jobs. I tried it with $unwind, but it does not give me a list of individual children objects, but the parent object with different jobs.

I have created a working example:

https://mongoplayground.net/p/DuN-yNuKlB-

Data:

[
  {
    "name": "Peter",
    "age": 50,
    "job": "retired",
    "children": [
      {
        "name": "Alex",
        "age": 33,
        "job": "teacher",
        "children": null
      },
      {
        "name": "Jenny",
        "age": 31,
        "job": "teacher",
        "children": null
      },
      {
        "name": "Rob",
        "age": 28,
        "job": "scientist",
        "children": null
      },
      {
        "name": "Harry",
        "age": 27,
        "job": "teacher",
        "children": null
      },
      {
        "name": "Tim",
        "age": 21,
        "job": "student",
        "children": null
      },
      
    ]
  }
]

Query:


db.collection.aggregate([
  {
    $match: {
      name: "Peter"
    }
  },
  {
    $group: {
      _id: "$children.job",
      count: {
        $sum: 1
      }
    }
  }
])

Result:

[
  {
    "_id": [
      "teacher",
      "teacher",
      "scientist",
      "teacher",
      "student"
    ],
    "count": 1
  }
]

I was expecting this to be the result:

 {
    "count": {
      "teacher": 3,
      "scientist": 1,
      "student: 1"
    }
  }

I have seen this and it works $group with nested array in mongodb

But is it possible to get the original objects of the array? Unwind does not return them but only a copy of a single object that differs in the single specified field

Tom Slabbaert · Accepted Answer · 2022-10-27 12:02:26Z

1

You actually do want to be using $unwind for this, you can in theory do it without it. But it just makes the code complex:

db.collection.aggregate([
  {
    $match: {
      name: "Peter"
    }
  },
  {
    $unwind: "$children"
  },
  {
    $group: {
      _id: "$children.job",
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: null,
      values: {
        $push: {
          k: "$_id",
          v: "$count"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        count: {
          "$arrayToObject": "$values"
        }
      }
    }
  }
])

Mongo Playground

answered Oct 27, 2022 at 12:02

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

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

1 Comment

jhb Over a year ago

Thank you Tom, those $group and $push and $replaceRoot operators are handy!

Collectives™ on Stack Overflow

How to use group aggregation for nested array in MongoDB

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related