MongoDB multi table Association query - $lookup - --- kalrry

Posted by delldeveloper on Sun, 13 Feb 2022 21:12:17 +0100

1, Aggregation framework

Aggregation framework is MongoDB's high-level query language, which allows us to generate new information that does not exist in a single document by transforming and merging data in multiple documents.

Polymerization pipeline operation mainly includes the following parts:

commandFunction descriptionremarks
$projectSpecify the fields in the output document
$matchSelect the document to process, similar to fine()
$limitLimit the number of documents passed to the next step
$skipSkip a certain number of documents
$unwindExpand the array and generate an output document for each array entry
$groupGroup documents by key
$sortSort documents
$geoNearSelect a document near a geographic location
$outWrite the results of the pipeline to a collection
$redactControl access to specific data
$lookupMulti table Association (new in version 3.2)

Here is how to use $lookup

2, Function and syntax of $lookup

  1. The main function is to input each document to be processed. After processing in the $lookup stage, the output new document will contain a newly generated array column (the account name can name the name of the new key as needed). The data stored in the array column is from the adaptation document of the Join set. If not, the set is empty (that is [])

  2. Basic grammar

       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
  1. Explanation of grammar
Syntax valueFunction descriptionremarks
fromCollection waiting to be joined under the same database
localFieldThe match value in the source set. If a document does not have the Key (Field) of localField in the input set, it will contain the Key value pair of localField: null by default during processing
foreignFieldmatch value of the set to be joined. If there is no foreignField in the document in the set to be joined
Value. In the process of processing, the document will contain the key value pair of foreignField: null by default
asName the new increment for the output document. If the value already exists in the input set, it will be overwritten
  1. Note: null = null, this is true
    Its syntax function is similar to the following pseudo SQL statement
SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (SELECT *
                               FROM <collection to join>
                               WHERE <foreignField>= <collection.localField>);

3, Case

  1. Assuming there is an order set, the stored test data are as follows
    The data corresponding to item is the product name
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
  1. The other is the commodity inventory collection. The stored test data are as follows
   { "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
   { "_id" : 5, "sku": null, description: "Incomplete" },
   { "_id" : 6 }

The sku data in this set is equivalent to the item name in the order set.
In this mode design, how to write the code if you want to query the inventory of the goods corresponding to the order table?
Obviously, this requires two sets, Join.
The scene is simple, without repeating, send the answer directly. The statement is as follows

         from: "inventory",
         localField: "item",
         foreignField: "sku",
         as: "inventory_docs"

The returned execution results are as follows

    "_id" : NumberInt("1"),
    "item" : "almonds",
    "price" : NumberInt("12"),
    "quantity" : NumberInt("2"),
    "inventory_docs" : [
            "_id" : NumberInt("1"),
            "sku" : "almonds",
            "description" : "product 1",
            "instock" : NumberInt("120")

    "_id" : NumberInt("2"),
    "item" : "pecans",
    "price" : NumberInt("20"),
    "quantity" : NumberInt("1"),
    "inventory_docs" : [
            "_id" : NumberInt("4"),
            "sku" : "pecans",
            "description" : "product 4",
            "instock" : NumberInt("70")

    "_id" : NumberInt("3"),
    "inventory_docs" : [
            "_id" : NumberInt("5"),
            "sku" : null,
            "description" : "Incomplete"
            "_id" : NumberInt("6")

4, Reference

1,Explain MongoDB in detail

Topics: Database MongoDB