Shulou(Shulou.com)06/01 Report--

one。 Aggregation framework

The aggregation framework is a high-level query language of MongoDB, which allows us to transform and merge data from multiple documents to generate information that does not exist in a new single document.

The aggregation pipeline operation mainly consists of the following parts:

The command function description $project specifies the fields in the output document. $match selects the document to be processed, similar to fine (). $limit limits the number of documents passed to the next step. $skip skips a certain number of documents. Unwind extends the array to generate an output document for each array entry. Group groups documents according to key. $sort sorts documents. $geoNear selects a document near a geographic location. $out writes the results of the pipe to a collection. Redact controls access to specific data.

$lookup

Multi-table association (added in version 3.2)

In this space, we focus on the use of $lookup.

two。 The function and grammar of $lookup

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

two。 Basic grammar

{$lookup: {from:, localField:, foreignField:, as:}}

3. Explanation of grammar

Grammatical value interpretation indicates a collection of from waiting to be Join in the same database. LocalField

The match value in the source collection, if a document does not have a localField in the entered collection

This Key (Field), during processing, defaults to the fact that this document contains

There is a key-value pair of localField:null.

The match value of the collection to be Join by foreignField. If there is no foreignField value in the collection to be Join, the document will be defaulted to contain the key-value pair of foreignField:null during processing. As names the new added value of the output document. If the value already exists in the collection entered, it will be overwritten

(note: null = null this is true)

Its syntax function is similar to the following pseudo-SQL statement:

SELECT *, FROM collectionWHERE IN (SELECT * FROM WHERE =)

three。 Case

The above grammar introduction is a bit boring and difficult to understand, so let's directly analyze the taste case.

Suppose there is an order collection, and the stored test data is as follows:

Db.orders.insert ([{"_ id": 1, "item": "almonds", "price": 12, "quantity": 2}, {"_ id": 2, "item": "pecans", "price": 20, "quantity": 1}, {"_ id": 3}])

The corresponding data of item is the name of the commodity.

The other is the commodity inventory collection, which stores the test data 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 collection is equivalent to the item name in the order collection.

In this pattern design, how to write code if you want to query the inventory of the goods corresponding to the order table?

Obviously this requires two sets of Join.

The scene is simple, do not repeat, 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 result is 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

}

]

}

{

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

}

]

}

Analyze the query statements and results, and rebate $lookup definition. The above process can be described as follows:

Get the document processing one by one from the collection order. After getting a document, you will traverse the inventory collection (from: "inventory") of the Join according to the localField value to see if the foreignField value in the inventory collection document is equal to it. If equal, the eligible inventory document as a whole is embedded in the newly generated document of the aggregation framework, and the new key is uniformly named inventory_docs. Considering that the eligible documents are not unique, the corresponding Value of this Key is an array. Special care should be taken when the value of Key in the original collection is null or does not exist.

four。 Description

In the following instructions, for ease of description, the collection corresponding to from is defined as the collection by join; the table to be aggregated becomes the source table; and the Key definition comparison column corresponding to localField and foreignField is defined.

1. Due to the problems displayed by the client tool, the query results in the above example are automatically displayed as NumberInt ("") with Int type values. This NumberInt tag, please ignore, does not affect our functional testing.

two。 In this example, a total of three documents are output, and without re-aggregation ($match), the number of output documents is determined by the number of input documents (determined by order), not by the number of inventory documents in Join.

3. What needs to be emphasized here is the third document of the output. In the source database, the original document does not have a column to compare (that is, the item value does not exist, neither a null value nor an empty value), at this time compared with the Join set, if the column compared by the Join set also happens to be NUll or non-existent value, at this time, the judgment is equal, that is, the comparison column in the Join set will be absorbed as NUll or the value does not exist in the document.

4. Suppose the comparison column in the source table (order) is a value that does not exist in all documents in the table to be compared (inventory). What would the query result look like?

Order collection on the basis of existing data, and then insert into a test data, the order of the goods is Start, there is no such data in the inventory.

Db.orders.insert ({"_ id": 4, "item": "Start", "price": 2000, "quantity": 1})

The number of documents in the order collection has increased from the previous three to four.

Execute the query again

Db.orders.aggregate ([{$lookup: {from: "inventory", localField: "item", foreignField: "sku", as: "inventory_docs"}}])

View the results at this time

{

"_ id": NumberInt ("1")

"item": "almonds"

Price: NumberInt ("12")

Quantity: NumberInt ("2")

"inventory_docs": [

{

"_ id": NumberInt ("1")

"sku": "almonds"

"description": "product 1"

"instock": NumberInt

}

]

}

{

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

}

]

}

{

"_ id": NumberInt ("4")

"item": "Start"

"price": NumberInt ("2000")

Quantity: NumberInt ("1")

"inventory_docs": []

}

The query results have also changed from three to four. What is more special is the fourth document, whose new column is "inventory_docs": [], that is, the value is empty. So, at this point, the functionality implemented is very similar to the left join of a relational database.

So, can you filter out only the documents that have been added as empty?

That is, under the condition of querying and comparing columns, what about the documents that are only in set A, but not in set B? Just like the left join on a.key = b.key where b.key is null of the scale Join in the relational database.

The answer is yes.

In fact, let's go back to the aggregation framework, just aggregate it again, and $match is fine.

Adjust the executed statement and OK it.

Db.orders.aggregate ([{$lookup: {from: "inventory", localField: "item", foreignField: "sku", as: "inventory_docs"}}, {$match: {"inventory_docs": []}}])

The execution result is

{

"_ id": NumberInt ("4")

"item": "Start"

"price": NumberInt ("2000")

Quantity: NumberInt ("1")

"inventory_docs": []

}

You can see that there is only one document for the execution result. The meaning of this document is: there is this item in the order, but it is not in stock.

($look is just a stage of the aggregation framework, and you can embed commands in other aggregation pipes, such as $match.$group, before and after it. Note 5 below, which can also explain one or two)

5. The above comparison columns are all single Key/Value, if it is more complicated, if the comparison column is an array, how should we relate it?

Let's test it again next. Empty the data inserted by the previous collections order and inventory.

Insert the new data in this scenario, and insert the data into the order as follows:

Db.orders.insert ({"_ id": 1, "item": "MON1003", "price": 350, "quantity": 2, "specs": ["27 inch", "Retina display", "1920x1080"], "type": "Monitor"})

The value corresponding to specs is in array format.

The newly inserted data into the collection inventory is as follows:

Db.inventory.insert ({"_ id": 1, "sku": "MON1003", "type": "Monitor", "instock": 120," size ":" 27 inch "," resolution ":" 1920x1080 "}) db.inventory.insert ({" _ id ": 2," sku ":" MON1012 "," type ":" Monitor "," instock ": 85," size ":" 23 inch ") "resolution": "1280x800"}) db.inventory.insert ({"_ id": 3, "sku": "MON1031", "type": "Monitor", "instock": 60, "size": "23 inch", "display_type": "LED"})

The query statement is as follows:

Db.orders.aggregate ([{$unwind: "$specs"}, {$lookup: {from: "inventory", localField: "specs", foreignField: "size", as: "inventory_docs"}}, {$match: {"inventory_docs": {$ne: []}])

The results of the query are as follows:

{

"_ id": NumberInt ("1")

"item": "MON1003"

"price": NumberInt

Quantity: NumberInt ("2")

"specs": "27 inch"

"type": "Monitor"

"inventory_docs": [

{

"_ id": NumberInt ("1")

"sku": "MON1003"

"type": "Monitor"

"instock": NumberInt

"size": "27 inch"

"resolution": "1920x1080"

}

]

}

Look carefully, the data corresponding to the specs in the output document becomes a string type (the original collection is an array). What works so miraculously? Please look at the blackboard and focus your eyes on

{$unwind: "$specs"}

There is also a small problem, you can guess, if not in the query statement

{$match: {"inventory_docs": {$ne: []}

What will be the result? That is, the view statement is modified to:

Db.orders.aggregate ([{$unwind: "$specs"}, {$lookup: {from: "inventory", localField: "specs", foreignField: "size", as: "inventory_docs"}}])

Let's guess!

Let's guess!

Let's guess!

Hehe. The result at this time is:

Document 1

{

"_ id": NumberInt ("1")

"item": "MON1003"

"price": NumberInt

Quantity: NumberInt ("2")

"specs": "27 inch"

"type": "Monitor"

"inventory_docs": [

{

"_ id": NumberInt ("1")

"sku": "MON1003"

"type": "Monitor"

"instock": NumberInt

"size": "27 inch"

"resolution": "1920x1080"

}

]

}

Document 2

{

"_ id": NumberInt ("1")

"item": "MON1003"

"price": NumberInt

Quantity: NumberInt ("2")

"specs": "Retina display"

"type": "Monitor"

"inventory_docs": []

}

Document 3

{

"_ id": NumberInt ("1")

"item": "MON1003"

"price": NumberInt

Quantity: NumberInt ("2")

"specs": "1920x1080"

"type": "Monitor"

"inventory_docs": []

}

Have you worked out the correct result?

Thank you!

I hope the above explanation and demonstration will be helpful for you to learn $lookup.

Note: the above case data refer to the official website of MongoDB, and you can also visit the official website for more and more relevant knowledge.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.