How to use $mergeObjects to merge arrays corresponding to existing groups?

user_7768287 0
Viewed 3461

I'm trying to merge two arrays in my aggregation pipeline. After performing $facet, my MongoDB document has this format:

{
  "final": [
    {
      "key": "TP-1",
      "status_map": [
        {  "status": "Closed", "final": [ "a", "b"]},
        { "status": "Done", "final": ["c","d" ] }
      ]
    },
    {
      "key": "TP-2",
      "status_map": [
        { "status": "Closed", "final": [  "x","y"] }
      ]
    }
  ],
  "start": [
    {
      "key": "TP-1",
      "status_map": [
        { "status": "Closed", "start": [ "h"]},
        { "status": "Done", "start": ["a"]}
      ]
    },
    {
      "key": "TP-2",
      "status_map": [{ "status": "Done", "start": ["l","m"]}
      ]
    }
  ]
}

Expected Output:

I need to merge final and start array corresponding to two groups:

  1. Based on key and then
  2. Based on status
{
  "data": [
    {
      "key": "TP-1",
      "status_map": [
        { "status": "Closed","final": ["a","b"],"start":["h"]},
        { "status": "Done","final": ["c","d"],"start":["a"]}
        ]
    },
    {
      "key": "TP-2",
      "status_map": [
        { "status": "Closed",  "final":[ "x","y"],"start": []},
        { "status": "Done",  "final": [ ],"start": [ "l","m"]}
      ]
    }
  ]
}

How to achieve this use case?

aggregation-framework mongodb-query mongodb aggregation grouping
3 Answers

There are several ways to approach this, not necessarily with $mergeObjects. But since you mentioned $mergeObjects this is one that uses it:

Note that, with this approach, we are merging objects of the same key and status, the values in the arrays will not get concatenated if the same key exists for multiple documents, The arrays will get replaced instead.

db.collection.aggregate([
  {
    $project: {
      all: { $concatArrays: ["$final","$start"] }
    }
  },
  {
    $unwind: "$all"
  },
  {
    $unwind: "$all.status_map"
  },
  {
    $group: {
      _id: {
        _id: "$_id",  // keep _id in $group to apply the group for each document, otherwise if you want to apply group on all documents, omit this
        key: "$all.key",
        status: "$all.status_map.status"
      },
      status_map: { $mergeObjects: "$$ROOT.all.status_map" }
    }
  },
  { // some data don't have start or end at all, we have to set a default empty array
    $addFields: { // you can skip this stage if you allow data without start and final keys
      "status_map.start": { $ifNull: ["$status_map.start", []] },
      "status_map.final": { $ifNull: ["$status_map.final", []] }
    }
  },
  {
    $group: {
      _id: { _id: "$_id._id", key: "$_id.key" },
      key: { $first: "$_id.key" },
      status_map: { $push: "$status_map" }
    }
  }
])

Mongo Playground

user_16567490

With no assumptions (for example for both keys to always appear) my strategy was to concat both arrays, unwind and finally group by the key.

db.collection.aggregate([
  {
    $project: {
      concat: {
        $concatArrays: [
          "$final",
          "$start"
        ]
      }
    }
  },
  {
    $unwind: "$concat"
  },
  {
    $unwind: "$concat.status_map"
  },
  {
    $group: {
      _id: {
        k: "$concat.key",
        status: "$concat.status_map.status"
      },
      final: {
        $push: "$concat.status_map.final"
      },
      start: {
        $push: "$concat.status_map.start"
      }
    }
  },
  {
    $group: {
      _id: "$_id.k",
      status_map: {
        $push: {
          status: "$_id.status",
          final: "$final",
          start: "$start"
        }
      }
    }
  },
  {
    $project: {
      key: "$_id",
      status_map: 1,
      _id: 0
    }
  }
])

Mongo Playground

user_107703700

Adding to @Tom Slabbaert's answer,

Mongo Playground

Here, final and start array is of format array of array. But It has to be simply an array.

It can be achieved by using $unwind on status_map and $reduce on status_map.final and status_map.start arrays.

Final query:

db.collection.aggregate([
  {
    $project: {
      concat: {
        $concatArrays: [
          "$final",
          "$start"
        ]
      }
    }
  },
  {
    $unwind: "$concat"
  },
  {
    $unwind: "$concat.status_map"
  },
  {
    $group: {
      _id: {
        k: "$concat.key",
        status: "$concat.status_map.status"
      },
      final: {
        $push: "$concat.status_map.final"
      },
      start: {
        $push: "$concat.status_map.start"
      }
    }
  },
  {
    $group: {
      _id: "$_id.k",
      status_map: {
        $push: {
          status: "$_id.status",
          final: "$final",
          start: "$start"
        }
      }
    }
  },
  {
    $project: {
      key: "$_id",
      status_map: 1,
      _id: 0
    }
  },
  {
    $unwind: "$status_map"
  },
  {
    $project: {
      key: 1,
      "status_map.status": 1,
      final: {
        $reduce: {
          input: "$status_map.final",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      start: {
        $reduce: {
          input: "$status_map.start",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$key",
      status_map: {
        $push: {
          status: "$status_map.status",
          final: "$final",
          start: "$start"
        }
      }
    }
  }
])

Mongo Playground

user_77682870
Related