SDK數(shù)據(jù)庫(kù) Aggregate·聯(lián)表查詢

2022-05-12 16:47 更新

Aggregate.lookup(object: Object): Aggregate

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

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

參數(shù)

object: Object

返回值

Aggregate

API 說(shuō)明

lookup 有兩種使用方式

1. 相等匹配

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

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

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

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

這個(gè)操作等價(jià)于以下偽 SQL 操作：

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (SELECT *
                               FROM <collection to join>
                               WHERE <foreignField>= <collection.localField>);

例子：

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

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

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

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

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

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

該操作等價(jià)于以下偽 SQL 語(yǔ)句：

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
                               FROM <collection to join>
                               WHERE <pipeline> );

例子

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

示例

指定一個(gè)相等匹配條件

假設(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"}
]

以下聚合操作可以通過(guò)一個(gè)相等匹配條件連接 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"
      }
    ]
  }
]

對(duì)數(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
  }
]

指定多個(gè)連接條件

假設(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 集合，要求兩個(gè)條件：

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

在每條輸出記錄上加上一個(gè)數(shù)組字段，該數(shù)組字段的值是對(duì) books 集合的一個(gè)查詢語(yǔ)句的結(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
      }
    ]
  }
]


以上內(nèi)容是否對(duì)您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號(hào)
微信公眾號(hào)

編程獅公眾號(hào)