如何将来自不同数组的两个匹配对象合并为一个对象? [英] How to merge two matching objects from different array into one object?
问题描述
在这种情况下,我从聚合中得到了一个结果,我正在以这种格式获取数据.
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" : ""
}
}
]
}
现在,我想比较每一行的两个对象数组,这里是基于"blockId"的对象的"dataBlocks"和"templateBlocks",我想以以下格式获取结果.
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" : ""
}
}
]
}
是否可以用mongodb完成它?我正在使用3.4,并尝试使用聚合来实现这一目标.
Is it possible to get it done with mongodb ? I am using 3.4 and trying to achieve this using aggregation.
谢谢.
推荐答案
以下查询完成了该工作:
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.
这篇关于如何将来自不同数组的两个匹配对象合并为一个对象?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!