mongodbaggregation-framework

MongoDB Aggregation - Combining objects by key into array


I have a JSON document where one array has multiple objects. I want to combine all objects into an array having the same key.

I want to perform such conversion using the MongoDB aggregation pipeline.

Input:

[
  {
    "_id": "65e6afb50f89ab48c027c54f",
    "items": [
      {
        "A": {
          "id": "ci33985107542",
          "sku": "2519438"
        }
      },
      {
        "A": {
          "id": "ci34045151326",
          "sku": "2517213"
        }
      },
      {
        "B": {
          "id": "ci34045151457",
          "sku": "2261539"
        }
      },
      {
        "B": {
          "id": "ci34045151509",
          "sku": "2302283"
        }
      },
      {
        "B": {
          "id": "ci34045151436",
          "sku": "2520098"
        }
      }
    ]
  }
]

Desired output:

[
  {
    "_id": "65e6afb50f89ab48c027c54f",
    "items": [
      {
        "A": [
          {
            "id": "ci33985107542",
            "sku": "2519438"
          },
          {
            "id": "ci34045151326",
            "sku": "2517213"
          }
        ],
        "B": [
          {
            "id": "ci34045151457",
            "sku": "2261539"
          },
          {
            "id": "ci34045151509",
            "sku": "2302283"
          },
          {
            "id": "ci34045151436",
            "sku": "2520098"
          }
        ]
      }
    ]
  }
]

I'm quite new to the MongoDB aggregation pipeline. I was going through some documents and looks like it can be done using $group, but haven't been able to achieve that yet. Can someone please help?


Solution

  • Side note: A well-designed document structure could save you time and better performance in querying.

    Solution 1

    1. $unwind - Deconstruct the items array into multiple documents.

    2. $set - Convert the items document to an array of documents with k and v fields.

    3. $group - Group by _id and item.k and add the first v document into items array.

    4. $group - Group by _id._id and push the converted to key-value pair document into the items array.

    db.collection.aggregate([
      {
        $unwind: "$items"
      },
      {
        $set: {
          items: {
            $objectToArray: "$items"
          }
        }
      },
      {
        $group: {
          _id: {
            _id: "$_id",
            key: {
              $first: "$items.k"
            }
          },
          items: {
            $push: {
              $first: "$items.v"
            }
          }
        }
      },
      {
        $group: {
          _id: "$_id._id",
          items: {
            $push: {
              $arrayToObject: [
                [
                  {
                    k: "$_id.key",
                    v: "$items"
                  }
                ]
              ]
            }
          }
        }
      }
    ])
    

    Demo Solution 1@ Mongo Playground


    Solution 2

    Another solution is slightly complex and does not use the $unwind and $group operators.

    1. $set - Iterate each element and convert it into an array of the documents with k and v fields in the items array.

    2. $set - Combine nested (dimensional) arrays from the previous stage into a single (level) array.

    3. $set - Iterating each key from the flattedItems array ($map), converting the array into objects ($arrayToObject) by performing grouping/filtering based on k fields and retrieving the v field.

    4. $unset - Remove the flattedItems field

    db.collection.aggregate([
      {
        $set: {
          flattedItems: {
            $map: {
              input: "$items",
              in: {
                $objectToArray: "$$this"
              }
            }
          }
        }
      },
      {
        $set: {
          flattedItems: {
            $reduce: {
              input: "$flattedItems",
              initialValue: [],
              in: {
                $concatArrays: [
                  "$$value",
                  "$$this"
                ]
              }
            }
          }
        }
      },
      {
        $set: {
          items: {
            $map: {
              input: {
                $setUnion: "$flattedItems.k"
              },
              in: {
                $arrayToObject: [
                  [
                    {
                      k: "$$this",
                      v: {
                        $map: {
                          input: {
                            $filter: {
                              input: "$flattedItems",
                              as: "item",
                              cond: {
                                $eq: [
                                  "$$this",
                                  "$$item.k"
                                ]
                              }
                            }
                          },
                          in: "$$this.v"
                        }
                      }
                    }
                  ]
                ]
              }
            }
          }
        }
      },
      {
        $unset: "flattedItems"
      }
    ])
    

    Demo Solution 2 @ Mongo Playground