Aggregate .lookup(object: Object): Aggregate

Support: Cloud function 1.3.0

The aggregation phase. T he aggregation phase. U nion table queries. D o left outer join (outside left connection) with a specified collection under the same database. F or each input record for that stage, lookup adds an array field to the record, which is a list of records that meet the matching criteria in the union table. Lookup outputs the results of the connection to the next stage.

Parameters

object: Object

Returns a value

Aggregate

API description

Lookup is used in two ways

1. Equal matching

When an input record is equally matched to a field in a connected collection, the following definition is used:

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

Detailed description of the parameters

The parameter field	Description
from	The name of the other collection to connect to
localField	The field name of the input record for the current pipeline, which will be used from foreignField specified from. If the field is not in the input record, the value of the field is considered null
foreignField	The field name of the connected collection, which is used localField equally. If there is no field in the record of the connected collection, the value of the field is treated as null
as	Specifies the field name to be stored in the list of records matched by the connection, and this array contains matching from collection. If the field already exists in the input record, the field is override

This operation is equivalent to the following pseudo SQL operations:

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

example:

1. Specify a phase equal match condition
2. Equal matching of array field applications
3. Combine MergeObjects application equal match

2. Custom connection conditions, splicing subqueries

If you need to specify a connection condition other than equal match, or specify a plurality of equal match conditions, or you need to splicing the subqueries result of the connected set, then the following definition can be used:

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

Detailed description of the parameters

The parameter field	Description
from	The name of the other collection to connect to
let	Optional. S pecify the pipeline can be used in the pipeline, and the value of the variable can refer to the field of the input record, such as let: . . let: { userName: '$name' } means that the name field of the name record is the value of the variable userName Fields pipeline that do not have direct access to the input record must be let by let before they can be accessed in expr operator $$变量名 variable name, such as $$userName
pipeline	Specifies the aggregate operation to run in the connected collection. I f you want to return the entire collection, the field is taken as an empty [] Fields pipeline that do not have direct access to the input record must be let by let before they can be accessed in expr operator $$变量名 variable name, such as $$userName
as	Specifies the field name to be stored in the list of records matched by the connection, and this array contains matching from collection. If the field already exists in the input record, the field is override

This action is equivalent to the following pseudo SQL statements:

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

Example

1. Specify more than one connection condition
2. Stitch the sub-queries of the connected collection

Example

Specify an equal match condition

Suppose the orders collection has the following records:

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

The books collection has the following records:

[
  {"_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"}
]

The following aggregations can connect the orders and books collections with an equal matching condition, which is the book field of the orders collection and the title field of the books collection:

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))

Results:

[
  {
    "_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"
      }
    ]
  }
]

Apply an equal match to the array field

Suppose the authors collection has the following records:

[
  {"_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"}
]

The books collection has the following records:

[
  {"_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"}
]

The following gets author information and the books they publish separately, using the lookup action to match the name field of the authors collection and the authors array field of the books collection:

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))

Results

[
  {
    "_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"
        ]
      }
    ]
  }
]

Combine mergeObjects apps for equal matching

Suppose the orders collection has the following records:

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

The books collection has the following records:

[
  {"_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"}
]

The following actions match the book field of orders and the title field of books, and merge the books match results directly into the orders record.

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))

Results

[
  {
    "_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
  }
]

Specify more than one connection condition

Suppose the orders collection has the following records:

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

The books collection has the following records:

[
  {"_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"}
]

The following actions connect the orders and books collections and require two conditions:

1. The book field of orders is equal to the title field of books
2. The quantity field of orders is greater than or equal to the stock field of books

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))

Results:

[
  {
    "_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
      }
    ]
  }
]

Stitch the sub-queries of the connected collection

Suppose the orders collection has the following records:

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

The books collection has the following records:

[
  {"_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"}
]

Add an array field to each output record, the value of which is the result of a query statement for the books collection:

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))

Results

[
  {
    "_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
      }
    ]
  }
]