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:

command	Function description	remarks
$project	Specify the fields in the output document
$match	Select the document to process, similar to fine()
$limit	Limit the number of documents passed to the next step
$skip	Skip a certain number of documents
$unwind	Expand the array and generate an output document for each array entry
$group	Group documents by key
$sort	Sort documents
$geoNear	Select a document near a geographic location
$out	Write the results of the pipeline to a collection
$redact	Control access to specific data
$lookup	Multi 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

{
   $lookup:
     {
       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 value	Function description	remarks
from	Collection waiting to be joined under the same database
localField	The 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
foreignField	match 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
as	Name 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

db.orders.insert([
   { "_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

db.inventory.insert([
   { "_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

db.orders.aggregate([
   {
     $lookup:
       {
         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