Aggregate.lookup

1. Interface Description

Function: Aggregation stage. Performs a table join. Executes a left outer join with a specified collection in the same database. For each input record in this stage, lookup adds an array field containing a list of matching records from the joined collection. lookup then outputs the joined results to the next stage.

Declaration: limit(object)

Two forms

1. Equality match

When performing an equality match between a field of the input record and a field of the joined collection, the following definition applies:

lookup({
  from: <name of the collection to join>,
  localField: <The field in the input record for equality matching>,
  foreignField: <The field in the joined collection for equality matching>,
  as: <output array field name>
})

2. Custom join conditions and subquery assembly

If you need to specify join conditions other than equality matches, define multiple equality match conditions, or assemble subquery results from the joined collection, the following definition can be used:

lookup({
  from: <name of the collection to join>,
  let: { <variable1>: <expression1>, ..., <variablen>: <expressionn> },
  pipeline: [ <pipeline operations on the collection to join> ],
  as: <output array field name>
})

2. Input Parameters

1. Equality match

Parameter	Type	Required	Description
from	string	Yes	The name of another collection to be joined
localField	string	Yes	The field name in the input record of the current pipeline, used for equality matching with the foreignField of the collection specified by 'from'. If the input record lacks this field, its value is treated as null during matching.
foreignField	string	Yes	The field name in the joined collection, used for equality matching with the localField. If a record in the joined collection lacks this field, its value is treated as null during matching.
as	string	Yes	Specifies the field name to store the list of matched records. This array contains matched records from the 'from' collection. If the input record already has this field, it will be overwritten.

• The operation is equivalent to the following pseudo-SQL operation:

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

2. Custom join conditions and subquery assembly

Parameter	Type	Required	Description
from	string	Yes	The name of another collection to be joined
let	string	No	Specifies variables that can be used in the pipeline. The value of variables can reference fields from input records, e.g., let: { userName: '$name' } means using the input record's name field as the userName variable value. Input record fields cannot be accessed directly in the pipeline; they must be defined via let before access. Access is achieved in expr operators using $$variable_name syntax, e.g., $\$userName.
pipeline	any	Yes	Specifies the aggregation operations to run on the joined collection. Set to an empty array [] to return the entire collection. Input record fields cannot be accessed directly in the pipeline; they must be defined via let before access. Access is achieved in expr operators using the $$variable_name syntax, e.g., $$userName.
as	string	Yes	Specifies the field name to store the list of matched records. This array contains matched records from the 'from' collection. If the input record already has this field, it will be overwritten.

• The operation is equivalent to the following pseudo-SQL operation:

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

3. Response

Parameter	Type	Required	Description
-	Aggregate	Yes	Aggregation object

4. Sample Code

Specify an equality match condition

| as | string | Yes | Specifies the field name to store the list of matched records. This array contains matched records from the 'from' collection. If the input record already has this field, it will be overwritten. |

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

The books collection contains 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 aggregation operation can join the orders and books collections using an equality match condition, where the book field from the orders collection is matched with the title field from the books collection:

const tcb = require("@cloudbase/node-sdk");
const app = tcb.init({
  env: "xxx",
});

const db = app.database();
const $ = db.command.aggregate;
const _ = db.command;

exports.main = async (event, context) => {
  const res = await db
    .collection("orders")
    .aggregate()
    .lookup({
      from: "books",
      localField: "book",
      foreignField: "title",
      as: "bookList",
    })
    .end();
  console.log(res.data);
};

Result:

[
  {
    "_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 equality match to an array field

Suppose the authors collection contains 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 contains 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 operation retrieves author information and their respective published books by using a lookup operation to match the name field in the authors collection with the authors array field in the books collection:

const tcb = require("@cloudbase/node-sdk");
const app = tcb.init({
  env: "xxx",
});

const db = app.database();
const $ = db.command.aggregate;
const _ = db.command;

exports.main = async (event, context) => {
  const res = await db
    .collection("authors")
    .aggregate()
    .lookup({
      from: "books",
      localField: "name",
      foreignField: "authors",
      as: "publishedBooks",
    })
    .end();
  console.log(res.data);
};

Result

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

Apply an equality match by combining mergeObjects

| as | string | Yes | Specifies the field name to store the list of matched records. This array contains matched records from the 'from' collection. If the input record already has this field, it will be overwritten. |

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

The books collection contains 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 operation matches the book field in orders with the title field in books and directly merges the matched books results into the orders records.

const tcb = require("@cloudbase/node-sdk");
const app = tcb.init({
  env: "xxx",
});

const db = app.database();
const $ = db.command.aggregate;
const _ = db.command;

exports.main = async (event, context) => {
  const res = await db
    .collection("orders")
    .aggregate()
    .lookup({
      from: "books",
      localField: "book",
      foreignField: "title",
      as: "bookList",
    })
    .replaceRoot({
      newRoot: $.mergeObjects([$.arrayElemAt(["$bookList", 0]), "$$ROOT"]),
    })
    .project({
      bookList: 0,
    })
    .end();
  console.log(res.data);
};

Result

[
  {
    "_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 multiple join conditions

| as | string | Yes | Specifies the field name to store the list of matched records. This array contains matched records from the 'from' collection. If the input record already has this field, it will be overwritten. |

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

The books collection contains 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 operation joins the orders and books collections with two conditions:

The book field in orders equals the title field in books. The quantity field in orders is greater than or equal to the stock field in books.

const tcb = require("@cloudbase/node-sdk");
const app = tcb.init({
  env: "xxx",
});

const db = app.database();
const $ = db.command.aggregate;
const _ = db.command;

exports.main = async (event, context) => {
  const res = await 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();
  console.log(res.data);
};

Result:

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

Combining Subqueries of Joined Collections

| as | string | Yes | Specifies the field name to store the list of matched records. This array contains matched records from the 'from' collection. If the input record already has this field, it will be overwritten. |

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

The books collection contains 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, where the value of this array field is the result of a query on the books collection:

const tcb = require("@cloudbase/node-sdk");
const app = tcb.init({
  env: "xxx",
});

const db = app.database();
const $ = db.command.aggregate;
const _ = db.command;

exports.main = async (event, context) => {
  const res = await 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();
  console.log(res.data);
};

Result

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