Group Subdocuments Array by key in MongoDB

Admin 1
Viewed 66

Is it possible to use MongoDB's aggregation framework to group channels using folder key and without joining documents?

[{key: 1, channels: {A: [], B: [], etc}}, {key: 2, channels: {A: [], B: [], etc}}]

I am trying to do using $unwind and then $group by folder name but it seems impossible.

Mongo Playground

Documents:

[
  {
    key: 1,
    channels: [
      {
        "id": 1,
        "name": "XXX",
        "folder": "C"
      },
      {
        "id": 2,
        "name": "XXX",
        "folder": "A"
      },
      {
        "id": 3,
        "name": "XXX",
        "folder": "B"
      },
      {
        "id": 4,
        "name": "XXX",
        "folder": "A"
      },
      {
        "id": 5,
        "name": "XXX",
        "folder": "B"
      },
      {
        "id": 6,
        "name": "XXX",
        "folder": "C"
      }
    ]
  },
  {
    key: 2,
    channels: [
      {
        "id": 1,
        "name": "XXX",
        "folder": "D"
      },
      {
        "id": 2,
        "name": "XXX",
        "folder": "B"
      },
      {
        "id": 3,
        "name": "XXX",
        "folder": "A"
      },
      {
        "id": 4,
        "name": "XXX",
        "folder": "C"
      },
      {
        "id": 5,
        "name": "XXX",
        "folder": "A"
      },
      {
        "id": 6,
        "name": "XXX",
        "folder": "D"
      }
    ]
  }
]

Expected Result:

[
  {
    key: 1,
    channels: {
      A: [{
        "id": 2,
        "name": "XXX"
      },{
        "id": 4,
        "name": "XXX"
      }],
      B: [{
        "id": 3,
        "name": "XXX"
      }, {
        "id": 5,
        "name": "XXX"
      }],
      C: [{
         "id": 1,
        "name": "XXX"
      },{
        "id": 6,
        "name": "XXX"
      }]
    }
  },
  {
    "key": 2,
    "channels": ...

  }
]

Thank you very much in advance.

aggregation-framework mongodb
3 Answers

You just have to add one more $group stage followed by project stage which will alter the data as per requirement.

db.collection.aggregate([
  {
    $match: {
      key: {
        $in: [
          1,
          2
        ]
      }
    }
  },
  {
    $unwind: "$channels"
  },
  {
    $group: {
      _id: {
        key: "$key",
        folder: "$channels.folder"
      },
      value: {
        $push: "$channels",
        
      },
      "foldersRef": {
        $push: "$channels.folder"
      },
      
    }
  },
  {
    $group: {
      _id: "$_id.key",
      "channels": {
        "$push": "$value"
      },
      
    },
    
  },
  {
    "$project": {
      "channels": {
        "$map": {
          "input": "$channels",
          "as": "channel",
          "in": {
            "$arrayToObject": [
              [
                {
                  "v": {
                    "$map": {
                      "input": "$$channel",
                      "as": "subChannel",
                      "in": {
                        "id": "$$subChannel.id",
                        "name": "$$subChannel.name",
                        
                      }
                    }
                  },
                  "k": {
                    "$arrayElemAt": [
                      "$$channel.folder",
                      0
                    ]
                  },
                  
                }
              ]
            ],
            
          },
          
        }
      },
      
    },
    
  },
  
])

Mongo Playground Sample Execution

user_82132770

What you have done is more appreciated. You might need two grop to easly preform the aggregations

  • $unwind to deconstruct the array
  • $group to group by key and foldername and second group to group by key only, but make the grp as key value pair which helps for $arrayToObject
  • $replaceRoot to make it to root with other fields _id ($mergeobjects)
  • $project for projection

Here is the code

db.collection.aggregate([
  { $unwind: "$channels" },
  {
    "$group": {
      "_id": { key: "$key", fname: "$channels.folder" },
      "channels": { "$push": "$channels" }
    }
  },
  {
    "$group": {
      "_id": "$_id.key",
      "grp": { "$push": { k: "$_id.fname", v: "$channels" } }
    }
  },
  { "$addFields": { "grp": { "$arrayToObject": "$grp" } } },
  {
    "$replaceRoot": {
      "newRoot": { "$mergeObjects": [ "$grp", "$$ROOT" ] }
    }
  },
  {
    "$project": { grp: 0 }
  }
])

Working Mongo playground

user_79757710
  • $group by key and folder, construct channels array by providing required fields
  • $group by only key and construct the channels array in key-vlaue format
  • $arrayToObject convert above constructed channels to object
db.collection.aggregate([
  { $match: { key: { $in: [1, 2] } } },
  { $unwind: "$channels" },
  {
    $group: {
      _id: {
        id: "$key",
        folder: "$channels.folder"
      },
      channels: {
        $push: {
          id: "$channels.id",
          name: "$channels.name"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.id",
      channels: {
        $push: {
          k: "$_id.folder",
          v: "$channels"
        }
      }
    }
  },
  { $project: { channels: { $arrayToObject: "$channels" } } }
])

Playground

user_89871280
Related