MongoDB Query Language Overview
MQL, short for MongoDB Query Language, is MongoDB’s proprietary language designed for data retrieval. It empowers users to fetch documents that align with precise criteria, execute aggregations, make updates to documents, and perform document deletions. MongoDB’s decision to develop its own query language is a result of their commitment to tailoring querying functionality to their unique requirements and the diverse needs of their customers.
MongoDB Query Language (MQL)
The syntax of MQL is designed to be simple and intuitive. It allows users to specify conditions using operators such as $eq (equals), $ne (not equals), $gt (greater than), $lt (less than), and many others. Multiple bitwise operators such as $and, $or, and $not can also be used to create complex queries. MQL provides powerful aggregation capabilities to process and analyze data in MongoDB. MQL is the basis for aggregation pipelines which restructures data through a multi-step process detailed here. MongoDB specifically includes many functions for their unique indexing and geospatial analysis in MQL, greatly simplifying the querying process for these tools.
MongoDB Query Language Aggregation Framework
The aggregation framework, in the case of MongoDB, is a set of processes which transform data into a structure which can be analyzed. Aggregation relies on a function known as the aggregation pipeline. The aggregation pipeline defines a sequence of data processing stages that transform and manipulate documents in a collection. This is a multi-step process which filters, groups, sorts, and transforms data.
MQL: $match and $group
MQL: $project, $sort’ and $limit
Next are the ‘$project’, ‘$sort’, and ‘$limit’ stages. The ‘$project’ stage works specifically on modifying fields in the data. In this step analysts can create new fields, exclude existing ones, rename fields and much more. The ‘$sort’ stage changes the output by rearranging the order of the data. Commonly, analysts arrange data by a given variable, date for example, ascending or descending. The ‘$limit’ stage will then limit the number of data points which can speed up run times and alleviate some load on the computer.
The last stage is known as the ‘$lookup stage’. The ‘$lookup’ stage allows analysts to combine data from different collections based on matching fields. This is a somewhat optional stage as it is only useful when trying to join across collections.
After the aggregation is executed, MongoDB will return the data in an array of documents. Now the data is ready to be analyzed and converted into a visualization.
Modifying Documents in MQL
Operators in MQL serve to add to a document or delete portions of said document. Depending on whether the user needs to add a new element or create a new field entirely, different operators must be used.
The “$set” operator modifies an existing field or adds a new field to a document. Rather than reopening a document and finding the exact field that needs to be changed, users can use the $set operator in the command line to change all instances of a given variable to the new value. Here’s an example of the $set operator.
In this example MongoDB first identifies the document—in this case it uses an ID—before setting the new values. Since version 5 of MongoDB “title” will be set to the new value before MongoDB modifies “author”. In some earlier versions of MongoDB, however, the platform will use a different system of prioritization.
The “$unset” operator is the exact opposite of the “$set” operator. Rather than assigning a new value to a field, the $unset operator will give an empty value to a field thereby deleting it. Here’s an example of the $unset operator,
Similar to the example above, MongoDB identifies the document with the “ObjectId”. Afterwards the query sets both “author” and “publicationYear” to an empty string, removing any previous values they had.
The “$inc” operator is MongoDB’s abbreviation of “increment”, an operator designed to increase or decrease the value of a field. $inc can be used with either doubles or integers, in simple terms this means that decimal values can be passed through $inc without an issue. Here’s an example of the $inc operator with an integer.
In this function, the value of “quantity” is decreased by two. For example, if the value of “quantity” was previously 10, now it would be 8. Multiple values can also be passed through the $inc operator if desired.
The “$push” operator can append a new element to a field or modify an existing element. Elements are similar to values in a list and are held in a specific field.
In this document for instance, “Thriller” and “Mystery” are elements within the “genres” field. Let’s say a user wanted to include a different book by “John Doe” with a new, third genre. A combination of the $push and $set operators could produce that result.
The “title” field now equals “The Tales of Yorkshire” and a new genre and rating have been added to accurately represent the book.
The “$pull” operator is the opposite of the “$push” operator. It removes elements from a field or, if there is only one element left in a field, deletes entire fields from a document. In the real-world use case below, a combination of the four operators above will be used on a sample dataset to showcase their capabilities.
In this query into the sample dataset, Knowi’s “Mongo Query” tool first executes the .aggregate() function (not shown above), extracting a few key variables from the dataset and then limiting the results to 10 items. Subsequently we use all four operators discussed in the “Operators” section to modify certain fields and pull unnecessary data. The donut chart below which shows the bed number grouped by the “name” field.
(Knowi, Click here to view)
In MongoDB there are four modifiers, $each, $position, $slice, and $sort. These all modify the functionality of the “$push” operator to help with organization or efficiency. While these modifiers are not required when performing an update on a document, they play a key role in simplifying the updating process.
The “$each” modifier can be used with “$push” or “$addToSet” to select multiple items to append to a field. Rather than copying a previous $push command and complicating the query, the $each modifier allows users to seamlessly execute multiple $push commands.
The “$position” modifier allows users to specify where in an array an item should be appended. In fields where proper ordering is crucial, the “$position” modifier is a simple solution to any issues with item placement.
The “$slice” modifier performs a similar function to the “$limit” operator in the aggregation pipeline; it reduces the total documents to a finite number. The “$slice” operator allows users to create a new limit in the updated collection which will be executed alongside any other updates.
The “$sort” modifier allows users to rearrange documents according to a given metric/field. For example, if a user wanted to arrange a list of books in order of publish date, the $sort modifier would allow them to do so.
The “$bit” operator is unique because it modifies the individual bits of a field. $bit uses bitwise functions such as “AND”, “OR”, and “XOR” to create conditionals for which bits it chooses to modify. $bit is a highly technical operator with limited uses in most day-to-day queries.
Real World Case
(Knowi, Click here to view)
For this visualization, both bathroom and bed number were included in a stacked column format. I sorted the result by “Desc bathrooms” meaning that the first value has the highest bathroom number and the last value has the lowest. Like in the previous real-world example I narrowed down the results to 10 locations.
Knowi is the best visualization tool for MongoDB analytics as it can query using MQL natively without any additional tools or connectors. Schedule a demo call to see how.