Aggregate.lookup(object: Object): Aggregate

支持端:云函数 1.3.0

聚合阶段。聚合阶段。联表查询。与同个数据库下的一个指定的集合做 left outer join(左外连接)。对该阶段的每一个输入记录,lookup 会在该记录中增加一个数组字段,该数组是被联表中满足匹配条件的记录列表。lookup 会将连接后的结果输出给下个阶段。

参数

object: Object

返回值

Aggregate

API 说明

lookup 有两种使用方式

1. 相等匹配

将输入记录的一个字段和被连接集合的一个字段进行相等匹配时,采用以下定义:

  1. lookup({
  2. from: <要连接的集合名>,
  3. localField: <输入记录的要进行相等匹配的字段>,
  4. foreignField: <被连接集合的要进行相等匹配的字段>,
  5. as: <输出的数组字段名>
  6. })

参数详细说明

参数字段说明
from要进行连接的另外一个集合的名字
localField当前流水线的输入记录的字段名,该字段将被用于与 from 指定的集合的 foreignField 进行相等匹配。如果输入记录中没有该字段,则该字段的值在匹配时会被视作 null
foreignField被连接集合的字段名,该字段会被用于与 localField 进行相等匹配。如果被连接集合的记录中没有该字段,该字段的值将在匹配时被视作 null
as指定连接匹配出的记录列表要存放的字段名,这个数组包含的是匹配出的来自 from 集合的记录。如果输入记录中本来就已有该字段,则该字段会被覆写

这个操作等价于以下伪 SQL 操作:

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

例子:

2. 自定义连接条件、拼接子查询

如果需要指定除相等匹配之外的连接条件,或指定多个相等匹配条件,或需要拼接被连接集合的子查询结果,那可以使用如下定义:

  1. lookup({
  2. from: <要连接的集合名>,
  3. let: { <变量1>: <表达式1>, ..., <变量n>: <表达式n> },
  4. pipeline: [ <在要连接的集合上进行的流水线操作> ],
  5. as: <输出的数组字段名>
  6. })

参数详细说明

参数字段说明
from要进行连接的另外一个集合的名字
let可选。指定在 pipeline 中可以使用的变量,变量的值可以引用输入记录的字段,比如 let: { userName: '$name' } 就代表将输入记录的 name 字段作为变量 userName 的值。在 pipeline 中无法直接访问输入记录的字段,必须通过 let 定义之后才能访问,访问的方式是在 expr 操作符中用 $$变量名 的方式访问,比如 $$userName
pipeline指定要在被连接集合中运行的聚合操作。如果要返回整个集合,则该字段取值空数组 []。在 pipeline 中无法直接访问输入记录的字段,必须通过 let 定义之后才能访问,访问的方式是在 expr 操作符中用 $$变量名 的方式访问,比如 $$userName
as指定连接匹配出的记录列表要存放的字段名,这个数组包含的是匹配出的来自 from 集合的记录。如果输入记录中本来就已有该字段,则该字段会被覆写

该操作等价于以下伪 SQL 语句:

  1. SELECT *, <output array field>
  2. FROM collection
  3. WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
  4. FROM <collection to join>
  5. WHERE <pipeline> );

例子

示例

指定一个相等匹配条件

假设 orders 集合有以下记录:

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

books 集合有以下记录:

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

以下聚合操作可以通过一个相等匹配条件连接 ordersbooks 集合,匹配的字段是 orders 集合的 book 字段和 books 集合的 title 字段:

  1. const db = cloud.database()
  2. db.collection('orders').aggregate()
  3. .lookup({
  4. from: 'books',
  5. localField: 'book',
  6. foreignField: 'title',
  7. as: 'bookList',
  8. })
  9. .end()
  10. .then(res => console.log(res))
  11. .catch(err => console.error(err))

结果:

  1. [
  2. {
  3. "_id": 4,
  4. "book": "novel 1",
  5. "price": 30,
  6. "quantity": 2,
  7. "bookList": [
  8. {
  9. "_id": "book1",
  10. "title": "novel 1",
  11. "author": "author 1",
  12. "category": "novel",
  13. "stock": 10
  14. }
  15. ]
  16. },
  17. {
  18. "_id": 5,
  19. "book": "science 1",
  20. "price": 20,
  21. "quantity": 1,
  22. "bookList": [
  23. {
  24. "_id": "book3",
  25. "category": "science",
  26. "title": "science 1",
  27. "author": "author 3",
  28. "stock": 30
  29. }
  30. ]
  31. },
  32. {
  33. "_id": 6,
  34. "bookList": [
  35. {
  36. "_id": "book5",
  37. "category": "science",
  38. "author": "author 4",
  39. "stock": 50,
  40. "title": null
  41. },
  42. {
  43. "_id": "book6",
  44. "author": "author 5",
  45. "stock": "60",
  46. "category": "novel"
  47. }
  48. ]
  49. }
  50. ]

对数组字段应用相等匹配

假设 authors 集合有以下记录:

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

books 集合有以下记录:

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

以下操作获取作者信息及他们分别发表的书籍,使用了 lookup 操作匹配 authors 集合的 name 字段和 books 集合的 authors 数组字段:

  1. const db = cloud.database()
  2. db.collection('authors').aggregate()
  3. .lookup({
  4. from: 'books',
  5. localField: 'name',
  6. foreignField: 'authors',
  7. as: 'publishedBooks',
  8. })
  9. .end()
  10. .then(res => console.log(res))
  11. .catch(err => console.error(err))

结果

  1. [
  2. {
  3. "_id": 1,
  4. "intro": "Two-time best-selling sci-fiction novelist",
  5. "name": "author 1",
  6. "publishedBooks": [
  7. {
  8. "_id": "book1",
  9. "title": "novel 1",
  10. "category": "novel",
  11. "stock": 10,
  12. "authors": [
  13. "author 1"
  14. ]
  15. }
  16. ]
  17. },
  18. {
  19. "_id": 3,
  20. "name": "author 3",
  21. "intro": "UCB assistant professor",
  22. "publishedBooks": [
  23. {
  24. "_id": "book3",
  25. "category": "science",
  26. "title": "science 1",
  27. "stock": 30,
  28. "authors": [
  29. "author 3",
  30. "author 4"
  31. ]
  32. },
  33. {
  34. "_id": "book4",
  35. "title": "science 2",
  36. "category": "science",
  37. "stock": 40,
  38. "authors": [
  39. "author 3"
  40. ]
  41. }
  42. ]
  43. },
  44. {
  45. "_id": 4,
  46. "intro": "major in CS",
  47. "name": "author 4",
  48. "publishedBooks": [
  49. {
  50. "_id": "book3",
  51. "category": "science",
  52. "title": "science 1",
  53. "stock": 30,
  54. "authors": [
  55. "author 3",
  56. "author 4"
  57. ]
  58. }
  59. ]
  60. }
  61. ]

组合 mergeObjects 应用相等匹配

假设 orders 集合有以下记录:

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

books 集合有以下记录:

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

以下操作匹配 ordersbook 字段和 bookstitle 字段,并将 books 匹配结果直接 mergeorders 记录中。

  1. var db = cloud.database()
  2. var $ = db.command.aggregate
  3. db.collection('orders').aggregate()
  4. .lookup({
  5. from: "books",
  6. localField: "book",
  7. foreignField: "title",
  8. as: "bookList"
  9. })
  10. .replaceRoot({
  11. newRoot: $.mergeObjects([ $.arrayElemAt(['$bookList', 0]), '$$ROOT' ])
  12. })
  13. .project({
  14. bookList: 0
  15. })
  16. .end()
  17. .then(res => console.log(res))
  18. .catch(err => console.error(err))

结果

  1. [
  2. {
  3. "_id": 4,
  4. "title": "novel 1",
  5. "author": "author 1",
  6. "category": "novel",
  7. "stock": 10,
  8. "book": "novel 1",
  9. "price": 30,
  10. "quantity": 2
  11. },
  12. {
  13. "_id": 5,
  14. "category": "science",
  15. "title": "science 1",
  16. "author": "author 3",
  17. "stock": 30,
  18. "book": "science 1",
  19. "price": 20,
  20. "quantity": 1
  21. },
  22. {
  23. "_id": 6,
  24. "category": "science",
  25. "author": "author 4",
  26. "stock": 50,
  27. "title": null
  28. }
  29. ]

指定多个连接条件

假设 orders 集合有以下记录:

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

books 集合有以下记录:

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

以下操作连接 ordersbooks 集合,要求两个条件:

  • ordersbook 字段与 bookstitle 字段相等
  • ordersquantity 字段大于或等于 booksstock 字段
  1. const db = cloud.database()
  2. const $ = db.command.aggregate
  3. db.collection('orders').aggregate()
  4. .lookup({
  5. from: 'books',
  6. let: {
  7. order_book: '$book',
  8. order_quantity: '$quantity'
  9. },
  10. pipeline: $.pipeline()
  11. .match(_.expr($.and([
  12. $.eq(['$title', '$$order_book']),
  13. $.gte(['$stock', '$$order_quantity'])
  14. ])))
  15. .project({
  16. _id: 0,
  17. title: 1,
  18. author: 1,
  19. stock: 1
  20. })
  21. .done(),
  22. as: 'bookList',
  23. })
  24. .end()
  25. .then(res => console.log(res))
  26. .catch(err => console.error(err))

结果:

  1. [
  2. {
  3. "_id": 4,
  4. "book": "novel 1",
  5. "price": 300,
  6. "quantity": 20,
  7. "bookList": []
  8. },
  9. {
  10. "_id": 5,
  11. "book": "science 1",
  12. "price": 20,
  13. "quantity": 1,
  14. "bookList": [
  15. {
  16. "title": "science 1",
  17. "author": "author 3",
  18. "stock": 30
  19. }
  20. ]
  21. }
  22. ]

拼接被连接集合的子查询

假设 orders 集合有以下记录:

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

books 集合有以下记录:

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

在每条输出记录上加上一个数组字段,该数组字段的值是对 books 集合的一个查询语句的结果:

  1. const db = cloud.database()
  2. const $ = db.command.aggregate
  3. db.collection('orders').aggregate()
  4. .lookup({
  5. from: 'books',
  6. let: {
  7. order_book: '$book',
  8. order_quantity: '$quantity'
  9. },
  10. pipeline: $.pipeline()
  11. .match({
  12. author: 'author 3'
  13. })
  14. .project({
  15. _id: 0,
  16. title: 1,
  17. author: 1,
  18. stock: 1
  19. })
  20. .done(),
  21. as: 'bookList',
  22. })
  23. .end()
  24. .then(res => console.log(res))
  25. .catch(err => console.error(err))

结果

  1. [
  2. {
  3. "_id": 4,
  4. "book": "novel 1",
  5. "price": 30,
  6. "quantity": 20,
  7. "bookList": [
  8. {
  9. "title": "science 1",
  10. "author": "author 3",
  11. "stock": 30
  12. },
  13. {
  14. "title": "science 2",
  15. "author": "author 3",
  16. "stock": 40
  17. }
  18. ]
  19. },
  20. {
  21. "_id": 5,
  22. "book": "science 1",
  23. "price": 20,
  24. "quantity": 1,
  25. "bookList": [
  26. {
  27. "title": "science 1",
  28. "author": "author 3",
  29. "stock": 30
  30. },
  31. {
  32. "title": "science 2",
  33. "author": "author 3",
  34. "stock": 40
  35. }
  36. ]
  37. }
  38. ]