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

Question

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?

Answer 1

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

Answer 2

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