MongoDB Query Language

Whereas relational databases are queried using Structured Query Language (SQL), MongoDB can be queried using MongoDB Query Language (MQL). It's the interface by which clients can interact with the MongoDB server. Developers and database administrators can write MQL commands interactively on the MongoDB Shell. For client applications, drivers are available in popular programming languages to execute MQL commands.

MQL supports CRUD operations. Results can be sorted, grouped, filtered, and counted via aggregation pipelines. Special operations such as text search and geospatial queries are possible. Multi-document transactions are supported.

This article covers MongoDB Shell commands except aggregations. Commands in Node.js are similar. For commands in other languages, refer to the documentation of language-specific drivers. This article doesn't cover administrative commands that relate to users, backups, replica sets, sharded clusters, etc.

Discussion

  • What are the basic commands to get started with MQL?
    Help of mongosh v1.1.0 shell commands. Source: Devopedia 2021.
    Help of mongosh v1.1.0 shell commands. Source: Devopedia 2021.

    Start by launching the MongoDB Shell mongosh. Type help() to see a list of shell commands. Here are some useful commands: show databases, show collections (of current database), use <db-name> (to switch to a database), version() (for shell version), quit and exit.

    The Database class has many methods. View them with the command db.help(). Here are some useful methods: getMongo() (get current connection), getCollectionNames(), createCollection(), dropDatabase(), and stats().

    The Collection class has many methods. View them with the command db.coll.help(). Here are some useful methods: insert(), find(), update(), deleteOne(), aggregate(), count(), distinct(), remove(), createIndex(), dropIndex(), and stats().

    The command syntax is same as JavaScript. Commands can be saved into a JavaScript file and executed using the load() command.

  • Could you introduce the commands for CRUD operations in MongoDB?
    Examples of CRUD operations in MongoDB. Source: Adapted from MongoDB Docs 2021c.
    Examples of CRUD operations in MongoDB. Source: Adapted from MongoDB Docs 2021c.

    Database operations are conveniently referred to as CRUD (Create, Read, Update, Delete). MongoDB supports CRUD with the following methods of Collection class:

    • Create: Add documents to a collection. Methods include insertOne() and insertMany(). If the collection doesn't exist, it will be created. Unique _id field is automatically added to each document if not specified in the method calls.
    • Read: Retrieve documents from a collection. Main method is find(), called with query criteria (how to match documents) and projection (what fields to retrieve).
    • Update: Modify existing documents of a collection. Methods include updateOne(), updateMany() and replaceOne(). Method calls include query criteria and what to update.
    • Delete: Remove documents from a collection. Methods include deleteOne() (first matching document is deleted) and deleteMany(), called with query criteria.

    It's also possible to insert new documents with the option upsert: true. Some methods that do this are update(), updateOne(), updateMany(), findAndModify(), findOneAndUpdate(), findOneAndReplace(), and bulkWrite().

  • How do I find documents in a MongoDB collection?

    The main method is find(query, projection). Query parameter specifies the filter. If omitted, all documents returned. Projection parameter specifies what fields to retrieve. If omitted, all fields are retrieved. The method returns a cursor to the documents. Method findOne(query, projection) returns the first matching document, not a cursor.

    Here are some sample commands on a collection named people:

    • db.people.find(): Retrieve all fields of all documents.
    • db.people.find({status: "A", {age: {$gt: 25}}}): Retrieve all fields of documents of status 'A' and age exceeding 25. Multiple query fields are combined with $and operator by default.
    • db.people.find({}, {user_id: 1, status: 1, _id: 0}): Retrieve only two fields of all documents.
    • db.people.find({$or: [{status: "A"}, {age: {$gt: 25, $lte: 50}}] }): A complex filter specifying status 'A' or age within a range.
    • db.people.find({status: {$ne: "A"}).sort({age: 1}).skip(10).limit(5): Filter, sort, skip and limit the results.

    Comparison operators include $eq, $gt, $gte, $in, $lt, $lt, $lte, $ne, and $nin. Logical operators include $and, $not, $nor and $or.

    Since MongoDB is schema-less, documents may include only some fields or fields of differing types. Operators $exists and $type check if field exists and if the type matches.

  • How do I write queries when arrays are involved?

    Consider an inventory collection with two array fields tags and dim_cm. Here are some example queries:

    • db.inventory.find({ tags: ["red", "blank"] }): Documents with both 'red' and 'blank' tags. Order of tags matters.
    • db.inventory.find({ tags: { $all: ["red", "blank"] } }): As above, but order of tags doesn't matter.
    • db.inventory.find({ tags: "red" }): Documents tagged 'red'.
    • db.inventory.find({ dim_cm: { $gt: 15, $lt: 20 } }): Documents where both conditions are satisfied but not necessarily by the same array item.
    • db.inventory.find({ dim_cm: { $elemMatch: { $gt: 15, $lt: 20 } } }): Documents where both conditions are satisfied by the same array item.
    • db.inventory.find({ "dim_cm.1": { $gt: 25 } }): Second item of array satisfies the condition.
    • db.inventory.find({ tags: { $size: 3 } }): Documents with exactly three tags.

    Operators $in and $nin check presence or absence in an array. For example, db.inventory.find({dim_cm: {$in: [15, 18, 20]}}) finds documents with specific dimensions.

  • How do I find documents based on values in nested documents?

    It's possible to find documents based on the content of inner documents, either with a full or a partial match. Assume documents with the field size that itself is a document with fields h, w and uom. Here are some examples:

    • db.inventory.find({ size: { h: 14, w: 21, uom: "cm" } }): Exact document match. Field order matters.
    • db.inventory.find({ "size.h": { $lt: 15 } }): Nested field query.

    Assume documents with the field instock that's an array of documents. Here are some examples:

    • db.inventory.find({ "instock": { warehouse: "A", qty: 5 } }): At least one item in array matches the query. Field order matters.
    • db.inventory.find({ 'instock.qty': { $lte: 20 } }): Nested field query. At least one item in array matches the query.
    • db.inventory.find({ 'instock.0.qty': { $lte: 20 } }): Check the first item in array.
    • db.inventory.find({ "instock.qty": 5, "instock.warehouse": "A" }): Two nested fields but they need not match the same item within the array.
    • db.inventory.find({ "instock": { $elemMatch: { qty: 5, warehouse: "A" } } }): Two nested fields and both should match at least one item within the array.
  • Could you share more details about MongoDB projection?

    The projection parameter is used in find() and findOne() methods. It controls what fields are sent by MongoDB to the application. It contains field-value pairs. Values can be boolean (1 or true, 0 or false), array, meta expression or aggregation expression.

    The _id field is always included in returned documents unless explicitly suppressed with _id: 0.

    Here are some examples of projections:

    • db.inventory.find({status: "A"}, {item: 1, _id: 0}): Retrieve only item. Suppress _id.
    • db.inventory.find({}, {status: 0, instock: 0}): Retrieve all fields except status and instock.
    • db.inventory.find({}, {"size.uom": 1}): Given size is a nested document, retrieve _id and nested field size.uom. An alternative form is db.inventory.find({}, {size: {uom: 1}}).
    • db.inventory.find({}, {_id: 0, "instock.qty": 1}): Given instock is an array of documents, retrieve only the instock.qty field.
    • db.inventory.find({}, {instock: {$slice: -2}}): Retrieve only the last two items of instock and all other fields of inventory.
    • db.inventory.find({}, {instock: {$slice: [3, 5]}}): Retrieve five items after skipping first three items of instock. Include all other fields of inventory since no explicit inclusion is specified.
    • db.inventory.find({}, {total: {$sum: "$instock.qty"}}): Using aggregation expression in projection, get total stock quantity in each document.
  • What are cursor methods in MongoDB?

    Cursor methods modify the execution of the underlying query. Since the collection method find() returns a cursor, we can list all cursor methods on the shell by calling db.coll.find().help().

    Here are a few examples, some showing how to chain method calls:

    • db.products.find().count(): Return number of documents rather than the documents themselves.
    • db.products.find().limit(2): Limit results to first two documents.
    • db.products.find().min({price: 2}).max({price: 5}).hint({price: 1}): Query only documents within the specified index range. Index {price: 1} must exist.
    • db.products.find().pretty(): Show results in a user-friendly format.
    • db.products.find().sort({price: 1, qty: -1}): Sort by price and then reverse sort by quantity.
    • db.people.find().skip(3).limit(5): Skip first three documents and show only the next five.
  • What are the update operators in MongoDB?
    Some examples of update operators in MongoDB. Source: Adapted from MongoDB Docs.
    Some examples of update operators in MongoDB. Source: Adapted from MongoDB Docs.

    There are three groups of update operators:

    • Field Update: Operators include $currentDate (Date or Timestamp), $inc (increment), $min (updates only if it's less than current field value), $max (updates only if it exceeds current field value), $mul, $rename, $set, $setOnInsert (only if update results in an insert), and $unset.
    • Array Update: Operators include $, $[], $[<identifier>], $addToSet, $pop (-1 to remove first item, 1 to remove last item), $pull (removes items based on a query condition), $push (append to array), and $pullAll (removes items based on a list of values). Operator modifiers include $each, $position, $slice and $sort.
    • Bitwise Update: The only operator is $bit. It supports AND, OR and XOR updates of integers.
  • As a SQL developer, how do I get started with MongoDB Query Language?
    A few SQL commands and their MQL equivalents. Source: Adapted from MongoDB Docs.
    A few SQL commands and their MQL equivalents. Source: Adapted from MongoDB Docs.

    SQL's (tables, rows, columns) map to MongoDB's (collections, documents, fields). SQL's GROUP BY aggregations are implemented via aggregation pipelines in MongoDB. Many other features such as primary keys and transactions are equivalent though not the same.

    In SQL, we can drop or add columns to a table. Since MongoDB is schema-less, these operations are not important. However, it's possible to add or drop fields using the method updateMany().

    SQL developers can refer to these useful guides:

Milestones

Feb
2009

MongoDB 1.0 is released. By August, this version becomes generally available for production environments.

Mar
2010

MongoDB 1.4 is released. Among the improvements are $all with regex, $not, $ operator for updating arrays, $addToSet, $unset, $pull with object matching, and $set with array indexes.

Aug
2012

MongoDB 2.2 is released. Projection operator $elemMatch is introduced. It returns only the first matching element in an array. For bulk inserts, it's now possible to pass an array of documents to insert() in the shell.

Mar
2015

MongoDB 3.0 is released. This includes a new query introspection system towards query planning and execution. Command explain and methods cursor.method() and db.collection.explain() are relevant here.

Dec
2015

MongoDB 3.2 is released. Query operators to test bit values are introduced: $bitsAllSet, $bitsAllClear, $bitsAnySet, $bitsAnyClear. Many CRUD methods on Collection class are added to correspond to drivers' APIs: bulkWrite(), deleteMany(), findOneAndUpdate(), insertOne(), and many more. Query modifiers are deprecated in the shell. Instead, cursor methods should be used.

Nov
2016

MongoDB 3.4 is released. Type decimal for 128-bit decimal is introduced. To support language-specific rules for string comparison, collation is introduced.

Nov
2017

MongoDB 3.6 is released. With arrayFilters parameter, we can selectively update elements of an array field. Positional operators $[] and $[<identifier>] allow multi-element array updates. For $push operator, $position modifier can be negative to indicate position from end of an array. Deprecated $pushAll operator is removed. New query operators include $jsonSchema and $expr. MongoDB shell now supports sessions.

Aug
2019

MongoDB 4.2 is released. We can now use the aggregation pipeline for updates. Wildcard indexes support queries against fields whose names are unknown or arbitrary. Some commands (group, eval, copydb, etc.) are removed.

Jul
2020

MongoDB 4.4 is released. Projections in find() and findAndModify() are made consistent with $project aggregation stage. Method sort() uses the same sorting algorithm as $sort aggregation stage. Compound hashed indexes and hidden indexes are introduced.

Jul
2021

MongoDB 5.0 is released. It provides better support for field names that are $ prefixed or include . characters.

References

  1. Ali, Asad. 2020. "48 MongoDB Commands and Queries to Know as Developer and DBA." Geekflare, July 4. Accessed 2021-10-14.
  2. Beugnet, Maxime. 2020. "MongoDB Cheat Sheet." MongoDB, September 30. Accessed 2021-10-15.
  3. Done, Paul. 2021. "History Of MongoDB Aggregations." Section 1.2 in: Practical MongoDB Aggregations, v3.00, MongoDB, Inc. Accessed 2021-10-10.
  4. MongoDB. 2009. "1.0 GA Released." Blog, MongoDB, August 27. Accessed 2021-10-16.
  5. MongoDB Docs. 2010. "Release Notes for MongoDB 1.4." August. Accessed 2021-10-16.
  6. MongoDB Docs. 2012. "Release Notes for MongoDB 2.2." August. Accessed 2021-10-16.
  7. MongoDB Docs. 2015a. "Release Notes for MongoDB 3.0." March 3. Accessed 2021-10-16.
  8. MongoDB Docs. 2015b. "Release Notes for MongoDB 3.2." December 8. Accessed 2021-10-16.
  9. MongoDB Docs. 2016. "Release Notes for MongoDB 3.4." November 29. Accessed 2021-10-16.
  10. MongoDB Docs. 2017. "Release Notes for MongoDB 3.6." November. Accessed 2021-10-16.
  11. MongoDB Docs. 2019. "Release Notes for MongoDB 4.2." August. Accessed 2021-10-16.
  12. MongoDB Docs. 2020. "Release Notes for MongoDB 4.4." June. Accessed 2021-10-16.
  13. MongoDB Docs. 2021a. "Write Scripts for the mongo Shell." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  14. MongoDB Docs. 2021b. "mongosh Methods." Documentation, MongoDB. Accessed 2021-10-14.
  15. MongoDB Docs. 2021c. "MongoDB CRUD Operations." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  16. MongoDB Docs. 2021d. "Insert Documents." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  17. MongoDB Docs. 2021e. "Insert Methods." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  18. MongoDB Docs. 2021f. "Delete Documents." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  19. MongoDB Docs. 2021g. "SQL to MongoDB Mapping Chart." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  20. MongoDB Docs. 2021h. "SQL to Aggregation Mapping Chart." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  21. MongoDB Docs. 2021i. "db.collection.find()." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  22. MongoDB Docs. 2021j. "Query and Projection Operators." Documentation, MongoDB 5.0. Accessed 2021-10-15.
  23. MongoDB Docs. 2021k. "Query Documents." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  24. MongoDB Docs. 2021l. "Query an Array." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  25. MongoDB Docs. 2021m. "Project Fields to Return from Query." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  26. MongoDB Docs. 2021n. "Query on Embedded/Nested Documents." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  27. MongoDB Docs. 2021o. "Query an Array of Embedded Documents." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  28. MongoDB Docs. 2021p. "db.collection.findOne()." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  29. MongoDB Docs. 2021q. "$slice (projection)." Documentation, MongoDB 5.0. Accessed 2021-10-15.
  30. MongoDB Docs. 2021r. "Cursor Methods." Documentation, MongoDB 5.0. Accessed 2021-10-15.
  31. MongoDB Docs. 2021s. "Field Update Operators." Documentation, MongoDB 5.0. Accessed 2021-10-16.
  32. MongoDB Docs. 2021t. "Array Update Operators." Documentation, MongoDB 5.0. Accessed 2021-10-16.
  33. MongoDB Docs. 2021u. "Bitwise Update Operator." Documentation, MongoDB 5.0. Accessed 2021-10-16.
  34. MongoDB Docs. 2021v. "Query Modifiers." Documentation, MongoDB 5.0. Accessed 2021-10-16.

Further Reading

  1. MongoDB Docs. 2021g. "SQL to MongoDB Mapping Chart." Documentation, MongoDB 5.0. Accessed 2021-10-14.
  2. MongoDB YouTube. 2020. "MongoDB Query Language: What's New." MongoDB, on YouTube, June 10. Accessed 2021-10-14.
  3. Beugnet, Maxime. 2020. "MongoDB Cheat Sheet." MongoDB, September 30. Accessed 2021-10-15.
  4. Ali, Asad. 2020. "48 MongoDB Commands and Queries to Know as Developer and DBA." Geekflare, July 4. Accessed 2021-10-14.

Article Stats

Author-wise Stats for Article Edits

Author
No. of Edits
No. of Chats
DevCoins
5
0
1285
1883
Words
0
Likes
25
Hits

Cite As

Devopedia. 2021. "MongoDB Query Language." Version 5, October 16. Accessed 2021-10-16. https://devopedia.org/mongodb-query-language
Contributed by
1 author


Last updated on
2021-10-16 13:59:28
  • MongoDB Aggregation
  • MongoDB Administration
  • MongoDB Query Optimization
  • SQL Injection
  • SQL Query Optimization
  • Database Normalization