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.
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.