Shulou(Shulou.com)05/31 Report--

This article shares with you about the methods of using JOIN operations in MongoDB. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preface

MongoDB is a kind of document-oriented non-relational database (an implementation of NoSql database) written by C++ language, and it is also a data storage product between relational database and non-relational database. It is well known that one of the biggest differences between SQL and NoSQL is that it does not support JOIN. In traditional databases, the SQL JOIN clause allows you to use ordinary fields and each row of data in a combined table in two or more tables. For example, if you have tables books and publishers, you can write commands like this:

SELECT book.title, publisher.nameFROM bookLEFT JOIN book.publisher_id ON publisher.id

In other words, the publisher_id field in the book table refers to the id dictionary in the publishers table. These are common examples: there can be thousands of books per publisher, and if you want to update publisher information, we only need to change one record. The redundancy of the data is small, because we do not need to update his publisher information repeatedly for each book, and this technology is basically regarded as a normalization. SQL database provides a number of column specifications and constraints to ensure data relevance.

NoSQL = = No JOIN?

That's not always the case.

Document-oriented databases, such as MongoDB, are designed to store unstructured data that is ideally unrelated to each other in a data set, and if a piece of data contains two or more times, the data is repeated. Because in most cases we still need data association, only a few cases do not need related data, it seems that these features of NoSQL seem to be disappointing. Fortunately, MongoDB 3.2introduces a new $lookup operation that provides an operation similar to LEFT OUTER JOIN under two or more conditions.

MongoDB Aggregation

Lookup is only allowed in aggregation operations. Think of it as a conduit operation: query, filter, and combine results. The output of one operation is used as the input of the next. Aggregation is more difficult to understand than simple query operations, and these operations are usually slow, but they are very efficient. Aggregation can use a good example to explain, suppose we use user data sets to create a social platform that stores no user's information in each separate document, for example:

{"_ id": ObjectID ("45b83bda421238c76f5c1969"), "name": "User One", "email:" userone@email.com "," country ":" UK "," dob ": ISODate (" 1999-09-13T00:00:00.000Z ")}

We can add enough users to the user collection, but each MongoDB document must have a value of the _ id field, which is like the key in SQL and is automatically added to the document when we don't explicitly specify _ id. Our social networking site now needs a post collection that stores user comments, this document stores plain text, time, score, and a player reference written to the user_id field.

{"_ id": ObjectID ("17c9812acff9ac0bba018cc1"), "user_id": ObjectID ("45b83bda421238c76f5c1969"), "date: ISODate (" 2016-09-05T03:05:00.123Z ")," text ":" My life story so far "," rating ":" important "}

We now want to display 20 pieces of data with recent important comments from all users and sorted by time. Each returned document should contain the text of the comment, the time the comment was posted, and the name and country of the relevant user.

Aggregate queries for MongoDB databases are passed through an array of pipeline operations in which each operation is determined sequentially. First, we need to extract all the documents from all the post collections, which are filtered using $match memory accurate rating.

{"$match": {"rating": "important"}}

We now need to sort the filtered documents by time, using the $sort operation.

{"$sort": {"date":-1}}

Because we want to return only 20 pieces of data, we can use $limit to limit the number of documents we need to process.

{"$limit": 20}

We now use the $lookup operation to connect data from the user collection, which requires an object with four parameters:

1. LocalField: the search field in the input document

2. From: collections that need to be connected

3. ForeignField: the field to be found in the from collection

4. As: the name of the output field

So here's what we do:

{"$lookup": {"localField": "user_id", "from": "user", "foreignField": "_ id", "as": "userinfo"}}

In our output, we will create a new field called userinfo, which is an array in which each element is a match in the user collection.

"userinfo": [{"name": "User One",...}]

We have an one-to-one relationship between post.user_id and user._id because there is only one user per post. So our userinfo array will contain only one element, and we can say we use the $unwind operation to deconstruct it and insert it into a self-document.

{"$unwind": "$userinfo"}

The current output will be transformed into a more commonly used structure:

Userinfo: {"name": "User One", "email:" userone@email.com ",...}

Finally, we can use the $project operation in the pipeline to return the comment information, the time of the comment, the user name of the comment, the country, etc.

{"$project": {"text": 1, "date": 1, "userinfo.name": 1, "userinfo.country": 1}}

Merge all the above operations

Our final aggregate query matches the comments, sorts in order, limits the latest 20 pieces of information, joins the user's data, flattens the user array, and finally returns only the necessary data we need. The general command is as follows:

Db.post.aggregate ([{"$match": {"rating": "important"}}, {"$sort": {"date":-1}}, {"$limit": 20}, {"$lookup": {"localField": "user_id", "from": "user", "foreignField": "_ id", "as": "userinfo"}, {"$unwind": "$userinfo"}) {"$project": {"text": 1, "date": 1, "userinfo.name": 1, "userinfo.country": 1}])

The result is a collection of twenty documents, such as:

[{"text": "The latest post", "date: ISODate (" 2016-09-27T00:00:00.000Z ")," userinfo ": {" name ":" User One "," country ":" UK "}}, {" text ":" Another post "," date: ISODate ("2016-09-26T00:00:00.000Z"), "userinfo": {"name": "User One" "country": "UK"}.]

MongoDB's $lookup is easy to use and efficient, but the basic example above is just a combined collection query. It is not a replacement for the more efficient JOIN clause in SQL. MongoDB also provides some restrictions, and if the user collection is deleted, the post document will still be retained.

Ideally, this $lookup operation should not be used often, and if you need to use it often, then you are using the wrong data store (database): if you have associated data, you should use an associated database (SQL).

In other words, $lookup is a new addition to MongoDB 3.2, which solves some disappointing problems when using small related data queries in Nosql databases.

Thank you for reading! This is the end of this article on "how to use JOIN in MongoDB". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.