我如何执行嵌套的“连接"?(加入 3 个或更多集合)在 MongoDB 聚合管道中? [英] How can I perform nested "joins" (joining 3 or more collections) in a MongoDB aggregation pipeline?
问题描述
假设我们在 MongoDB 中有 3 个假设集合:customers
、orders
和 orderItems
.
每个客户有多个订单,每个订单有多个订单商品.
以下是这 3 个集合的一些示例数据:
客户
<预><代码>[{客户 ID:1,姓名:吉姆·史密斯",电子邮件:jim.smith@example.com"},{客户 ID:2,名称:鲍勃·琼斯",电子邮件:bob.jones@example.com"}]订单
<预><代码>[{order_id: 1,客户 ID:1},{order_id: 2,客户 ID:1}]订单项
<预><代码>[{order_item_id: 1,名称:Foo",价格:4.99,订单 ID:1},{order_item_id: 2,名称:酒吧",价格:17.99,订单 ID:1},{order_item_id: 3,名称: "baz",价格:24.99,订单 ID:2}]想要的结果
如何编写聚合管道,使返回的结果看起来像这样?
<预><代码>[{客户 ID:1,姓名:吉姆·史密斯",电子邮件:jim.smith@example.com"命令: [{order_id: 1,项目: [{名称:Foo",价格:4.99},{名称:酒吧",价格:17.99}]},{order_id: 2,项目: [{名称: "baz",价格:24.99}]}]},{客户 ID:2,名称:鲍勃·琼斯",电子邮件:bob.jones@example.com"命令: []}]使用 使用管道查找,
$lookup
和orders
集合,let
,定义来自主集合的变量customer_id
,使用$$
像$ 一样在管道内访问这个引用变量$customer_id
,pipeline
可以像在根级管道中一样添加管道阶段$expr
每当我们匹配内部字段时,它都需要表达式匹配条件,所以$$customer_id
是在let
中声明的父集合字段,并且$customer_id
是子集合/当前集合的字段
$lookup
和orderitems
集合
db.customers.aggregate([{$查找:{来自:订单",让:{customer_id:$customer_id";},管道:[{ $match: { $expr: { $eq: ["$$customer_id", "$customer_id"] } } },{$查找:{来自:订单项",本地字段:order_id",外国字段:order_id",如:物品"}}],如:订单"}}])
<块引用>
提示:
在 NoSQL 中,一些连接被认为是不好的做法,我建议您是否可以将订单项作为数组添加到订单集合中,您可以为订单项保存一个连接过程,请参阅 游乐场
Let's say we have 3 hypothetical collections in MongoDB: customers
, orders
, and orderItems
.
Each customer has multiple orders, and each order has multiple order items.
Here's some sample data for these 3 collections:
customers
[
{
customer_id: 1,
name: "Jim Smith",
email: "jim.smith@example.com"
},
{
customer_id: 2,
name: "Bob Jones",
email: "bob.jones@example.com"
}
]
orders
[
{
order_id: 1,
customer_id: 1
},
{
order_id: 2,
customer_id: 1
}
]
orderItems
[
{
order_item_id: 1,
name: "Foo",
price: 4.99,
order_id: 1
},
{
order_item_id: 2,
name: "Bar",
price: 17.99,
order_id: 1
},
{
order_item_id: 3,
name: "baz",
price: 24.99,
order_id: 2
}
]
Desired Result
How can I write my aggregation pipeline so that the result returned looks something like this?
[
{
customer_id: 1,
name: "Jim Smith",
email: "jim.smith@example.com"
orders: [
{
order_id: 1,
items: [
{
name: "Foo",
price: 4.99
},
{
name: "Bar",
price: 17.99
}
]
},
{
order_id: 2,
items: [
{
name: "baz",
price: 24.99
}
]
}
]
},
{
customer_id: 2,
name: "Bob Jones",
email: "bob.jones@example.com"
orders: []
}
]
Do nested lookup using lookup with pipeline,
$lookup
withorders
collection,let
, define variablecustomer_id
that is from main collection, to access this reference variable inside pipeline using$$
like$$customer_id
,pipeline
can add pipeline stages same as we do in root level pipeline$expr
whenever we match internal fields it requires expression match condition, so$$customer_id
is parent collection field that declared inlet
and$customer_id
is child collection's/current collection's field
$lookup
withorderitems
collection
db.customers.aggregate([
{
$lookup: {
from: "orders",
let: { customer_id: "$customer_id" },
pipeline: [
{ $match: { $expr: { $eq: ["$$customer_id", "$customer_id"] } } },
{
$lookup: {
from: "orderitems",
localField: "order_id",
foreignField: "order_id",
as: "items"
}
}
],
as: "orders"
}
}
])
Tip:
Several joins considered as bad practice in NoSQL, I would suggest if you could add your order items in orders collection as array, you can save one join process for orderitems, see improved version in playground
这篇关于我如何执行嵌套的“连接"?(加入 3 个或更多集合)在 MongoDB 聚合管道中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!