How to merge two matching objects from different array into one object?
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 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.