如何执行嵌套的“连接"?(加入 3 个或更多集合)在 MongoDB 聚合管道中?

问题描述

假设我们在 MongoDB 中有 3 个假设集合:customers、orders 和 orderItems.

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.

以下是这 3 个集合的一些示例数据:

Here's some sample data for these 3 collections:

[
    {
        customer_id: 1,
        name: "Jim Smith",
        email: "[email protected]"
    },
    {
        customer_id: 2,
        name: "Bob Jones",
        email: "[email protected]"
    }
]

订单

[
    {
        order_id: 1,
        customer_id: 1
    },
    {
        order_id: 2,
        customer_id: 1
    }
]

订单项

[
    {
        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: "[email protected]"
        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: "[email protected]"
        orders: []
    }
]

推荐答案

使用 使用管道查找,

• $lookup 和 orders 集合，
  • let，定义来自主集合的变量customer_id，使用$$像$一样访问管道内的这个引用变量$customer_id,
  • 管道可以像我们在根级管道中一样添加管道阶段
  • $expr 每当我们匹配内部字段时，它都需要表达式匹配条件，因此 $$customer_id 是在 let 中声明的父集合字段，并且$customer_id 是子集合/当前集合的字段
  • $lookup with orders collection,
    • let, define variable customer_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 in let and $customer_id is child collection's/current collection's field

    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"
        }
      }
    ])
    

    游乐场

    提示:

    在 NoSQL 中，多个连接被认为是不好的做法，我建议您是否可以将订单集合中的订单项添加为数组，您可以为订单项保存一个连接过程，请参阅 游乐场

    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 聚合管道中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！