我正在努力编写聚合管道,以便在不覆盖数据中现有键/值的情况下,根据文档的_id查找嵌套文档并返回特定的名称。我已经成功地对嵌套数组执行了此操作,但无法对嵌套在嵌套数组中的数组执行此操作。
所以我想要查找每个配料和每个subIngredient的数量,并将它们与这些已经存在的配料的数据(即_id,subIngredient)合并。
这就是我到目前为止所拥有的:https://mongoplayground.net/p/ft4oIMm_8wg
产品集合:
[
{
"_id": {
"$oid": "5ecf269bceb735416db0b329"
},
"id": 36,
"title": "Product 1",
"description": {
"generalInformation": "Some information",
"activeIngredients": [
{
"_id": 1636,
"qty": 133.5,
"measure": "µg",
"subIngredient": [
{
"_id": 1626,
"qty": 16.6,
"measure": "µg"
}
],
},
{
"_id": 1234,
"qty": 133.5,
"measure": "µg",
"subIngredient": [
{
"_id": 1122,
"qty": 16.6,
"measure": "µg"
},
{
"_id": 1212,
"qty": 16.6,
"measure": "µg"
}
],
},
]
},
},
{
"_id": {
"$oid": "5ecf269bceb735416db0b346"
},
"id": 36,
"title": "Product 2",
"description": {
"generalInformation": "Some information",
"activeIngredients": [
{
"_id": 1234,
"qty": 133.5,
"measure": "µg",
"subIngredient": [
{
"_id": 1122,
"qty": 16.6,
"measure": "µg"
}
],
},
{
"_id": 1234,
"qty": 133.5,
"measure": "µg",
"subIngredient": [
{
"_id": 1122,
"qty": 16.6,
"measure": "µg"
},
{
"_id": 1212,
"qty": 16.6,
"measure": "µg"
}
],
},
]
},
}
]配料集合:
[
{
"_id": 1234,
"name": "Ingredient 1",
},
{
"_id": 1122,
"name": "Ingredient 2",
},
{
"_id": 1212,
"name": "Ingredient 3",
},
{
"_id": 1636,
"name": "Ingredient 4",
},
{
"_id": 1626,
"name": "Ingredient 5",
}
]应返回的内容:
[
{
"_id": ObjectId("5ecf269bceb735416db0b329"),
"title": "Product 1"
"description": {
"activeIngredients": {
"_id": 1636,
"measure": "µg",
"name": "Ingredient 4",
"qty": 133.5,
"subIngredient": [
{
"_id": 1626,
"measure": "µg",
"qty": 16.6
}
]
},
"generalInformation": "Some information"
},
"ingredients": [
{
"_id": 1636,
"measure": "µg",
"name": "Ingredient 4",
"qty": 133.5,
"subIngredient": [
{
"_id": 1626,
"measure": "µg",
"qty": 16.6,
"name": "Ingredient 2"
}
]
},
{
"_id": 1234,
"measure": "µg",
"name": "Ingredient 1",
"qty": 133.5,
"subIngredient": [
{
"_id": 1122,
"measure": "µg",
"qty": 16.6,
"name": "Ingredient 2"
},
{
"_id": 1212,
"measure": "µg",
"qty": 16.6,
"name": "Ingredient 2"
}
]
}
]
},
]我目前的流水线:
[
{
"$unwind": {
"path": "$description.activeIngredients",
"preserveNullAndEmptyArrays": false
}
},
{
"$lookup": {
"from": "ingredients",
"localField": "description.activeIngredients._id",
"foreignField": "_id",
"as": "description.activeIngredients.name"
}
},
{
"$addFields": {
"description.activeIngredients.name": {
"$arrayElemAt": [
"$description.activeIngredients.name.name",
0
]
}
}
},
{
"$group": {
"_id": "$_id",
"ingredients": {
"$push": "$description.activeIngredients"
},
"description": {
"$first": "$description"
},
"title": {
"$first": "$title"
}
}
},
{
"$lookup": {
"from": "ingredients",
"localField": "ingredients.subIngredient._id",
"foreignField": "_id",
"as": "subIngredients"
}
}
]感谢任何人的帮助。谢谢。
发布于 2020-08-04 15:26:14
这并不远,您可以通过多种不同的方法来实现此结果,其中一种方法是只对subingredients数组执行$unwind操作,然后在该数组上执行$lookup操作,最后添加另一个$group阶段来重构文档。
正如你已经清楚地表明,你知道如何做所有这些事情,我将展示一种不同的方法,利用像$map,$indexOfArray和Mongo的v3.6 $lookup语法这样的运算符。
策略不是解开子数组,我们只需$lookup所有相关的子成分,然后使用我指定的运算符“合并”两个数组。
也就是说,采取:
[ {id: 5, name: "name"} ];
[ {id: 5, qty: 25} ]并将其转化为:
[ {id: 5, name: "name", qty: 25} ]我们是这样做的:
db.products.aggregate([
{
"$unwind": {
"path": "$description.activeIngredients",
"preserveNullAndEmptyArrays": false
}
},
{
"$lookup": {
"from": "ingredients",
"localField": "description.activeIngredients._id",
"foreignField": "_id",
"as": "description.activeIngredients.name"
}
},
{
"$addFields": {
"description.activeIngredients.name": {
"$arrayElemAt": [
"$description.activeIngredients.name.name",
0
]
}
}
},
{
"$lookup": {
"from": "ingredients",
"let": {
sub: "$description.activeIngredients.subIngredient"
},
"pipeline": [
{
$match: {
$expr: {
"$setIsSubset": [
[
"$_id"
],
{
$map: {
input: "$$sub",
as: "datum",
in: "$$datum._id"
}
}
]
}
}
}
],
"as": "subIngredients"
}
},
{
"$addFields": {
"description.activeIngredients.subIngredient": {
$map: {
input: "$description.activeIngredients.subIngredient",
as: "sub",
in: {
"$mergeObjects": [
"$$sub",
{
name: {
$arrayElemAt: [
"$subIngredients.name",
{
"$indexOfArray": [
"$subIngredients._id",
"$$sub._id"
]
}
]
}
}
]
}
}
}
}
},
{
"$group": {
"_id": "$_id",
"ingredients": {
"$push": "$description.activeIngredients"
},
"description": {
"$first": "$description"
},
"title": {
"$first": "$title"
}
}
}
])https://stackoverflow.com/questions/63238809
复制相似问题