MongoDB: Querying reference

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

• Query documents – MongoDB docs
• Query on Embedded / Nested documents – MongoDB docs
• Query an Array – MongoDB docs