Querying arrays and embedded documents
How a condition on an array field matches any element, why that surprises people, and how $elemMatch, $all and $size give you precise control.
- Predict how filters match array fields
- Use $elemMatch to apply several conditions to the same element
- Use $all, $size and positional dot notation
- Query arrays of embedded documents
conceptThe concept
A condition on an array field matches if any element satisfies it: { tags: "b2b" } matches tags: ["loyalty", "b2b"]. That applies to arrays of embedded documents too: { "items.category": "books" } matches an order with at least one book.
The trap: with several conditions on the same array, each condition can be satisfied by a different element. { "items.category": "books", "items.qty": { $gte: 3 } } matches an order with one book and a separate line of 3 mugs. To require one element that satisfies both, use $elemMatch:
{ items: { $elemMatch: { category: "books", qty: { $gte: 3 } } } }
$all: ["a", "b"]: the array contains every listed value, in any order.$size: 2: exactly 2 elements (no ranges; store a count field if you need$gt)."items.0.price": a specific position.{ tags: ["a", "b"] }: the whole array equals exactly["a", "b"]in that order.
whyWhy it matters
Arrays are central to the document model (order items, tags, roles, addresses). Getting array semantics wrong produces queries that return extra documents, which is worse than an error because nobody notices.
Use caseReal-world scenario
"Find orders containing a single line of 3 or more electronics items" is an $elemMatch query. "Find customers tagged both b2b and loyalty" is $all. "Find customers with no tags" is { tags: { $size: 0 } }.
Try itExamples
Run and edit these in the interactive lesson.
db.customers.countDocuments({ tags: "b2b" })
db.orders.countDocuments({ "items.category": "books" })db.orders.countDocuments({ "items.category": "electronics", "items.qty": { $gte: 3 } })
db.orders.countDocuments({ items: { $elemMatch: { category: "electronics", qty: { $gte: 3 } } } })db.customers.find({ tags: { $all: ["b2b", "loyalty"] } }, { name: 1, tags: 1 }).limit(3)
db.customers.countDocuments({ tags: { $size: 0 } })
db.orders.countDocuments({ "items.3": { $exists: true } })
db.orders.find({ "items.0.price": { $gt: 4000 } }, { orderNo: 1, "items.name": 1 }).limit(3)db.lab_scores.drop()
db.lab_scores.insertMany([{ s: [2, 9] }, { s: [5] }, { s: [11, 1] }])
db.lab_scores.find({ s: { $gt: 4, $lt: 6 } }, { _id: 0 })
db.lab_scores.find({ s: { $elemMatch: { $gt: 4, $lt: 6 } } }, { _id: 0 })internalsUnder the hood
An index on an array field is multikey: it stores one index key per array element. That is why any-element matching is efficient. A compound index can include at most one array field per document, because indexing the cartesian product of two arrays could explode. For $elemMatch the planner can combine bounds on several fields of the same embedded element when they share an array path.
Avoid / preferDo this, not that
db.orders.find({ "items.category": "books", "items.qty": { $gte: 2 } })db.orders.find({ items: { $elemMatch: { category: "books", qty: { $gte: 2 } } } })Without $elemMatch each condition may be met by a different line item: one book with qty 1 plus any other item with qty 2 matches. $elemMatch pins both to the same element.
db.products.find({ tags: ["sale", "gift"] })db.products.find({ tags: { $all: ["sale", "gift"] } })An array literal matches only that exact array, in that order. $all asks for both values in any order.
PitfallsCommon mistakes
- Using two separate conditions on an array of sub-documents when they must hold for the same element.
- Trying
$size: { $gt: 3 }; it accepts only an exact number. Use"arr.3": { $exists: true }or a stored count. - Matching arrays by full value (
{ tags: ["a", "b"] }) when order or extra elements differ.
LimitsWhen not to use it
Arrays that grow to thousands of elements make multikey indexes huge (one key per element) and every update rewrites a large document. A compound index can also contain at most one array field per document. When an array is unbounded, give its elements their own collection.
SecuritySecurity notes
Cap the size of $in and $all lists that come from requests. A 100,000-element $in is a cheap way for a client to make the server work very hard.
ChallengeTry it yourself
Find three orders with at least one electronics line whose qty is 2 or more, returning orderNo and only the matching lines.
Show one solution
db.orders.aggregate([
{ $match: { items: { $elemMatch: { category: "electronics", qty: { $gte: 2 } } } } },
{ $project: { orderNo: 1, items: { $filter: {
input: "$items",
cond: { $and: [{ $eq: ["$$this.category", "electronics"] }, { $gte: ["$$this.qty", 2] }] }
} } } },
{ $limit: 3 }
])InterviewQuestions you should be able to answer
When do you need $elemMatch?
When two or more conditions must be satisfied by the same array element, either on fields of embedded documents or a range on scalar arrays. Without it, each condition can be satisfied by a different element.
What is a multikey index and what are its limitations?
An index on a field that holds an array in at least one document: it stores a key per element. Limitations: at most one array field per document within a compound index, it cannot be a shard key, multikey indexes cannot cover queries on the array field, and bounds on two predicates of the same array field may not be intersected unless you use $elemMatch.
QuizCheck your understanding
4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.