sajad torkamani

MongoDB: Querying reference

27 July 2022 (Updated 27 July 2022)
On this page
Example inventory collection
Query flat documents
Specify EQUALS condition
Specify VALUE IN condition
Specify AND condition
Specify OR condition
Specify combination of AND and OR
Query nested documents
Field must exactly match a document
Nested field must exactly match value
Use query operator on nested field
Specify AND condition on multiple nested fields
Query arrays
Field must be exactly equal to an array
Field must contain all array elements
Field must contain a specific element
Field must contain an array element that matches a condition
Field must contain an array element that matches one of the conditions
Field must contain an array element that matches all of the conditions
Sources

Example inventory collection

Let’s suppose you have a inventory collection like this:

[
    { "item": "journal", "qty": 25, "size": { "h": 14, "w": 21, "uom": "cm" }, "status": "A" },
    { "item": "notebook", "qty": 50, "size": { "h": 8.5, "w": 11, "uom": "in" }, "status": "A" },
    { "item": "paper", "qty": 100, "size": { "h": 8.5, "w": 11, "uom": "in" }, "status": "D" },
    { "item": "planner", "qty": 75, "size": { "h": 22.85, "w": 30, "uom": "cm" }, "status": "D" },
    { "item": "postcard", "qty": 45, "size": { "h": 10, "w": 15.25, "uom": "cm" }, "status": "A" }
]

Let’s assume you’ll be using Mongo Compass to work with the collection. Most of the queries and writes should be similar even if you’re using MongoDB Shell or a library in another language.

Query flat documents

Specify EQUALS condition

{ "status": "D" }

SQL equivalent:

SELECT * FROM inventory WHERE status = "D"

Specify VALUE IN condition

{ status: { $in: [ "A", "D" ] } }

SQL equivalent:

SELECT * FROM inventory WHERE status in ("A", "D")

Specify AND condition

{ status: "A", qty: { $lt: 30 } }

SQL equivalent:

SELECT * FROM inventory WHERE status = "A" AND qty < 30

Specify OR condition

{ $or: [ { status: "A" }, { qty: { $lt: 30 } } ] }

SQL equivalent:

SELECT * FROM inventory WHERE status = "A" OR qty < 30

Specify combination of AND and OR

{ status: "A", $or: [ { qty: { $lt: 30 } }, { item: /^p/ } ] }

SQL equivalent:

SELECT * FROM inventory WHERE status = "A" AND ( qty < 30 OR item LIKE "p%")

Query nested documents

Field must exactly match a document 

{ size: { h: 14, w: 21, uom: "cm" } }

This will match only the documents where size is exactly as specified in the filter – all the fields must match and be in the same order.

Nested field must exactly match value

{ "size.uom": "in" }

Use query operator on nested field

{ "size.h": { $lt: 15 } }

Specify AND condition on multiple nested fields

{ "size.h": { $lt: 15 }, "size.uom": "in", status: "D" }

Query arrays

Field must be exactly equal to an array

{ tags: ["red", "blank"] }

This will return only documents where tags has exactly two elements and in the exact order specified.

Field must contain all array elements

{ tags: { $all: ["red", "blank"] } }

tags can contain other elements and the order can be different to what’s specified.

Field must contain a specific element

{ tags: "red" }

Happy as long as tags contains red. Doesn’t matter if it contains other elements.

Field must contain an array element that matches a condition

{ dim_cm: { $gt: 25 } }

This will match any document where dim_cm contains is an array that contains an element greater than 25.

Field must contain an array element that matches one of the conditions

This will match any document where dim_cm has at least one element that’s either greater than 15 or less than 20.

{ dim_cm: { $gt: 15, $lt: 20 } }

Field must contain an array element that matches all of the conditions

{ dim_cm: { $elemMatch: { $gt: 22, $lt: 30 } } }

This will match any document where dim_cm contains an element that meets both the conditions: greater than 22 and less than 30.

Sources

Tagged: MongoDB