mongodb aggregate lookup 关联表

目录

• MongoDB 的 集合关联（演示： $lookup 和 DBRef 实现集合关联； 演示： $out 和 $merge 的插入和合并到目标集合中）

• 演示前提：

• 登录单机模式的 mongodb 服务器命令
• 登录【test】数据库的 mongodb 客户端命令
• 登录【admin】数据库的 mongodb 客户端命令

• SQL 术语 与 Mongodb 的对应关系
• 使用 $lookup 实现集合关联

• 语法格式
• 添加测试数据
• 1、查询出订单数量大于6，及其对应的商品：

• 1-1：查询出订单中，【amount】数量大于6的订单
• 1-2：查询出订单中，【amount】数量大于 6 的订单，及其对应的商品

• 查询命令
• 命令解释
• 查询结果

• 2、查询出价格为 30 的商品所关联的全部订单

• 2-1：查询【item】集合中价格为 30 的商品:
• 2-2：查询出价格为 30 的商品所关联的全部订单

• 查询命令
• 命令解释
• 查询结果

• 使用 DBRef 实现集合关联

• 语法格式
• 添加测试数据
• 1、查询标题为 “标题2" 的 comment 及对应的商品

• 1-1：查询标题为 “标题2" 的 comment

• 查询命令
• 查询结果

• 1-2：查询标题为 “标题2" 的 comment 文档 及 对应的商品

• 查询返回的文档只有一条数据：

• 查询命令：
• 查询结果

• 查询返回的文档有多条数据：

• 查询命令
• 查询结果

• $out 和 $merge (将查询到的文档插入到目标集合中)

• 1、$out： 将 item 中 price 大于 30 的所有文档选出、并插入 abc 集合中。

• 1-1、测试前，【abc】集合不存在

• 命令
• 命令解释
• 测试结果

• 1-2、【abc】集合已经存在并有数据，再测试

• 命令
• 测试结果

• 2、$merge ： 将 item 中 price 大于 30 的所有文档选出、合并到目标集合【ccc】中。

• 2-1、测试前，【ccc】集合不存在

• 命令
• 测试结果

• 2-1、【ccc】集合已存在的测试：合并测试

• 命令
• 测试结果

MongoDB 的 集合关联（演示： $lookup 和 DBRef 实现集合关联； 演示： $out 和 $merge 的插入和合并到目标集合中）

演示前提：

登录单机模式的 mongodb 服务器命令

mongod.exe --config "E:\install\mongodb\mongodb-4.2.25\mongod.conf"

登录【test】数据库的 mongodb 客户端命令

mongo mongodb://192.168.0.107:27017/test -u LJHAAA -p 123456

登录【admin】数据库的 mongodb 客户端命令

mongo mongodb://192.168.0.107:27017/admin -u admin -p 123456

SQL 术语 与 Mongodb 的对应关系

使用 $lookup 实现集合关联

语法格式

在聚集运算使用如下文档：

{
     $lookup:
        {
          from: 指定要连接的集合
          localField: 指定本集合中文档的主键字段
          foreignField: 指定要连接的集合中的外键字段
          as: 指定被连接对象的属性名
     }
   }

localField 和 foreignField 指定两个集合之间的关联条件

添加测试数据

原本的 item 集合有这些数据

价格为 30 的【键盘】，和 订单 1、2、3相关联；
价格为 60 的【鼠标】，和 订单 4、5 相关联；

db.order.insert([
  {item_id:  ObjectId("65e6cb5e055c945aa89b8575"), name: "订单1", amount: 3},
  {item_id:  ObjectId("65e6cb5e055c945aa89b8575"), name: "订单2", amount: 5},
  {item_id:  ObjectId("65e6cb5e055c945aa89b8575"), name: "订单3", amount: 8},
  {item_id:  ObjectId("65e6cb5e055c945aa89b8578"), name: "订单4", amount: 4},
  {item_id:  ObjectId("65e6cb5e055c945aa89b8578"), name: "订单5", amount: 7}
])

如图：生成一个【order】的订单集合，相当于sql的订单表。

1、查询出订单数量大于6，及其对应的商品：

1-1：查询出订单中，【amount】数量大于6的订单

1-2：查询出订单中，【amount】数量大于 6 的订单，及其对应的商品

查询命令

db.order.aggregate([
    {$match: {amount: {$gt: 6}}},
    { 
       $lookup: 
        {
           from: "item",
           localField: "item_id",
           foreignField: "_id",
           as: "item_detail"
        }
     }
])

命令解释

db.order.aggregate([                  //aggregate 表示对 MongoDB 中的 order 集合执行聚集运算
 
    {$match: {amount: {$gt: 6}}},      //这是聚合管道中的第一个阶段，相当于where 条件：查询 amount > 6 的订单     
    { 
       $lookup:                        // 这是聚合管道中的第二个阶段，使用 $lookup 操作符来执行关联查询
        {
           from: "item",               //指定要连接的集合，这里是连接【item】这个集合（表）
           
           localField: "item_id",      // 指定当前集合（order）中用于关联的字段为 item_id
           
           foreignField: "_id",        //指定要关联的集合（item）中用于关联的字段为 _id
           
           as: "item_detail"           //指定将关联查询的结果存储到名为 item_detail 的字段中
        }
     }
])

相当于 SQL 中的 join 查询：

select * from order o  
join  item i  on o.item_id = i . _id 
where o.amout > 6;

查询结果

2、查询出价格为 30 的商品所关联的全部订单

2-1：查询【item】集合中价格为 30 的商品:

db.item.aggregate([
    {$match:{price:30}}
])

2-2：查询出价格为 30 的商品所关联的全部订单

查询命令

db.item.aggregate([
     {$match: {price: 30}},
     {$lookup: {
              from: "order",
              localField: "_id",
              foreignField: "item_id",
              as: "order_array"
          }
      }
])

命令解释

db.item.aggregate([

     {$match: {price: 30}},             //这是聚合管道中的第一个阶段，用于筛选出【item】集合中 price 等于 30 的 数据
     
     {$lookup: {    			        //这是聚合管道中的第二个阶段，使用 $lookup 操作符来执行关联查询
     
              from: "order",            //指定要关联查询的集合为 order
              
              localField: "_id",        //指定当前集合（item）中用于关联的字段为 _id
              
              foreignField: "item_id",  //指定要关联的集合（order）中用于关联的字段为 item_id
              
              as: "order_array"         //指定将关联查询的结果存储到名为 order_array 的字段中
          }
      }
])

查询结果

使用 DBRef 实现集合关联

语法格式

DBRef 尽可能地模拟了SQL数据库中外键——指定要引用哪个表的哪个列。

插入文档时使用 DBRef 引用其他文档，DBRef 的完整形式：

{ $ref : 被引用的集合名 , $id : 被引用的文档主键< value > , $db : 被引用的数据库 }

如果被引用的集合在同一个数据库中，$db 可以省略。

通过 DBRef 的 fetch() 方法即可抓取关联文档。

添加测试数据

db.comment.insert([
  {item:  {$ref: "item", $id: ObjectId("65e6cb5e055c945aa89b8575")}, title: "标题1", body: "评论内容1"},
  {item:  {$ref: "item", $id: ObjectId("65e6cb5e055c945aa89b8575")}, title: "标题2", body: "评论内容2"},
  {item:  {$ref: "item", $id: ObjectId("65e6cb5e055c945aa89b8575")}, title: "标题3", body: "评论内容3"},
  {item:  {$ref: "item", $id: ObjectId("65e6cb5e055c945aa89b8575")}, title: "标题4", body: "评论内容4"},
  {item:  {$ref: "item", $id: ObjectId("65e6cb5e055c945aa89b8575")}, title: "标题5", body: "评论内容5"}
])

item： 这是一个嵌套的字段，包含了一个引用到 item 集合中某个文档的信息。

$ref: “item”： 指定了引用的集合为 item。

$id: ObjectId(“65e6cb5e055c945aa89b8575”)： 指定了引用的文档的 ObjectId。

如图：引用了 item 集合中的 id 为 【65e6cb5e055c945aa89b8575】 的文档

测试数据：

1、查询标题为 “标题2" 的 comment 及对应的商品

1-1：查询标题为 “标题2" 的 comment

查询命令

db.comment.findOne({title: "标题2"})

查询结果

1-2：查询标题为 “标题2" 的 comment 文档 及 对应的商品

【备注】如果你已经用了 DBRef 来记录关联，程序可以非常方便处理关联，压根不需要使用 $lookup

查询返回的文档只有一条数据：

查询命令：

db.comment.findOne({title: "标题2"}).item.fetch()

查询结果

在 studio 3t 这个图形界面工具查询就出错。

修改成这样：

db.comment.findOne({title: "标题2"}, {item: 1})

在命令行窗口查询就可以

查询返回的文档有多条数据：

如果查询的包含了多个文档，且要通过DBRef获取关联文档时，
由于 find() 方法返回的是 DBCursor，因此需要先遍历 DBCursor，然后在通过查询文档的关联属性来获取关联的文档。

DBCursor 有一个 forEach 方法，该方法需要传入一个 JS 的 function( c ) {c就代表了正则遍历的文档}，
通过该函数可对DbCursor中多个文档进行遍历。

查询命令

db.comment.find({title: "标题2"}).forEach(function(c){
    print(JSON.stringify(c) + JSON.stringify(c.item.fetch()));
})

查询结果

$out 和 $merge (将查询到的文档插入到目标集合中)

$out 和 $merge 都用于先选出符合条件的文档，然后插入目标集合中。

区别在于：

$out 总会创建新集合，这意味着当 $out 指定的集合已经存在时，它要先删除该集合（该集合中的文档自然就没了），再创建新集合、插入文档。

1、$out： 将 item 中 price 大于 30 的所有文档选出、并插入 abc 集合中。

1-1、测试前，【abc】集合不存在

命令

db.item.aggregate([
    {$match: {price: {$gt: 30}}},
    {$out: "abc"}
])

命令解释

db.item.aggregate([
    {$match: {price: {$gt: 30}}}, //查询条件
    {$out: "abc"}      //将查出来的文档插入到【abc】这个集合中
    				   //如果 【abc】集合不存在，则创建该集合并插入数据
    				   //如果 【abc】集合存在，就会将【abc】集合删除掉，然后再创建一个新的【abc】集合并插入数据
])

测试结果

如图，查询出 item 集合中， price 大于 30 的所有文档，然后插入 abc 集合中，
此前因为没有【abc】这个集合，所以是新创建的

1-2、【abc】集合已经存在并有数据，再测试

查询出【books】集合中，【type】值是【漫画】的文档，然后插入到【abc】集合中。

命令

db.books.aggregate([
    {$match: {type: "漫画"}},
    {$out: "abc"}
])

测试结果

如图：查询出【books】集合中符合条件的文档，然后插入到 【abc】这个集合中。

因为【$out】在插入指定集合【abc】前，若该集合【abc】已经存在，会将该集合【abc】先删除掉【abc集合中的文档也随着删除】，然后创建一个新的【abc】集合，再将查询出来的文档数据插入进去。

2、$merge ： 将 item 中 price 大于 30 的所有文档选出、合并到目标集合【ccc】中。

$merge 会将选出的文档合并到目标集合中。这意味着当目标集合存在时，则直接插入选出的文档；

如果目标集合不存在，则创建新集合、插入文档 。

2-1、测试前，【ccc】集合不存在

命令

db.item.aggregate([
    {$match: {price: {$gt: 30}}},
    {$merge: "ccc"}
])

测试结果

如图：查询出来的数据，插入到【ccc】集合中，以为此前没有【ccc】这个集合，所以自行创建一个出来

2-1、【ccc】集合已存在的测试：合并测试

命令

db.books.aggregate([
    {$match: {type: "漫画"}},
    {$merge: "ccc"}
])

测试结果

如图：查询出来的数据，成功合并到【ccc】集合之中。