MongoDB在具有附加字段的对象数组上聚合$lookup
我有两个不同的集合(下面的示例)方法 &项目.至于现在,我正在为 $lookup
使用 pre 3.6 vanilla 聚合查询:
I have two different collections (example below) methods & items. As for now, I'm using pre 3.6 vanilla aggregation query for $lookup
:
MongoPlayground 示例
{
$lookup: {
from: "items",
localField: "reagents._id",
foreignField: "_id",
as: "reagent_items"
}
}
问题是,如果我使用它,我会在 $lookup
阶段错过 quantity
字段(来自嵌入的 methods.reagents
)原创收藏.现在,我在 lookup
之后立即返回 quantity
但正如我听说的那样,Mongo 从 3.6 引入了一种用于 lookup
查询的新语法,所以问题是:
The problem is that if I am using it, I miss quantity
field (from methods.reagents
embedded) during $lookup
stage from original collection. For now, I return quantity
right after lookup
but as I heard, Mongo introduced from 3.6 a new syntax for lookup
queries, so the question is:
它能否解决我收到以下结果的问题:
Can it solve my problem for receiving the following results:
{
"_id": 1,
"name": "Test",
"reagent_items": [ // <= the exact schema of what I need after lookup
{
"_id": 1,
"name": "ItemOne",
"other": "field",
"quantity": 2 //quantity field from original {array of objects} after lookup
},
{
"_id": 2,
"name": "ItemTwo",
"other": "field",
"quantity": 4 //quantity field from original {array of objects} after lookup
}
],
"reagents": [ //original reagents field here just for example, we could remove it
{
"_id": 1,
"quantity": 2
},
{
"_id": 2,
"quantity": 4
}
]
}
方法
{
"_id": 1,
"name": "Test",
"reagents": [
{
_id: 1,
quantity: 2
},
{
_id: 2,
quantity: 4
}
]
}
项目
{
"_id": 1,
"name": "ItemOne",
"other": "field"
},
{
"_id": 2,
"name": "ItemTwo",
"other": "field"
}
推荐答案
使用 $map 以及 $arrayElemAt 为每个 reagent_items
找到对应的 reagent
并应用 $mergeObjects 获取一个对象:
Use $map along with $arrayElemAt to find corresponding reagent
for each reagent_items
and the apply $mergeObjects to get one object:
db.methods.aggregate([
{
$lookup: {
from: "items",
localField: "reagents._id",
foreignField: "_id",
as: "reagent_items"
}
},
{
$project: {
_id:1,
name: 1,
reagents: 1,
reagent_items: {
$map: {
input: "$reagent_items",
as: "ri",
in: {
$mergeObjects: [
"$$ri",
{
$arrayElemAt: [ { $filter: { input: "$reagents", cond: { $eq: [ "$$this._id", "$$ri._id" ] } } }, 0 ]
}
]
}
}
}
}
}
])
Mongo 游乐场
相关文章