How to merge two matching objects from different array into one object?

Question

I have a situation where I have got one result from aggregation where I am getting data in this format.

{
    "_id" : ObjectId("5a42432d69cbfed9a410e8ad"),
    "bacId" : "BAC0023444",
    "cardId" : "2",
    "defaultCardOrder" : "2",
    "alias" : "Finance",
    "label" : "Finance",
    "for" : "",
    "cardTooltip" : {
        "enable" : true,
        "text" : ""
    },
    "dataBlocks" : [
        {
            "defaultBlockOrder" : "1",
            "blockId" : "1",
            "data" : "0"
        },
        {
            "defaultBlockOrder" : "2",
            "blockId" : "2",
            "data" : "0"
        },
        {
            "defaultBlockOrder" : "3",
            "blockId" : "3",
            "data" : "0"
        }
    ],
    "templateBlocks" : [
        {
            "blockId" : "1",
            "label" : "Gross Profit",
            "quarter" : "",
            "data" : "",
            "dataType" : {
                "typeId" : "2"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        },
        {
            "blockId" : "2",
            "label" : "Profit Forecast",
            "quarter" : "",
            "data" : "",
            "dataType" : {
                "typeId" : "2"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        },
        {
            "blockId" : "3",
            "label" : "Resource Billing",
            "quarter" : "",
            "data" : "",
            "dataType" : {
                "typeId" : "2"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        }
    ]
},
{
    "_id" : ObjectId("5a42432d69cbfed9a410e8ad"),
    "bacId" : "BAC0023444",
    "cardId" : "3",
    "defaultCardOrder" : "3",
    "alias" : "Staffing",
    "label" : "Staffing",
    "for" : "",
    "cardTooltip" : {
        "enable" : true,
        "text" : ""
    },
    "dataBlocks" : [
        {
            "defaultBlockOrder" : "1",
            "blockId" : "1",
            "data" : "1212"
        },
        {
            "defaultBlockOrder" : "2",
            "blockId" : "2",
            "data" : "1120"
        },
        {
            "defaultBlockOrder" : "3",
            "blockId" : "3",
            "data" : "1200"
        }
    ],
    "templateBlocks" : [
        {
            "blockId" : "1",
            "label" : "Staffing Planner",
            "quarter" : "",
            "data" : "",
            "dataType" : {
                "typeId" : "1"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        },
        {
            "blockId" : "2",
            "label" : "Baseline",
            "quarter" : "",
            "data" : "",
            "dataType" : {
                "typeId" : "1"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        },
        {
            "blockId" : "3",
            "label" : "Projected",
            "quarter" : "",
            "data" : "",
            "dataType" : {
                "typeId" : "1"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        }
    ]
}

Now I want to compare the two array of objects for each row, here in this case its "dataBlocks" and "templateBlocks" based on "blockId" s and I want to get the result in the following format.

{
    "_id" : ObjectId("5a42432d69cbfed9a410e8ad"),
    "bacId" : "BAC0023444",
    "cardId" : "2",
    "defaultCardOrder" : "2",
    "alias" : "Finance",
    "label" : "Finance",
    "for" : "",
    "cardTooltip" : {
        "enable" : true,
        "text" : ""
    },
    "blocks" : [
        {
            "defaultBlockOrder" : "1",
            "blockId" : "1",
            "data" : "0",
            "label" : "Gross Profit",
            "quarter" : "",
            "dataType" : {
                "typeId" : "2"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        },
        {
            "defaultBlockOrder" : "2",
            "blockId" : "2",
            "data" : "0",
            "label" : "Profit Forecast",
            "quarter" : "",
            "dataType" : {
                "typeId" : "2"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        },
        {
            "defaultBlockOrder" : "3",
            "blockId" : "3",
            "data" : "0",
            "label" : "Resource Billing",
            "quarter" : "",
            "dataType" : {
                "typeId" : "2"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        }
    ]
},
{
    "_id" : ObjectId("5a42432d69cbfed9a410e8ad"),
    "bacId" : "BAC0023444",
    "cardId" : "3",
    "defaultCardOrder" : "3",
    "alias" : "Staffing",
    "label" : "Staffing",
    "for" : "",
    "cardTooltip" : {
        "enable" : true,
        "text" : ""
    },
    "dataBlocks" : [
        {
            "defaultBlockOrder" : "1",
            "blockId" : "1",
            "data" : "1212",
            "label" : "Staffing Planner",
            "quarter" : "",
            "dataType" : {
                "typeId" : "1"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        },
        {
            "defaultBlockOrder" : "2",
            "blockId" : "2",
            "data" : "1120",
            "label" : "Baseline",
            "quarter" : "",
            "dataType" : {
                "typeId" : "1"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        },
        {
            "defaultBlockOrder" : "3",
            "blockId" : "3",
            "data" : "1200",
            "label" : "Projected",
            "quarter" : "",
            "dataType" : {
                "typeId" : "1"
            },
            "tooltip" : {
                "enable" : true,
                "text" : ""
            }
        }
    ]
}

Is it possible to get it done with mongodb ? I am using 3.4 and trying to achieve this using aggregation.

Thanks in advance.

score 12 · Answer 1 · edited Oct 29 '20 at 11:50

You can try below aggregation in 3.6.

The query below iterates the dataBlocks array and merges the data block element with template block element. The template block is looked up using $indexofArray which locates the array index with matching block id and $arrayElemAt to access the element at the found index.

db.collection_name.aggregate([{"$addFields":{
  "blocks":{
    "$map":{
      "input":"$dataBlocks",
      "in":{
        "$mergeObjects":[
          "$$this",
          {"$arrayElemAt":[
            "$templateBlocks",
            {"$indexOfArray":["$templateBlocks.blockId","$$this.blockId"]}
            ]
          }
        ]
      }
    }
  }
}}])

For 3.4, replace $mergeObjects with combination of $arrayToObject, $objectToArray and $concatArrays to merge the each array element from both arrays.

db.collection_name.aggregate([{"$addFields":{
  "blocks":{
    "$map":{
      "input":"$dataBlocks",
      "in":{
        "$arrayToObject":{
          "$concatArrays":[
            {"$objectToArray":"$$this"},
            {"$objectToArray":{
              "$arrayElemAt":[
                "$templateBlocks",
                {"$indexOfArray":["$templateBlocks.blockId","$$this.blockId"]
                }
              ]
            }}
          ]
        }
      }
    }
  }
}}])

You can use project with exclusion as last stage to remove array fields from output.

{"$project":{"templateBlocks":0,"dataBlocks":0}}

The part of `{ "$indexOfArray":["$templateBlocks","$$this.blockId"] }` doesn't work, as `templateBlocks` contains objects and not elements, so they do not match with given `blockId` directly, they need to be mapped somehow so you can reference the `blockId` property of each `templateBlock`. — Amr Saber, Nov 24 '19 at 09:07
I suggested an edit with the fix of the issue mentioned by @Argento. Otherwise this should definitely be the accepted answer. — Burawi, Mar 17 '20 at 08:26
@Argento, @Burawi, I just added ```{ "$indexOfArray":["$templateBlocks``` **.blockId** ```","$$this.blockId"] }``` and it seems to work. — Kenna, Jun 11 '20 at 18:34

score 5 · Accepted Answer · answered Dec 27 '17 at 14:23

The following query does the job:

db.merge.aggregate([
  // unwind twice
  {$unwind: "$templateBlocks"},
  {$unwind: "$dataBlocks"},
  // get rid of documents where dataBlocks.blockId and 
  // templateBlocks.blockId are not equal
  {$redact: {$cond: [{
                        $eq: [
                               "$dataBlocks.blockId",
                               "$templateBlocks.blockId"
                             ]
                      },
                      "$$KEEP",
                      "$$PRUNE"
                    ]
            }
  },
  // merge dataBlocks and templateBlocks into a single document
  {$project: {
                bacId: 1,
                cardId: 1,
                defaultCardOrder: 1,
                alias: 1,
                label: 1,
                for: 1,
                cardTooltip: 1,
                dataBlocks: {
                              defaultBlockOrder: "$dataBlocks.defaultBlockOrder",
                              blockId: "$dataBlocks.blockId",
                              data: "$dataBlocks.data",
                              label: "$templateBlocks.label",
                              quarter: "$templateBlocks.quarter",
                              data: "$templateBlocks.data",
                              dataType: "$templateBlocks.dataType",
                              tooltip: "$templateBlocks.tooltip"
                            }
             }
      },
      // group to put correspondent dataBlocks to an array
      {$group: {
              _id: {
                     _id: "$_id",
                     bacId: "$bacId",
                     cardId: "$cardId",
                     defaultCardOrder: "$defaultCardOrder",
                     alias: "$alias",
                     label: "$label",
                     for: "$for",
                     cardTooltip: "$cardTooltip"
                   },
              dataBlocks: {$push: "$dataBlocks" }
           }
  },
  // remove the unnecessary _id object
  {$project: {
               _id: "$_id._id",
               bacId: "$_id.bacId",
               cardId: "$_id.cardId",
               defaultCardOrder: "$_id.defaultCardOrder",
               alias: "$_id.alias",
               label: "$_id.label",
               for: "$_id.for",
               cardTooltip: "$_id.cardTooltip",
               dataBlocks: "$dataBlocks"
             }
  }
])

Take into account that performance depends of size of your data set as the query unwinds twice and it may produce significant amount of intermediate documents.

How to merge two matching objects from different array into one object?

2 Answers2