学无先后，达者为师

网站首页 编程语言 正文

MongoDB聚合管道 $lookup 与$mergeObjects配合使用 以及使用let，pipeline自定义参数进行指定多个连接条件

作者：正直的刘大炮. 更新时间： 2022-09-25 编程语言

使用$lookup 与$mergeObjects配合使用

以及进行指定多个连接条件

语法 说明
from 同一个数据库下等待被Join的集合。
localField 源集合中的match值，如果输入的集合中，某文档没有 localField这个Key（Field），在处理的过程中，会默认为此文档含有 localField：null的键值对。
foreignField 待Join的集合的match值，如果待Join的集合中，文档没有foreignField值，在处理的过程中，会默认为此文档含有 foreignField：null的键值对
as 为输出文档的新增值命名。如果输入的集合中已存在该值，则会覆盖掉

注意！！

$lookup是如果涉及关联"_id",注意两个字段的类型,用string类型匹配ObjectId类型是关联不上的

准备数据

//订单表
db.orders.insert([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
])
//库存表
db.inventory.insert([
   { "_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 }
])

两种用法
1.一种关联

{
   $lookup:
     {
       //要关联的从表名
       from: <collection to join>,
       //主表关联字段
       localField: <field from the input documents>,
       //从表中与主表关联的字段
       foreignField: <field from the documents of the "from" collection>, 
       //别名
       as: <output array field>	
     }
}

2.自定义多种关联

{
   $lookup:
     {
       //要关联的从表名
       from: <collection to join>,
       //自定义变量有一个或多个变量
       let: { <var_1>: <expression>, …, <var_n>: <expression> },
       //自定义的操作从表的聚合但不允许使用out和merge操作
       pipeline: [ <pipeline to execute on the collection to join> ],
       //别名
       as: <output array field>	
     }
}

orders表为主表 inventory表为从表 根据 orders表的item字段与inventory表的sku进行关联

db.orders.aggregate([
   {
     $lookup:
       {
         from: "inventory",
         localField: "item",
         foreignField: "sku",
         as: "inventory_docs"
       }
  }
])

该操作返回以下文档：

// 1
{
    "_id": 1,
    "item": "almonds",
    "price": 12,
    "quantity": 2,
    "inventory_docs": [
        {
            "_id": 1,
            "sku": "almonds",
            "description": "product 1",
            "instock": 120
        }
    ]
}

// 2
{
    "_id": 2,
    "item": "pecans",
    "price": 20,
    "quantity": 1,
    "inventory_docs": [
        {
            "_id": 4,
            "sku": "pecans",
            "description": "product 4",
            "instock": 70
        }
    ]
}

// 3
{
    "_id": 3,
    "inventory_docs": [
        {
            "_id": 5,
            "sku": null,
            "description": "Incomplete"
        },
        {
            "_id": 6
        }
    ]
}

对应sql的写法

SELECT *, inventory_docs
FROM orders
WHERE inventory_docs IN (SELECT *
FROM inventory
WHERE sku= orders.item);

使用lookup操作数组

准备数据

db.classes.insert([
    {
        _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.insert( [
   { _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" }
])

classes主表 通过enrollmentlist 与members从表进行关联

不同之处是：主表的关联字段是数组的结构 跟从表的关联关系时多对一的关系

db.classes.aggregate([
   {
      $lookup:
         {
            from: "members",
            localField: "enrollmentlist",
            foreignField: "name",
            as: "enrollee_info"
        }
   }
])

返回的集合

// 1
{
    "_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"
        }
    ]
}

// 2
{
    "_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"
        }
    ]
}

使用$lookup 与$mergeObjects配合使用

db.orders.insert([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
])
db.items.insert([
  { "_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 }
])

$replaceRoot：用指定的文档替换输入文档。该操作将替换输入文档中的所有现有字段，包括_id字段。您可以将现有嵌入文档提升到顶层，或创建新文档进行提升

$mergeObjects：将多个文档合并为一个文档。

mergeObjects忽略对象{}。如果mergeObjects的所有操作数都解析为null，那么mergeObject将返回一个空文档{}。

合并对象在合并文档时覆盖字段值。如果要合并的文档包含相同的字段名，则结果文档中的字段具有该字段最后一个合并文档中的值。

$arrayElemAt：返回指定数组索引处的元素。

db.orders.aggregate([
    {
        $lookup: {
            from: "items",
            localField: "item", //orders表的字段
            foreignField: "item", // 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指定多个联接条件

准备数据

db.orders.insert([
  { "_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.insert([
  { "_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 }
])

注意:

pipeline中的 m a t c h 特殊操作需要 match特殊操作需要 match特殊操作需要expr进行条件过滤,如果在使用match没有效果的的情况下可以试试$expr哦

$expr运算符仅使用from集合上的索引进行相等匹配。例如，如果仓库集合上存在索引{stock_item:1，instock:1}：

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

对应sql的写法

SELECT *, stockdata
FROM orders
WHERE stockdata IN ( SELECT warehouse, instock
                     FROM warehouses
                     WHERE stock_item = orders.item
                     AND instock >= orders.ordered );

允许指定多个连接条件以及不相关的子查询。

准备数据

db.absences.insert([
   { "_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.insert([
   { "_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") }
])

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:00Z"), ISODate("2018-08-23T00:00:00Z")],
    "holidays": [{
        "name": "New Years",
        "date": ISODate("2018-01-01T00:00:00Z")
    }, {
        "name": "Pi Day",
        "date": ISODate("2018-03-14T00:00:00Z")
    }, {
        "name": "Ice Cream Day",
        "date": ISODate("2018-07-15T00:00:00Z")
    }]
}
{
    "_id": 2,
    "student": "Zoe Zebra",
    "sickdays": [ISODate("2018-02-01T00:00:00Z"), ISODate("2018-05-23T00:00:00Z")],
    "holidays": [{
        "name": "New Years",
        "date": ISODate("2018-01-01T00:00:00Z")
    }, {
        "name": "Pi Day",
        "date": ISODate("2018-03-14T00:00:00Z")
    }, {
        "name": "Ice Cream Day",
        "date": ISODate("2018-07-15T00:00:00Z")
    }]
}

对应sql的写法

SELECT *, holidays
FROM absences
WHERE holidays IN (SELECT name, date
                    FROM holidays
                    WHERE year = 2018);

内容来自：
mongoDB $lookup (aggregation)官方文档版本4.2

mongoDB $mergeObjects (aggregation)官方文档版本4.2

mongoDB $arrayElemAt (aggregation)官方文档版本4.2

mongoDB $replaceRoot (aggregation)官方文档版本4.2

原文链接：https://blog.csdn.net/qq_32492415/article/details/127032706

相关推荐

栏目分类
最近更新
Copyright © 2025   AB教程网    免责声明
本站部分资源收集于互联网、用户投稿，其版权归原作者所有，内容以共享、参考、学习为主，不存在任何商业目的。如有侵权请与本站联系，情况属实将予以删除!
本站由    提供CDN加速