$expr and Computed Predicates: Comparing Fields and Reusing Aggregation Expressions

Learning outcomes

AtlasMart's inventory service needs questions that cannot be written as “field compared with constant”: “reserved stock exceeds on-hand stock,” “available stock is below this product's own reorder point,” and “discounted price is lower than list price after handling missing values.” These are computed predicates. MongoDB's $expr lets a find or $match use aggregation expressions so the comparison stays on the server.

1. $expr turns an aggregation expression into a query predicate

$expr has the shape {$expr:{<expression>}}. The inner expression must resolve to a truthy/falsey result for each candidate document. Field paths inside expressions are strings beginning with $, such as "$reserved". This is different from a normal query predicate where the field name itself is the key.

db.inventory_query.drop()db.inventory_query.insertMany([ {_id:"sku-a",status:"active",onHand:10,reserved:4,reorderPoint:3,listPriceCents:15000,promoPriceCents:12900}, {_id:"sku-b",status:"active",onHand:5,reserved:7,reorderPoint:2,listPriceCents:9000,promoPriceCents:9500}, {_id:"sku-c",status:"active",onHand:8,reserved:6,reorderPoint:4,listPriceCents:12000}, {_id:"sku-d",status:"retired",onHand:100,reserved:0,reorderPoint:10,listPriceCents:5000,promoPriceCents:4000}])printjson({ overReserved: db.inventory_query.find({$expr:{$gt:["$reserved","$onHand"]}},{_id:1,onHand:1,reserved:1}).sort({_id:1}).toArray(), promoBelowList: db.inventory_query.find({$expr:{$lt:["$promoPriceCents","$listPriceCents"]}},{_id:1,listPriceCents:1,promoPriceCents:1}).sort({_id:1}).toArray()})

sku-b is over-reserved because its own two fields violate the invariant. The predicate does not need an application loop. Note that a missing promoPriceCents introduces missing/null expression behavior; do not assume every arithmetic or comparison expression automatically means what your business wants for absent data.

2. Build the business expression once and reuse it

Available stock is onHand - reserved. AtlasMart wants active products where available stock falls below each product's own reorderPoint. The reusable expression can be placed in a find filter and then reused in an aggregation pipeline that also projects the computed value for diagnosis.

const available={$subtract:["$onHand","$reserved"]};const needsReorder={$lt:[available,"$reorderPoint"]};printjson(db.inventory_query.find({status:"active",$expr:needsReorder},{_id:1,onHand:1,reserved:1,reorderPoint:1}).sort({_id:1}).toArray());printjson(db.inventory_query.aggregate([ {$match:{status:"active",$expr:needsReorder}}, {$project:{_id:1,onHand:1,reserved:1,reorderPoint:1,available}}, {$sort:{_id:1}}]).toArray());

Keeping the ordinary status:"active" predicate outside $expr is intentional. It is simpler, easier to read, and directly indexable. Use expressions for the part that truly requires per-document computation.

3. Missing values require explicit business semantics, not accidental coercion

If old AtlasMart records omit reserved, does that mean zero reserved, unknown reserved, or invalid data? Only the domain can answer. If the contract explicitly says “missing reserved in legacy rows means zero,” $ifNull can encode that default because it treats null/missing as the fallback case. If missing means “unknown,” defaulting to zero would fabricate inventory.

db.inventory_query.insertOne({_id:"sku-legacy",status:"active",onHand:3,reorderPoint:5})const availableLegacy={ $subtract:[{$ifNull:["$onHand",0]},{$ifNull:["$reserved",0]}]};const q={status:"active",$expr:{$lt:[availableLegacy,"$reorderPoint"]}};printjson(db.inventory_query.find(q,{_id:1,onHand:1,reserved:1,reorderPoint:1}).sort({_id:1}).toArray())

Document the fallback in the schema/version migration. Expressions can make a query resilient, but they should not hide unbounded data-quality debt.

4. $expr is powerful, but computed predicates can be less index-friendly

An ordinary predicate such as {status:"active",onHand:{$lt:5}} compares indexed fields with constants and is a classic indexable shape. A field-to-field predicate such as {$expr:{$gt:["$reserved","$onHand"]}} depends on two values from each document; an ordinary B-tree index cannot precompute every such relationship. MongoDB can optimize some $expr comparisons involving a field and a constant, especially in documented $lookup contexts, but do not extrapolate that to arbitrary field-to-field arithmetic. Multikey indexes are also not supported by $expr.

db.inventory_query.createIndex({status:1,onHand:1})const simple={status:"active",onHand:{$lt:5}};const computed={status:"active",$expr:{$gt:["$reserved","$onHand"]}};for (const [label,q] of [["simple",simple],["computed",computed]]) { const e=db.inventory_query.explain("executionStats").find(q,{_id:1}); printjson({label,nReturned:e.executionStats.nReturned,keys:e.executionStats.totalKeysExamined,  docs:e.executionStats.totalDocsExamined,winningPlan:e.queryPlanner.winningPlan});}

If a computed predicate becomes a hot path, consider whether the application should maintain a derived field such as available or needsReorder under a well-defined write invariant and index that field. That trades write complexity for read efficiency; it is not automatically better.

5. AtlasMart computed-predicate lab

docker rm -f atlasmart-mongo-ch04-l4docker run --name atlasmart-mongo-ch04-l4 -p 127.0.0.1:27035:27017 -d mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimdocker logs atlasmart-mongo-ch04-l4 --tail 25

mongosh "mongodb://127.0.0.1:27035/atlasmart?directConnection=true" --quiet --eval 'db.inventory_query.drop();db.inventory_query.insertMany([ {_id:"sku-a",status:"active",onHand:10,reserved:4,reorderPoint:3}, {_id:"sku-b",status:"active",onHand:5,reserved:7,reorderPoint:2}, {_id:"sku-c",status:"active",onHand:8,reserved:6,reorderPoint:4}, {_id:"sku-legacy",status:"active",onHand:3,reorderPoint:5}, {_id:"sku-d",status:"retired",onHand:100,reserved:0,reorderPoint:10}]);db.inventory_query.createIndex({status:1,onHand:1});const available={$subtract:[{$ifNull:["$onHand",0]},{$ifNull:["$reserved",0]}]};const q={status:"active",$expr:{$lt:[available,"$reorderPoint"]}};printjson(db.inventory_query.aggregate([{$match:q},{$project:{_id:1,available,reorderPoint:1}},{$sort:{_id:1}}]).toArray());for (const [label,filter] of [["simple",{status:"active",onHand:{$lt:5}}],["computed",q]]) { const e=db.inventory_query.explain("executionStats").find(filter,{_id:1}); printjson({label,nReturned:e.executionStats.nReturned,keys:e.executionStats.totalKeysExamined,docs:e.executionStats.totalDocsExamined});}' 

Verification checklist

• The field-to-field over-reservation predicate identifies the intentionally invalid record.
• The reusable available-stock expression is used in both matching and projection.
• The legacy default is explicit through $ifNull; the lesson states that this is a domain choice, not a universal rule.
• The direct and computed predicates are both explained, with keys/docs examined captured from the local environment.
• The ordinary equality/range predicates stay outside $expr where possible.
• No client-side full-collection loop is needed to decide the server-side condition.

docker rm -f atlasmart-mongo-ch04-l4

The final lesson turns the chapter into a query-review discipline for expensive or accidentally non-indexable predicates.

Authoritative references

• MongoDB release notes — Official current stable server series and patch notes.
• MongoDB 8.3 release notes — Official 8.3 patch history; 8.3.8 is the latest released patch at review time.
• MongoDB query documents — Official find/query behavior and cursor semantics.
• MongoDB query optimization — Official selectivity, index, and explain guidance.
• PyMongo query documents — Official Python driver query-filter behavior.
• $expr query operator — Use aggregation expressions in query predicates and documented index limitations.
• Aggregation expressions — Expression components, field paths, constants, and operators.
• Multikey indexes — Documents that $expr does not support multikey indexes.
• Query optimization — Selectivity, index use, and explain-driven optimization principles.