用 MongoDB 聚合实现多字段关联

本教程介绍如何构建聚合管道、在集合上执行聚合并查看结果。示例选择 MongoDB Shell。

任务目标

把描述商品信息的集合与描述客户订单的集合合并，得到 2020 年被订购的商品列表，并列出每笔订单的详情。

聚合通过 $lookup 实现多字段关联。如果两个集合的文档中存在多个相互对应的字段，就可以依据这些字段匹配文档，再把两侧的信息合并到同一个文档中。

开始之前

原文可以通过右上角的语言选择菜单切换示例，或选择 MongoDB Shell。这里使用 MongoDB Shell 分支。

示例包含两个集合：

• products：文档描述商店销售的商品。
• orders：文档描述商店中针对商品的单笔订单。

每笔订单只能包含一种商品。聚合把一份商品文档与该商品对应的订单文档关联起来。products 集合中的 name 和 variation 字段，分别对应 orders 集合中的 product_name 和 product_variation 字段。

使用 insertMany() 创建 orders 和 products 集合并插入样例数据：

db.orders.insertMany( [
   {
      customer_id: "elise_smith@myemail.com",
      orderdate: new Date("2020-05-30T08:35:52Z"),
      product_name: "Asus Laptop",
      product_variation: "Standard Display",
      value: 431.43,
   },
   {
      customer_id: "tj@wheresmyemail.com",
      orderdate: new Date("2019-05-28T19:13:32Z"),
      product_name: "The Day Of The Triffids",
      product_variation: "2nd Edition",
      value: 5.01,
   },
   {
      customer_id: "oranieri@warmmail.com",
      orderdate: new Date("2020-01-01T08:25:37Z"),
      product_name: "Morphy Richards Food Mixer",
      product_variation: "Deluxe",
      value: 63.13,
   },
   {
      customer_id: "jjones@tepidmail.com",
      orderdate: new Date("2020-12-26T08:55:46Z"),
      product_name: "Asus Laptop",
      product_variation: "Standard Display",
      value: 429.65,
   }
] )

db.products.insertMany( [
   {
      name: "Asus Laptop",
      variation: "Ultra HD",
      category: "ELECTRONICS",
      description: "Great for watching movies"
   },
   {
      name: "Asus Laptop",
      variation: "Standard Display",
      category: "ELECTRONICS",
      description: "Good value laptop for students"
   },
   {
      name: "The Day Of The Triffids",
      variation: "1st Edition",
      category: "BOOKS",
      description: "Classic post-apocalyptic novel"
   },
   {
      name: "The Day Of The Triffids",
      variation: "2nd Edition",
      category: "BOOKS",
      description: "Classic post-apocalyptic novel"
   },
   {
      name: "Morphy Richards Food Mixer",
      variation: "Deluxe",
      category: "KITCHENWARE",
      description: "Luxury mixer turning good cakes into great"
   }
] )

构建并执行聚合

以下步骤演示如何创建聚合管道，并通过多个字段关联两个集合。

1. 创建供 lookup 阶段使用的嵌套管道

主聚合管道的第一个阶段是 $lookup，通过两侧各两个字段把 orders 集合关联到 products 集合。$lookup 内包含一条嵌套管道，用于配置关联过程。

embedded_pl = [
   // Stage 1: Match the values of two fields on each side of the join
   // The $eq filter uses aliases for the name and variation fields set
   { $match: {
      $expr: {
         $and: [
            { $eq: ["$product_name", "$$prdname"] },
            { $eq: ["$product_variation", "$$prdvartn"] }
         ]
      }
   } },
   // Stage 2: Match orders placed in 2020
   { $match: {
      orderdate: {
         $gte: new Date("2020-01-01T00:00:00Z"),
         $lt: new Date("2021-01-01T00:00:00Z")
      }
   } },
   // Stage 3: Remove unneeded fields from the orders collection side of the join
   { $unset: ["_id", "product_name", "product_variation"] }
]

2. 执行聚合管道

db.products.aggregate( [
   // Use the embedded pipeline in a lookup stage
   { $lookup: {
         from: "orders",
         let: {
            prdname: "$name",
            prdvartn: "$variation"
         },
         pipeline: embedded_pl,
         as: "orders"
   } },
   // Match products ordered in 2020
   { $match: { orders: { $ne: [] } } },
   // Remove unneeded fields
   { $unset: ["_id", "description"] }
] )

3. 理解聚合结果

聚合结果包含两个文档，分别代表在 2020 年被订购的商品。每个文档都有一个 orders 数组，列出该商品每笔订单的详情。

{
   name: 'Asus Laptop',
   variation: 'Standard Display',
   category: 'ELECTRONICS',
   orders: [
      {
         customer_id: 'elise_smith@myemail.com',
         orderdate: ISODate('2020-05-30T08:35:52.000Z'),
         value: 431.43
      },
      {
         customer_id: 'jjones@tepidmail.com',
         orderdate: ISODate('2020-12-26T08:55:46.000Z'),
         value: 429.65
      }
   ]
}
{
   name: 'Morphy Richards Food Mixer',
   variation: 'Deluxe',
   category: 'KITCHENWARE',
   orders: [
      {
         customer_id: 'oranieri@warmmail.com',
         orderdate: ISODate('2020-01-01T08:25:37.000Z'),
         value: 63.13
      }
   ]
}