How to create nested array inside $group in MongoDB?

Question

After some of the aggregation pipeline stages, my MongoDB Document has the following format:

{
    "key": "A",
    "status": "To Do",
    "val": 4
  },
  {
    "key": "A",
    "status": "Done",
    "val": 1
  },
  {
    "key": "A",
    "status": "To Do",
    "val": 3
  },
  {
    "key": "A",
    "status": "Done",
    "val": 2
  },
  {
    "key": "B",
    "status": "Done",
    "val": 5
  },
  {
    "key": "B",
    "status": "Done",
    "val": 6
  }

Expected output:

Group by key
Group by status
Push val

  {
    "key": "A",
    "status_val": [
      {
        "status": "To Do",
        "val": [
          3,
          4
        ]
      },
      {
        "status": "Done",
        "val": [
          1,
          2
        ]
      }
    ]
  },
  {
    "key": "B",
    "status_val": "Done",
    "val": [
      5,
      6
    ]
  }

Here is what I tried:

db.collection.aggregate([
          {
            $group: {
            _id: {
              
              key:"$key",
              status:"$status"
            },
              v: {
               $push:  "$val"
               }
           }
         },
         {
            $group: {
            _id: "$_id.key",
              status_val: {
               $addToSet: {
                 status: "$_id.status",
                 val: "$_id.v"
               }
           }
         }},
         {
    $project: { _id: 0,key:"$_id",status_val:1 }
  }
]).pretty()

But I get the wrong output:

[
  {
    "key": "B",
    "status_val": [
      {
        "status": "Done"
      }
    ]
  },
  {
    "key": "A",
    "status_val": [
      {
        "status": "Done"
      },
      {
        "status": "To Do"
      }
    ]
  }
]

How to get nested array inside $group?

mickl · Accepted Answer · 2020-07-09 05:45:01Z

2

You're correctly running $group twice but this line needs to be changed:

val: "$_id.v"

v is not a part of _id, your data after first $group looks like this:

{
    "_id": { "key": "B, "status": "Done" },
    "v": [ 5, 6 ]
}

try:

db.collection.aggregate([
    {
        $group: {
            _id: { key: "$key", status: "$status" },
            val: { $push: "$val" }
        }
    },
    {
        $group: {
            _id: "$_id.key",
            status_val: {
                $push: {
                    status: "$_id.status",
                    val: "$val"
                }
            }
        }
    },
    {
        $project: {
            _id: 0,
            key: "$_id",
            status_val: 1
        }
    }
])

Mongo Playground

edited Jul 9, 2020 at 5:45

answered Jul 9, 2020 at 5:36

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

Collectives™ on Stack Overflow

How to create nested array inside $group in MongoDB?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related