日本综合一区二区|亚洲中文天堂综合|日韩欧美自拍一区|男女精品天堂一区|欧美自拍第6页亚洲成人精品一区|亚洲黄色天堂一区二区成人|超碰91偷拍第一页|日韩av夜夜嗨中文字幕|久久蜜综合视频官网|精美人妻一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
創(chuàng)新互聯(lián)小程序教程：SDK數(shù)據(jù)庫Aggregate·聯(lián)表查詢

Aggregate.lookup(object: Object): Aggregate

支持端：云函數(shù) 1.3.0

創(chuàng)新互聯(lián)專注于企業(yè)全網(wǎng)營銷推廣、網(wǎng)站重做改版、杭錦網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5場景定制、商城網(wǎng)站建設(shè)、集團(tuán)公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計(jì)等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為杭錦等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。

聚合階段。聚合階段。聯(lián)表查詢。與同個數(shù)據(jù)庫下的一個指定的集合做 left outer join(左外連接)。對該階段的每一個輸入記錄，lookup 會在該記錄中增加一個數(shù)組字段，該數(shù)組是被聯(lián)表中滿足匹配條件的記錄列表。lookup 會將連接后的結(jié)果輸出給下個階段。

參數(shù)

object: Object

返回值

Aggregate

API 說明

lookup 有兩種使用方式

1. 相等匹配

將輸入記錄的一個字段和被連接集合的一個字段進(jìn)行相等匹配時，采用以下定義：

lookup({
  from: <要連接的集合名>,
  localField: <輸入記錄的要進(jìn)行相等匹配的字段>,
  foreignField: <被連接集合的要進(jìn)行相等匹配的字段>,
  as: <輸出的數(shù)組字段名>
})

參數(shù)詳細(xì)說明

參數(shù)字段 說明
from要進(jìn)行連接的另外一個集合的名字
localField當(dāng)前流水線的輸入記錄的字段名，該字段將被用于與 from 指定的集合的 foreignField 進(jìn)行相等匹配。如果輸入記錄中沒有該字段，則該字段的值在匹配時會被視作 null
foreignField被連接集合的字段名，該字段會被用于與 localField 進(jìn)行相等匹配。如果被連接集合的記錄中沒有該字段，該字段的值將在匹配時被視作 null
as指定連接匹配出的記錄列表要存放的字段名，這個數(shù)組包含的是匹配出的來自 from 集合的記錄。如果輸入記錄中本來就已有該字段，則該字段會被覆寫

這個操作等價于以下偽 SQL 操作：

SELECT *, 
FROM collection
WHERE  IN (SELECT *
                               FROM 
                               WHERE = );

例子：

  1. 指定一個相等匹配條件
  2. 對數(shù)組字段應(yīng)用相等匹配
  3. 組合 mergeObjects 應(yīng)用相等匹配

2. 自定義連接條件、拼接子查詢

如果需要指定除相等匹配之外的連接條件，或指定多個相等匹配條件，或需要拼接被連接集合的子查詢結(jié)果，那可以使用如下定義：

lookup({
  from: <要連接的集合名>,
  let: { <變量1>: <表達(dá)式1>, ..., <變量n>: <表達(dá)式n> },
  pipeline: [ <在要連接的集合上進(jìn)行的流水線操作> ],
  as: <輸出的數(shù)組字段名>
})

參數(shù)詳細(xì)說明

參數(shù)字段 說明
from要進(jìn)行連接的另外一個集合的名字
let可選。指定在 pipeline 中可以使用的變量，變量的值可以引用輸入記錄的字段，比如 let: { userName: '$name' } 就代表將輸入記錄的 name 字段作為變量 userName 的值。在 pipeline 中無法直接訪問輸入記錄的字段，必須通過 let 定義之后才能訪問，訪問的方式是在 expr 操作符中用 $$變量名 的方式訪問，比如 $$userName。
pipeline指定要在被連接集合中運(yùn)行的聚合操作。如果要返回整個集合，則該字段取值空數(shù)組 []。在 pipeline 中無法直接訪問輸入記錄的字段，必須通過 let 定義之后才能訪問，訪問的方式是在 expr 操作符中用 $$變量名 的方式訪問，比如 $$userName。
as指定連接匹配出的記錄列表要存放的字段名，這個數(shù)組包含的是匹配出的來自 from 集合的記錄。如果輸入記錄中本來就已有該字段，則該字段會被覆寫

該操作等價于以下偽 SQL 語句：

SELECT *, 
FROM collection
WHERE  IN (SELECT 
                               FROM 
                               WHERE  );

例子

  1. 指定多個連接條件
  2. 拼接被連接集合的子查詢

示例

指定一個相等匹配條件

假設(shè) orders 集合有以下記錄：

[
  {"_id":4,"book":"novel 1","price":30,"quantity":2},
  {"_id":5,"book":"science 1","price":20,"quantity":1},
  {"_id":6}
]

books 集合有以下記錄：

[
  {"_id":"book1","author":"author 1","category":"novel","stock":10,"time":1564456048486,"title":"novel 1"},
  {"_id":"book3","author":"author 3","category":"science","stock":30,"title":"science 1"},
  {"_id":"book4","author":"author 3","category":"science","stock":40,"title":"science 2"},
  {"_id":"book2","author":"author 2","category":"novel","stock":20,"title":"novel 2"},
  {"_id":"book5","author":"author 4","category":"science","stock":50,"title":null},
  {"_id":"book6","author":"author 5","category":"novel","stock":"60"}
]

以下聚合操作可以通過一個相等匹配條件連接 orders 和 books 集合，匹配的字段是 orders 集合的 book 字段和 books 集合的 title 字段：

const db = cloud.database()
db.collection('orders').aggregate()
  .lookup({
    from: 'books',
    localField: 'book',
    foreignField: 'title',
    as: 'bookList',
  })
  .end()
  .then(res => console.log(res))
  .catch(err => console.error(err))

結(jié)果：

[
  {
    "_id": 4,
    "book": "novel 1",
    "price": 30,
    "quantity": 2,
    "bookList": [
      {
        "_id": "book1",
        "title": "novel 1",
        "author": "author 1",
        "category": "novel",
        "stock": 10
      }
    ]
  },
  {
    "_id": 5,
    "book": "science 1",
    "price": 20,
    "quantity": 1,
    "bookList": [
      {
        "_id": "book3",
        "category": "science",
        "title": "science 1",
        "author": "author 3",
        "stock": 30
      }
    ]
  },
  {
    "_id": 6,
    "bookList": [
      {
        "_id": "book5",
        "category": "science",
        "author": "author 4",
        "stock": 50,
        "title": null
      },
      {
        "_id": "book6",
        "author": "author 5",
        "stock": "60",
        "category": "novel"
      }
    ]
  }
]

對數(shù)組字段應(yīng)用相等匹配

假設(shè) authors 集合有以下記錄：

[
  {"_id": 1, "name": "author 1", "intro": "Two-time best-selling sci-fiction novelist"},
  {"_id": 3, "name": "author 3", "intro": "UCB assistant professor"},
  {"_id": 4, "name": "author 4", "intro": "major in CS"}
]

books 集合有以下記錄：

[
  {"_id":"book1","authors":["author 1"],"category":"novel","stock":10,"time":1564456048486,"title":"novel 1"},
  {"_id":"book3","authors":["author 3", "author 4"],"category":"science","stock":30,"title":"science 1"},
  {"_id":"book4","authors":["author 3"],"category":"science","stock":40,"title":"science 2"}
]

以下操作獲取作者信息及他們分別發(fā)表的書籍，使用了 lookup 操作匹配 authors 集合的 name 字段和 books 集合的 authors 數(shù)組字段：

const db = cloud.database()
db.collection('authors').aggregate()
  .lookup({
    from: 'books',
    localField: 'name',
    foreignField: 'authors',
    as: 'publishedBooks',
  })
  .end()
  .then(res => console.log(res))
  .catch(err => console.error(err))

結(jié)果

[
  {
    "_id": 1,
    "intro": "Two-time best-selling sci-fiction novelist",
    "name": "author 1",
    "publishedBooks": [
      {
        "_id": "book1",
        "title": "novel 1",
        "category": "novel",
        "stock": 10,
        "authors": [
          "author 1"
        ]
      }
    ]
  },
  {
    "_id": 3,
    "name": "author 3",
    "intro": "UCB assistant professor",
    "publishedBooks": [
      {
        "_id": "book3",
        "category": "science",
        "title": "science 1",
        "stock": 30,
        "authors": [
          "author 3",
          "author 4"
        ]
      },
      {
        "_id": "book4",
        "title": "science 2",
        "category": "science",
        "stock": 40,
        "authors": [
          "author 3"
        ]
      }
    ]
  },
  {
    "_id": 4,
    "intro": "major in CS",
    "name": "author 4",
    "publishedBooks": [
      {
        "_id": "book3",
        "category": "science",
        "title": "science 1",
        "stock": 30,
        "authors": [
          "author 3",
          "author 4"
        ]
      }
    ]
  }
]

組合 mergeObjects 應(yīng)用相等匹配

假設(shè) orders 集合有以下記錄：

[
  {"_id":4,"book":"novel 1","price":30,"quantity":2},
  {"_id":5,"book":"science 1","price":20,"quantity":1},
  {"_id":6}
]

books 集合有以下記錄：

[
  {"_id":"book1","author":"author 1","category":"novel","stock":10,"time":1564456048486,"title":"novel 1"},
  {"_id":"book3","author":"author 3","category":"science","stock":30,"title":"science 1"},
  {"_id":"book4","author":"author 3","category":"science","stock":40,"title":"science 2"},
  {"_id":"book2","author":"author 2","category":"novel","stock":20,"title":"novel 2"},
  {"_id":"book5","author":"author 4","category":"science","stock":50,"title":null},
  {"_id":"book6","author":"author 5","category":"novel","stock":"60"}
]

以下操作匹配 orders 的 book 字段和 books 的 title 字段，并將 books 匹配結(jié)果直接 merge 到 orders 記錄中。

var db = cloud.database()
var $ = db.command.aggregate
db.collection('orders').aggregate()
  .lookup({
    from: "books",
    localField: "book",
    foreignField: "title",
    as: "bookList"
  })
  .replaceRoot({
    newRoot: $.mergeObjects([ $.arrayElemAt(['$bookList', 0]), '$$ROOT' ])
  })
  .project({
    bookList: 0
  })
  .end()
  .then(res => console.log(res))
  .catch(err => console.error(err))

結(jié)果

[
  {
    "_id": 4,
    "title": "novel 1",
    "author": "author 1",
    "category": "novel",
    "stock": 10,
    "book": "novel 1",
    "price": 30,
    "quantity": 2
  },
  {
    "_id": 5,
    "category": "science",
    "title": "science 1",
    "author": "author 3",
    "stock": 30,
    "book": "science 1",
    "price": 20,
    "quantity": 1
  },
  {
    "_id": 6,
    "category": "science",
    "author": "author 4",
    "stock": 50,
    "title": null
  }
]

指定多個連接條件

假設(shè) orders 集合有以下記錄：

[
  {"_id":4,"book":"novel 1","price":300,"quantity":20},
  {"_id":5,"book":"science 1","price":20,"quantity":1}
]

books 集合有以下記錄：

[
  {"_id":"book1","author":"author 1","category":"novel","stock":10,"time":1564456048486,"title":"novel 1"},
  {"_id":"book3","author":"author 3","category":"science","stock":30,"title":"science 1"}
]

以下操作連接 orders 和 books 集合，要求兩個條件：

  1. orders 的 book 字段與 books 的 title 字段相等
  2. orders 的 quantity 字段大于或等于 books 的 stock 字段
const db = cloud.database()
const $ = db.command.aggregate
db.collection('orders').aggregate()
  .lookup({
    from: 'books',
    let: {
      order_book: '$book',
      order_quantity: '$quantity'
    },
    pipeline: $.pipeline()
      .match(_.expr($.and([
        $.eq(['$title', '$$order_book']),
        $.gte(['$stock', '$$order_quantity'])
      ])))
      .project({
        _id: 0,
        title: 1,
        author: 1,
        stock: 1
      })
      .done(),
    as: 'bookList',
  })
  .end()
  .then(res => console.log(res))
  .catch(err => console.error(err))

結(jié)果：

[
  {
    "_id": 4,
    "book": "novel 1",
    "price": 300,
    "quantity": 20,
    "bookList": []
  },
  {
    "_id": 5,
    "book": "science 1",
    "price": 20,
    "quantity": 1,
    "bookList": [
      {
        "title": "science 1",
        "author": "author 3",
        "stock": 30
      }
    ]
  }
]

拼接被連接集合的子查詢

假設(shè) orders 集合有以下記錄：

[
  {"_id":4,"book":"novel 1","price":30,"quantity":2},
  {"_id":5,"book":"science 1","price":20,"quantity":1}
]

books 集合有以下記錄：

[
  {"_id":"book1","author":"author 1","category":"novel","stock":10,"time":1564456048486,"title":"novel 1"},
  {"_id":"book3","author":"author 3","category":"science","stock":30,"title":"science 1"},
  {"_id":"book4","author":"author 3","category":"science","stock":40,"title":"science 2"}
]

在每條輸出記錄上加上一個數(shù)組字段，該數(shù)組字段的值是對 books 集合的一個查詢語句的結(jié)果：

const db = cloud.database()
const $ = db.command.aggregate
db.collection('orders').aggregate()
  .lookup({
    from: 'books',
    let: {
      order_book: '$book',
      order_quantity: '$quantity'
    },
    pipeline: $.pipeline()
      .match({
        author: 'author 3'
      })
      .project({
        _id: 0,
        title: 1,
        author: 1,
        stock: 1
      })
      .done(),
    as: 'bookList',
  })
  .end()
  .then(res => console.log(res))
  .catch(err => console.error(err))

結(jié)果

[
  {
    "_id": 4,
    "book": "novel 1",
    "price": 30,
    "quantity": 20,
    "bookList": [
      {
        "title": "science 1",
        "author": "author 3",
        "stock": 30
      },
      {
        "title": "science 2",
        "author": "author 3",
        "stock": 40
      }
    ]
  },
  {
    "_id": 5,
    "book": "science 1",
    "price": 20,
    "quantity": 1,
    "bookList": [
      {
        "title": "science 1",
        "author": "author 3",
        "stock": 30
      },
      {
        "title": "science 2",
        "author": "author 3",
        "stock": 40
      }
    ]
  }
]

新聞名稱：創(chuàng)新互聯(lián)小程序教程：SDK數(shù)據(jù)庫Aggregate·聯(lián)表查詢
文章位置：http://www.dlmjj.cn/article/ccocdci.html