group an array into subarrays in a project stage

Question

I want to split the following array according to the group-value. I know I can do this using $unwind and $group. Is there any way to this in a single $project-stage?

Input

{
    "_id": 1,
    "some_field": "some_value",
    "array": [
        {
            "group": "a",
            "subgroup": "aa",
            "value": 1
        },
        {
            "group": "b",
            "subgroup": "bb",
            "value": 2
        },
        {
            "group": "a",
            "subgroup": "ab",
            "value": 2
        }
    ]
}

desired output:

{
    "_id": 1,
    "some_field": "some_value",
    "array": [
        {
            "group": "a",
            "values": [
                {
                    "subgroup": "aa",
                    "value": 1
                },
                {
                    "subgroup": "ab",
                    "value": 2
                }
            ]
        },
        {
            "group": "b",
            "values": [
                {
                    "subgroup": "bb",
                    "value": 2
                }
            ]
        }
    ]
}

user_1672799 · Answer

Try this: https://mongoplayground.net/p/pFn3tLtAG4D

$set: {
    _id: "$_id",
    some_field: "$some_field",
    array: {
      $map: {
        input: {
          $setUnion: [
            "$array.group"
          ]
        },
        in: {
          group: "$$this",
          values: {
            $map: {
              input: {
                $filter: {
                  input: "$array",
                  as: "elem",
                  cond: {
                    $eq: [
                      "$$elem.group",
                      "$$this"
                    ]
                  }
                }
              },
              as: "vals",
              in: {
                subgroup: "$$vals.subgroup",
                value: "$$vals.value"
              }
            }
          }
        }
      }
    }
  }

user_10770370 · Answer

It is doable, it's definitely not clean or sexy. My approach is to use $reduce and $mergeObjects, we'll iterate over the array and keep reconstructing the result.

The main issue that plagues this approach is this feature that doesn't allow to $concatArrays expressions, so we have to use some very ugly workarounds.

Anyways here is how you can achieve this:

db.collection.aggregate([
  {
    $project: {
      _id: 1,
      some_field: 1,
      array: {
        $map: {
          input: {
            "$objectToArray": {
              $reduce: {
                input: "$array",
                initialValue: {},
                in: {
                  "$mergeObjects": [
                    "$$value",
                    {
                      "$arrayToObject": [
                        [
                          {
                            k: "$$this.group",
                            v: {
                              $map: {
                                input: {
                                  "$concatArrays": [
                                    [
                                      "$$this"
                                    ],
                                    {
                                      $map: {
                                        input: {
                                          $filter: {
                                            input: {
                                              "$objectToArray": "$$value"
                                            },
                                            as: "filterItem",
                                            cond: {
                                              $eq: [
                                                "$$filterItem.k",
                                                "$$this.group"
                                              ]
                                            }
                                          }
                                        },
                                        as: "mapItem",
                                        in: "$$mapItem.v"
                                      }
                                    },
                                    
                                  ]
                                },
                                as: "map2Item",
                                in: {
                                  $cond: [
                                    {
                                      "$isArray": "$$map2Item"
                                    },
                                    {
                                      $arrayElemAt: [
                                        "$$map2Item",
                                        0
                                      ]
                                    },
                                    "$$map2Item"
                                  ]
                                }
                              }
                            }
                          }
                        ]
                      ]
                    }
                  ]
                }
              }
            }
          },
          as: "item",
          in: {
            group: "$$item.k",
            values: "$$item.v"
          }
        }
      }
    }
  }
])

Mongo Playground

user_1409374 · Answer

This is far from a single project stage, but it does produce the desired output from the given input.

db.collection.aggregate([
{'$match': {'_id': 1}},
 {'$unwind': '$array'},
 {'$project': {'array': {'group': '$array.group', 'values': '$array'},
   'some_field': 1,
   'my_id': '$_id'}},
 {'$unset': 'array.values.group'},
 {'$group': {'_id': '$array.group',
   'values': {'$push': '$array.values'},
   'some_field': {'$first': '$some_field'},
   'my_id': {'$first': '$my_id'}}},
 {'$set': {'array': {'group': '$_id', 'values': '$values'}}},
 {'$unset': 'values'},
 {'$group': {'_id': '$my_id',
   'array': {'$push': '$array'},
   'some_field': {'$first': '$some_field'}}}
])

Try it on mongoplayground.net.

group an array into subarrays in a project stage

3 Answers