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

以下聚合操作可以通过一个相等匹配条件连接 orders 和 books 集合，匹配的字段是 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. ]

以下操作匹配 orders 的 book 字段和 books 的 title 字段，并将 books 匹配结果直接 merge 到 orders 记录中。

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

以下操作连接 orders 和 books 集合，要求两个条件：

  • orders 的 book 字段与 books 的 title 字段相等
  • orders 的 quantity 字段大于或等于 books 的 stock 字段
  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. ]