MongoDB-aggregate+lookup关联查询(MongoDB多表联接)

最新推荐文章于 2024-09-11 23:37:29 发布

侯晓阳丶

最新推荐文章于 2024-09-11 23:37:29 发布

阅读量1.2k

点赞数 20

分类专栏： MongoDB 文章标签： mongodb 数据库 dba 经验分享

本文链接：https://blog.csdn.net/h_3369/article/details/141959551

版权

MongoDB 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

前言

MongoDB的lookup，对同一数据库中的未分片集合执行左外连接，将两个集合，根据指定的字段进行等值筛选联接，以方便对联接后的结果集进行后续处理。对于被联接的集合，lookup阶段会添加一个新的数组字段，里面的元素是一个被"{}"包裹的，被联接集合与本地集合相匹配的数据行，lookup阶段将这些重塑后的文档传递到下一阶段。如let或者pipeline以方便做后续的处理，如定义变量，聚合操作等。

lookup语法介绍

{
   $lookup:
     {
       from: //要关联查询的集合名称,
       localField: //此处指定的输入集合的字段,
       foreignField: //这里指的是from连接集合的字段与localField字段进行等值匹配,
       as: 添加新的字段，包括输入的和from集合的字段，如果输入文档中已存在此字段，这里将被重写
     }
}

如果对应SQL语句上方的语法就是

SELECT *, (
   SELECT ARRAY_AGG(*)
   FROM xxx
   WHERE xxx = xxx
) AS xxx
FROM xxx;

使用lookup进行等值联接

1.插入两个集合的测试数据，用于关联查询

db.orders.insertMany( [
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
] )
###
db.inventory.insertMany( [
   { "_id" : 1, "sku" : "almonds", "description": "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", "description": "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", "description": "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", "description": "product 4", "instock" : 70 },
   { "_id" : 5, "sku": null, "description": "Incomplete" },
   { "_id" : 6 }
] )

2.通过lookup，将order和inventory集合通过item和skw字段进行联接。并返回新的集合名为inventory_docs

db.orders.aggregate( [
   {
     $lookup:
       {
         from: "inventory",
         localField: "item",
         foreignField: "sku",
         as: "inventory_docs"
       }
  }
] )
[
  {
    _id: 1,
    item: 'almonds',
    price: 12,
    quantity: 2,
    inventory_docs: [
      {
        _id: 1,
        sku: 'almonds',
        description: 'product 1',
        instock: 120
      }
    ]
  },
  {
    _id: 2,
    item: 'pecans',
    price: 20,
    quantity: 1,
    inventory_docs: [
      { _id: 4, sku: 'pecans', description: 'product 4', instock: 70 }
    ]
  },
  {
    _id: 3,
    inventory_docs: [ { _id: 5, sku: null, description: 'Incomplete' }, { _id: 6 } ]
  }
]

通过上方查询结果可以看出，orders集合的_id:1的item almonds值与inventory集合的_id:1 almonds的值，匹配成功并返回数据。其他的结果依次类推。

数组使用lookup进行联接

1.插入一批测试文档

db.classes.insertMany( [
   { _id: 1, title: "Reading is ...", enrollmentlist: [ "giraffe2", "pandabear", "artie" ], days: ["M", "W", "F"] },
   { _id: 2, title: "But Writing ...", enrollmentlist: [ "giraffe1", "artie" ], days: ["T", "F"] }
] )
###
db.members.insertMany( [
   { _id: 1, name: "artie", joined: new Date("2016-05-01"), status: "A" },
   { _id: 2, name: "giraffe", joined: new Date("2017-05-01"), status: "D" },
   { _id: 3, name: "giraffe1", joined: new Date("2017-10-01"), status: "A" },
   { _id: 4, name: "panda", joined: new Date("2018-10-11"), status: "A" },
   { _id: 5, name: "pandabear", joined: new Date("2018-12-01"), status: "A" },
   { _id: 6, name: "giraffe2", joined: new Date("2018-12-01"), status: "D" }
] )

2.对classes与members集合进行联接，通过classes的enrollmentlist字段对members的name字段进行关联。

db.classes.aggregate( [
   {
      $lookup:
         {
            from: "members",
            localField: "enrollmentlist",
            foreignField: "name",
            as: "enrollee_info"
        }
   }
] )
[
  {
    _id: 1,
    title: 'Reading is ...',
    enrollmentlist: [ 'giraffe2', 'pandabear', 'artie' ],
    days: [ 'M', 'W', 'F' ],
    enrollee_info: [
      {
        _id: 1,
        name: 'artie',
        joined: ISODate('2016-05-01T00:00:00.000Z'),
        status: 'A'
      },
      {
        _id: 5,
        name: 'pandabear',
        joined: ISODate('2018-12-01T00:00:00.000Z'),
        status: 'A'
      },
      {
        _id: 6,
        name: 'giraffe2',
        joined: ISODate('2018-12-01T00:00:00.000Z'),
        status: 'D'
      }
    ]
  },
  {
    _id: 2,
    title: 'But Writing ...',
    enrollmentlist: [ 'giraffe1', 'artie' ],
    days: [ 'T', 'F' ],
    enrollee_info: [
      {
        _id: 1,
        name: 'artie',
        joined: ISODate('2016-05-01T00:00:00.000Z'),
        status: 'A'
      },
      {
        _id: 3,
        name: 'giraffe1',
        joined: ISODate('2017-10-01T00:00:00.000Z'),
        status: 'A'
      }
    ]
  }
]

通过上方的语句和结果可以看出，语法上和普通文档等值联接一样，虽说enrollmentlist列为数组，但是也会通过数组中的元素与name列进行一个一个匹配的。

mergeObjects与lookup

mergeObjects：可以将多个文档合并成一个文档，遇到相同的字段会覆盖。保留最后一次这个字段出现时的值。
replaceRoot：将输入文档替换为指定的文档。该操作会替换输入文档中的所有现有字段，包括 _id 字段。您可以将现有的嵌入式文档提升到顶层，或创建新文档进行提升。
arrayElemAt：通过数组元素的索引位置，返回数组中的元素。

1.插入一批测试数据

db.orders.insertMany( [
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
] )
###
db.items.insertMany( [
  { "_id" : 1, "item" : "almonds", description: "almond clusters", "instock" : 120 },
  { "_id" : 2, "item" : "bread", description: "raisin and nut bread", "instock" : 80 },
  { "_id" : 3, "item" : "pecans", description: "candied pecans", "instock" : 60 }
] )

2.通过将orders集合与items结合，根据item列进行关联，并且将输入输出结合的结果集进行合并，并只输出合并后的结果。

db.orders.aggregate( [
   {
      $lookup: {
         from: "items",
         localField: "item",   
         foreignField: "item", 
         as: "fromItems"
      }
   },
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$fromItems", 0 ] }, "$$ROOT" ] } }
   },
   { $project: { fromItems: 0 } }
] )
...
[
  {
    _id: 1,
    item: 'almonds',
    description: 'almond clusters',
    instock: 120,
    price: 12,
    quantity: 2
  },
  {
    _id: 2,
    item: 'pecans',
    description: 'candied pecans',
    instock: 60,
    price: 20,
    quantity: 1
  }
]

lookup阶段，将items表与orders通过items字段进行关联，并输出一个为fromItems的数组集合。
replaceRoot阶段，通过使用mergeObjects将多个文档和为一个文档，arrayElemAt提取fromItems数组中的索引位置为0得元素。就是将fromItems列所有得数据提取出来，然后通过mergeObjects根据orders集合内容进行合并，根据mergeObjects的特性，遇到相同的字段，取最后一次值进行合并，例如上方数据中，_id为2的数据，fromItems中为_id：3，合并时最后一次遇到_id将是_id：2所以合并时为_id:2，随后进行替换到orders结果集中。
project：将fromItems字段隐藏，不显示，最后输出的就是我们多表联接合并后的结果集。

子查询语法介绍

对于已经联接的集合上和联接条件，或者时子查询，MongoDB支持以下方式：

支持在已联接的集合上执行管道。
支持多个联接条件
关联和不关联查询

在 MongoDB 中，关联子查询是 $lookup 阶段中的管道，引用了联接集合中的文档字段。非关联子查询不引用联接字段，从 MongoDB 5.0 开始，对于包含 $sample 阶段、$sampleRate 操作符或 $rand 操作符的 $lookup 管道阶段中的非关联子查询，如果重复此子查询，此子查询总是会再次运行。以前，根据子查询输出大小，要么缓存子查询输出，要么再次运行子查询。

{
   $lookup:
      {
         from: //同一个数据库指定要联接的集合
         let: //指定在管道阶段，要使用的变量，使用变量表达式访问已经联接的集合中的字段。再将字段输出到pipline这个阶段,
         pipeline: //指定要在已联接集合上运行的 pipeline。pipeline 数据来自联接集合的结果文档。要返回所有文档，请指定一个空的 pipeline [],
         as: //指定要添加到已连接文档的新字段名称。新的字段包含或来自。如果指定的名称已存在于所连接的文档中，则现有字段将被覆盖。
      }
}

使用lookup执行多个连接和关联子查询

pipeline表示确定来自已联接集合的结果文档。要返回所有文档，请指定一个空的 pipeline []，pipeline 无法直接访问联接的文档字段。可以使用 let 选项为联接的文档字段定义变量，然后在 pipeline 阶段引用变量。

1.创建测试数据

db.orders.insertMany( [
  { "_id" : 1, "item" : "almonds", "price" : 12, "ordered" : 2 },
  { "_id" : 2, "item" : "pecans", "price" : 20, "ordered" : 1 },
  { "_id" : 3, "item" : "cookies", "price" : 10, "ordered" : 60 }
] )
###
db.warehouses.insertMany( [
  { "_id" : 1, "stock_item" : "almonds", warehouse: "A", "instock" : 120 },
  { "_id" : 2, "stock_item" : "pecans", warehouse: "A", "instock" : 80 },
  { "_id" : 3, "stock_item" : "almonds", warehouse: "B", "instock" : 60 },
  { "_id" : 4, "stock_item" : "cookies", warehouse: "B", "instock" : 40 },
  { "_id" : 5, "stock_item" : "cookies", warehouse: "A", "instock" : 80 }
] )

2.根据item和stock_item进行关联查询，并判断，instock的值大于等于ordered值得有哪些。

db.orders.aggregate( [
   {
      $lookup:
         {
           from: "warehouses",
           let: { order_item: "$item", order_qty: "$ordered" },
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$stock_item",  "$$order_item" ] },
                         { $gte: [ "$instock", "$$order_qty" ] }
                       ]
                    }
                 }
              },
              { $project: { stock_item: 0, _id: 0 } }
           ],
           as: "stockdata"
         }
    }
] )
...
[
  {
    _id: 1,
    item: 'almonds',
    price: 12,
    ordered: 2,
    stockdata: [
      { warehouse: 'A', instock: 120 },
      { warehouse: 'B', instock: 60 }
    ]
  },
  {
    _id: 2,
    item: 'pecans',
    price: 20,
    ordered: 1,
    stockdata: [ { warehouse: 'A', instock: 80 } ]
  },
  {
    _id: 3,
    item: 'cookies',
    price: 10,
    ordered: 60,
    stockdata: [ { warehouse: 'A', instock: 80 } ]
  }
]

lookup阶段，将orders集合与warehouses进行关联。
let阶段定义两个变量，order_item为item列，order_qty为ordered列。传递给下一个管道pipeline。
pipeline做聚合操作，使用match进行过滤查询，通过expr使用聚合表达式，聚合表式为and，and的内容为stock_item字段，与order_item字段进行等值比较。并且instock大于等于order_qty。随后传递给下个管道project做自定义结果集。
使用project抑制stock_item字段和_id的输出。
as阶段，为lookup语句的结尾，为多表关联后的数组结果集进行命名。

使用lookup执行不相关的子查询

聚合管道 $lookup 阶段可以在已联接的集合上执行一个管道，这样不但可以执行关联子查询，也可以执行非关联子查询。非关联子查询不会引用已联接的文档字段。

1.创建一些测试数据

db.absences.insertMany( [
   { "_id" : 1, "student" : "Ann Aardvark", sickdays: [ new Date ("2018-05-01"),new Date ("2018-08-23") ] },
   { "_id" : 2, "student" : "Zoe Zebra", sickdays: [ new Date ("2018-02-01"),new Date ("2018-05-23") ] },
] )
###
db.holidays.insertMany( [
   { "_id" : 1, year: 2018, name: "New Years", date: new Date("2018-01-01") },
   { "_id" : 2, year: 2018, name: "Pi Day", date: new Date("2018-03-14") },
   { "_id" : 3, year: 2018, name: "Ice Cream Day", date: new Date("2018-07-15") },
   { "_id" : 4, year: 2017, name: "New Years", date: new Date("2017-01-01") },
   { "_id" : 5, year: 2017, name: "Ice Cream Day", date: new Date("2017-07-16") }
] )

2.将两个集合进行关联，过滤2018年的数据，并且将absences集合的date字段与holidays集合的name字段进行联接在一起。

db.absences.aggregate( [
   {
      $lookup:
         {
           from: "holidays",
           pipeline: [
              { $match: { year: 2018 } },
              { $project: { _id: 0, date: { name: "$name", date: "$date" } } },
              { $replaceRoot: { newRoot: "$date" } }
           ],
           as: "holidays"
         }
    }
] )
[
  {
    _id: 1,
    student: 'Ann Aardvark',
    sickdays: [
      ISODate('2018-05-01T00:00:00.000Z'),
      ISODate('2018-08-23T00:00:00.000Z')
    ],
    holidays: [
      { name: 'New Years', date: ISODate('2018-01-01T00:00:00.000Z') },
      { name: 'Pi Day', date: ISODate('2018-03-14T00:00:00.000Z') },
      {
        name: 'Ice Cream Day',
        date: ISODate('2018-07-15T00:00:00.000Z')
      }
    ]
  },
  {
    _id: 2,
    student: 'Zoe Zebra',
    sickdays: [
      ISODate('2018-02-01T00:00:00.000Z'),
      ISODate('2018-05-23T00:00:00.000Z')
    ],
    holidays: [
      { name: 'New Years', date: ISODate('2018-01-01T00:00:00.000Z') },
      { name: 'Pi Day', date: ISODate('2018-03-14T00:00:00.000Z') },
      {
        name: 'Ice Cream Day',
        date: ISODate('2018-07-15T00:00:00.000Z')
      }
    ]
  }
]

lookup阶段，将absences与holidays进行关联，传递给pipeline阶段做聚合操作
pipeline通过match过滤2018年的数据。
project阶段，重新构建多表联接数组里的内容，将_id列隐藏起来，并命名成date文档，date文档的内容为holidays集合的name和date字段。
最后再通过replaceRoot，将date替换掉。最后只保留date字段和name字段。
as返回数组结果集为holidays。

使用lookup执行简洁版关联子查询

从 MongoDB 5.0 开始，聚合管道 $lookup 阶段支持简洁关联子查询语法，该语法改进了集合之间的联接。新的简洁语法取消了在 $match 阶段对 $expr 操作符内的外部和本地字段进行等值匹配的要求。
简洁关联子查询的语法

{
   $lookup:
      {
         from: <foreign collection>,  //同一个数据库要联接的集合
         localField: <field from local collection's documents>, //指定本集合需要与关联集合匹配的字段
         foreignField: <field from foreign collection's documents>, //指定被联接集合与本集合匹配的字段
         let: { <var_1>: <expression>, …, <var_n>: <expression> }, //可选。指定各个管道阶段中使用的变量。使用变量表达式访问输入到 pipeline 的文档字段。
         pipeline: [ <pipeline to run> ], //	
指定在外部集合上运行的 pipeline。pipeline 返回外部集合的文档。要返回所有文档，请指定一个空的 pipeline []
         as: <output array field> //为关联数组结果集命名
      }
}

1.还是使用，上方关联子查询子查询的例子。我们在5.0版本之前的语法是以下这样。

db.orders.aggregate( [
   {
      $lookup:
         {
           from: "warehouses",
           let: { order_item: "$item", order_qty: "$ordered" },
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$stock_item",  "$$order_item" ] },
                         { $gte: [ "$instock", "$$order_qty" ] }
                       ]
                    }
                 }
              },
              { $project: { stock_item: 0, _id: 0 } }
           ],
           as: "stockdata"
         }
    }
] )

2.修改语句，为简洁版本关联查询，结果集与上方的语句一致。

db.orders.aggregate( [
   {
      $lookup: {
         from: "warehouses",
         localField: "item",
         foreignField: "stock_item",
         let: { order_qty: "$ordered" },
         pipeline: [ {
            $match: {
               $expr: { $gte: [ "$instock","$$order_qty" ] }
            }
         },
         { $project: { stock_item: 0, _id: 0 } } ],
         as: "stockdata"
      }
   }
] )
...
[
  {
    _id: 1,
    item: 'almonds',
    price: 12,
    ordered: 2,
    stockdata: [
      { warehouse: 'A', instock: 120 },
      { warehouse: 'B', instock: 60 }
    ]
  },
  {
    _id: 2,
    item: 'pecans',
    price: 20,
    ordered: 1,
    stockdata: [ { warehouse: 'A', instock: 80 } ]
  },
  {
    _id: 3,
    item: 'cookies',
    price: 10,
    ordered: 60,
    stockdata: [ { warehouse: 'A', instock: 80 } ]
  }
]

上方两种语法主要不同的点就是，不需要在expr中做等值匹配了，等值匹配与单表联接一样，放到了localField和foreignField选项中，不需要等值匹配let变量也就只需要我们在聚合操作中，需要进行运算的字段，才设置变量。
match阶段直接使用expr进行比较运算即可。也不需要and，因为不需要再进行等值联接了。

侯晓阳丶

关注

20
点赞
踩
16

收藏

觉得还不错? 一键收藏
0
评论
MongoDB-aggregate+lookup关联查询(MongoDB多表联接)

MongoDB的lookup，对同一数据库中的未分片集合执行左外连接，将两个集合，根据指定的字段进行等值筛选联接，以方便对联接后的结果集进行后续处理。对于被联接的集合，lookup阶段会添加一个新的数组字段，里面的元素是一个被"{}"包裹的，被联接集合与本地集合相匹配的数据行，lookup阶段将这些重塑后的文档传递到下一阶段。如let或者pipeline以方便做后续的处理，如定义变量，聚合操作等。
复制链接

扫一扫

专栏目录