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
The first two stages are known as the ‘$match’ and ‘$group’ stages. The reason why these are not simply called the match stage and the group stage is because in Javascript the ‘$’ sign signifies an operation. The ‘$match’ stage is the initial filtering of the data which selects any data that match the variable connected to the operator. This step is useful if the analyst only wants to look at a specific area of the data such as mammals in a group of animals. The ‘$group’ stage uses specific keys to continue parsing through the data. At this stage, analysts can calculate important statistics such as the average or sum of the data.
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.
MQL: $lookup
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
In addition to querying and aggregation, MQL supports various update operations to modify documents in the database. Operators like $set, $unset, $inc, $push, and $pull update specific fields or arrays within documents. These operators can be enhanced with various modifiers and MQL’s bitwise capability. MQL is typically used in conjunction with programming languages like JavaScript, Python, or any other language that has a MongoDB driver. These drivers provide APIs—interfaces to connect software programs—to interact with the MongoDB database and execute MQL queries.
Operators
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.
MQL: $set
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.
MQL: $unset
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.
MQL: $inc
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.
MQL: $push
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.
MQL: $pull
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.
Real-World Case
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)
Modifiers
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.
MQL: $each
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.
MQL: $position
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.
MQL: $slice
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.
MQL: $sort
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.
MQL: $bit
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.
Conclusion
MQL has myriad capabilities for data aggregation and modification. It is uniquely designed to work with MongoDB’s JSON format to extract data in the user’s desired format. MQL’s syntax mirrors that of JavaScript making it an easily usable tool for JS and other front end programmers. With the use of MQL, raw data can be transformed into data which can easily be inputted into any visualization tools. To learn more about MongoDB be sure to check out https://www.mongodb.com, Mongo’s official website. Blogs such as this(https://www.knowi.com/blog/choosing-the-best-mongodb-visualization-and-reporting-tool-for-your-team-updated-2023/) are also great tools for learning more about how MongoDB’s capabilities can effectively be used for businesses today.
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.
