日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學無先后，達者為師

網站首頁 編程語言 正文

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

欄目分類
最近更新