在mongodb中进行汇总查找后,如何填充ID的深层嵌套数组? [英] How to populate deeply nested array of ids after aggregate lookup in mongodb?
问题描述
此问题是上一个问题.
我在A,B和C馆藏中有以下文件
I have following documents in collection A, B, and C
"A":
{_id: "A_id1", labelA: "LabelA1"},
{_id: "A_id2", labelA: "LabelA2"}
"C":
{ _id: "C_id1", labelC: "LabelC1"},
{ _id: "C_id2",labelC: "LabelC2"}
"B":
{
_id: "B_id1",
labelB: "LabelB1",
refToA: "A_id1",
items: [
{
itemLabel: "a",
options: [ { optionLabel: "opt1", codes: [ "C_id1"] },
{ optionLabel: "opt2", codes: [ "C_id2"] } ]
}
]
},
{
_id: "B_id4",
labelB: "LabelB4",
refToA: "A_id2",
items: [
{ itemLabel: "b",
options: [ { optionLabel: "opt3", codes: [ "C_id1", "C_id2"]
}
]
}
集合B在项目"字段中嵌套了子文档数组,进一步在子项目.options"中嵌套了子子文档的arrya.最后,第三个子级别的"items.options.codes"包含文档C的ID列表.
The collection B has nested array of sub-documents in the field 'items', further nested arrya of sub-sub-document as 'items.options'. Finally, the third sub-level 'items.options.codes' contain list of ids of document C.
我想聚合A来收集所有引用A的B.我使用以下命令来做到这一点:
I want to Aggregate A to collect all B as that refer to A. I do it using the command:
db.A.aggregate([
{
$match: {
_id: "A_id1"
}
},
{
$lookup: {
from: "B",
let: {
refToA: "$_id"
},
pipeline: [
{
$match: { $expr: { $eq: ["$refToA", "$$refToA"]}}
},
],
as: "BCollection"
}
}
])
给出以下结果
{
"BCollection": [
{
"_id": "B_id1",
"items": [
{
"itemLabel": "a",
"options": [
{
"codes": [ "C_id1" ],
"optionLabel": "opt1"
},
{
"codes": [ "C_id2"],
"optionLabel": "opt2"
}
]
}
],
"labelB": "LabelB1",
"refToA": "A_id1"
}
],
"_id": "A_id1",
"labelA": "LabelA1"
}
现在,我想保留上面的结构,并使用集合C中的详细信息填充代码"字段.所需结果如下
Now, I want to preserve the above structure and also populate the field 'codes' with details from collection C. The desired result is as follows
{
"BCollection": [
{
"_id": "B_id1",
"items": [
{
"itemLabel": "a",
"options": [
{
"codes": [ { _id: "C_id1", labelC: "LabelC1"} ],
"optionLabel": "opt1"
},
{
"codes": [ { _id: "C_id2", labelC: "LabelC2"}],
"optionLabel": "opt2"
}
]
}
],
"labelB": "LabelB1",
"refToA": "A_id1"
}
],
"_id": "A_id1",
"labelA": "LabelA1"
}
我尝试了以下查询,但未产生期望的结果:
I have tried the following query, but it does not produce the desired result:
db.A.aggregate([
{
$match: {
_id: "A_id1"
}
},
{
$lookup: {
from: "B",
let: {
refToA: "$_id"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$refToA",
"$$refToA"
]
}
}
},
{
$lookup: {
from: "C",
localField: "items.options.codes",
foreignField: "_id",
as: "items.option.codes"
}
}
],
as: "BCollection"
}
}
])
您可以在此处查看上述查询的输出: https://mongoplayground.net/p/ZJVU6PQF6MZ
You can see the output of above query in here: https://mongoplayground.net/p/ZJVU6PQF6MZ
推荐答案
尝试一下:
db.A.aggregate([
{
$lookup: {
from: "B",
let: { refToA: "$_id" },
pipeline: [
{
$match: {
$expr: { $eq: ["$refToA", "$$refToA"] }
}
},
{ $unwind: "$items" },
{ $unwind: "$items.options" },
{
$lookup: {
from: "C",
localField: "items.options.codes",
foreignField: "_id",
as: "items.options.codes"
}
},
{
$group: {
_id: {
id: "$_id",
itemLabel: "$items.itemLabel"
},
labelB: { $first: "$labelB" },
refToA: { $first: "$refToA" },
items: {
$push: {
"itemLabel": "$items.itemLabel",
"options": "$items.options"
}
}
}
},
{
$group: {
_id: "$_id.id",
labelB: { $first: "$labelB" },
refToA: { $first: "$refToA" },
items: {
$push: {
itemLabel: "$_id.itemLabel",
"options": "$items.options"
}
}
}
}
],
as: "BCollection"
}
}
]);
这篇关于在mongodb中进行汇总查找后,如何填充ID的深层嵌套数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!