Validating Document References with $lookup

Finding the documents whose reference points at nothing — an order.customer_id with no matching row in customers — is a read-side detection job, and it is the natural counterpart to the write-side guards catalogued under cross-collection validation patterns within the MongoDB JSON Schema Validation Architecture. Because a $jsonSchema validator only ever sees one document and cannot join to a second collection, broken references are invisible to it and accumulate silently; the aggregation framework’s $lookup stage is the tool that makes them visible again. This page gives you a runnable pipeline that returns exactly the orphaned documents, shows how to schedule it as a periodic integrity job, and covers the indexing and performance work that decides whether that scan finishes in seconds or saturates the primary.

Operational Mechanics and Write-Path Impact

Detection is a read operation and touches no validator, no collMod, and no write path. A $lookup performs a left outer join from the local collection (orders) to a foreign collection (customers), attaching an array of matched parents to each input document. A reference is orphaned precisely when that array comes back empty, so the detection idiom is $lookup followed by a $match on an empty result array. This is the read-side mirror of write-side enforcement: a transactional guard prevents an orphan at commit time, whereas this pipeline finds orphans that already exist — whether they predate any guard, arrived through a bypassed path, or were created by a parent delete. The two approaches are complementary, and the write-time mechanics they detect against are developed under enforcing referential integrity across collections.

Two forms exist. The classic localField/foreignField join is the simplest and matches a single equality. The pipelined $lookup with a let/$expr sub-pipeline is more expressive: it can add predicates so that only active parents count, catching references that resolve to a soft-deleted or archived customer — a subtler class of orphan that the equality join treats as valid.

$lookup form	Matches on	Best for	Cost profile
localField / foreignField	Single-field equality (customer_id = _id)	Plain existence checks	Uses an index on the foreign _id; cheapest
let + $expr sub-pipeline	Equality plus extra predicates (e.g. status: active)	Existence and liveness of the parent	Needs a compound index to stay indexed
$lookup then $match parent: []	Empty-join detection	Returning the orphans themselves	Scan cost scales with the local collection

Critically, the pipeline reads a point-in-time snapshot and holds no lock on the write path, so running it against a production replica set — ideally with readPreference=secondaryPreferred — adds no write latency. It is a report, not an enforcement gate.

Exact Diagnostic Fingerprints and Fast Resolution

The output of the detection pipeline is itself the diagnostic. Each returned document is an orphan; the shape of the result set tells you which failure produced it. Match on these fingerprints to route the finding:

Signature	What the pipeline shows	Root cause	Resolution
parent array is [] for scattered _ids	Isolated orphans across time	References written without an existence guard	Backfill or quarantine; add a guarded write path
parent array is [] clustered on recent customer_ids	A burst of orphans sharing parents	A delete_many on customers with no cascade	Restore parents from backup or tombstone the children
Join is slow, COLLSCAN on customers in explain	Pipeline runs for minutes	Foreign field is not _id and is unindexed	Index the foreign field; re-check with explain
Parent resolves under equality join but not under $expr liveness check	Two counts disagree	Reference points at a soft-deleted parent	Treat as an orphan; enforce liveness in the write guard

Confirm the scan is index-backed before you schedule it — an unindexed $lookup degrades to a nested-loop scan of the foreign collection per input document, which is quadratic and will saturate the node:

db.orders.aggregate([
  { $lookup: { from: "customers", localField: "customer_id",
               foreignField: "_id", as: "parent" } },
  { $match: { parent: { $eq: [] } } }
], { explain: true })
// Inspect $lookup stage: want an indexed path on customers._id, not COLLSCAN.

Wiring this count into a recurring signal turns a manual grep into an operational metric; the alerting side of that is the subject of tracking validation failures with MongoDB Atlas alerts.

Step-by-Step Playbook

The goal is a repeatable integrity job that returns orphans cheaply and can run unattended on a schedule against a secondary.

1. Index the foreign field the join reads. When customer_id maps to customers._id, the _id index already covers it. If your reference targets a non-_id field, index it explicitly or the join is a scan:

// Only needed when the join targets a non-_id field, e.g. a natural key.
db.customers.createIndex({ account_ref: 1 })
// Expected output: account_ref_1

2. Run the anti-join to return the orphans. This pipeline returns the offending order documents themselves, not just a count, so remediation can act on _id:

db.orders.aggregate([
  { $lookup: { from: "customers", localField: "customer_id",
               foreignField: "_id", as: "parent" } },
  { $match: { parent: { $eq: [] } } },
  { $project: { customer_id: 1, status: 1, created_at: 1 } }
])
// Expected output: the order documents whose customer_id resolves to nothing

3. Add a liveness predicate when soft-deletes exist. Swap the equality join for a let/$expr sub-pipeline so a reference to an archived customer also counts as an orphan:

db.orders.aggregate([
  { $lookup: {
      from: "customers",
      let: { cid: "$customer_id" },
      pipeline: [
        { $match: { $expr: { $and: [
          { $eq: ["$_id", "$$cid"] },
          { $eq: ["$archived", false] }
        ] } } },
        { $project: { _id: 1 } }
      ],
      as: "parent"
  } },
  { $match: { parent: { $eq: [] } } }
])

4. Schedule it as an unattended integrity job. Drive the pipeline from Python against a secondary so it never competes with production writes, and persist the result for trend tracking:

from pymongo import MongoClient, ReadPreference

client = MongoClient("mongodb://localhost:27017/?replicaSet=rs0")
db = client.shop.with_options(read_preference=ReadPreference.SECONDARY_PREFERRED)

def integrity_job() -> int:
    pipeline = [
        {"$lookup": {"from": "customers", "localField": "customer_id",
                     "foreignField": "_id", "as": "parent"}},
        {"$match": {"parent": {"$eq": []}}},
        {"$project": {"parent": 0}},
    ]
    orphans = list(db.orders.aggregate(pipeline, allowDiskUse=True))
    # Snapshot the count so the trend is a metric, not a one-off grep.
    client.shop.integrity_reports.insert_one(
        {"orphaned_orders": len(orphans), "ids": [o["_id"] for o in orphans]}
    )
    return len(orphans)

Failure Modes & Rollback

Detection is read-only, so its failure modes are about cost and correctness rather than data loss:

• Unindexed join goes quadratic (step 1). Without an index on the foreign field, $lookup runs a full scan of customers for every order, and the job time explodes on large collections. Confirm the indexed path in explain before scheduling; if you cannot index the foreign field, do not run the scan on the primary.
• In-memory aggregation hits the 100 MB stage limit (step 2). A large orphan result set can exceed the per-stage memory cap and fail with code 292 (QueryExceededMemoryLimitNoDiskUseAllowed). Pass allowDiskUse=True, as the job above does, or narrow the pipeline with an earlier $match.
• Scheduled job overlaps its predecessor (step 4). A scan that runs longer than its interval can stack, doubling read load. Guard the job with a lock document so a run exits early if the previous one is still active.

Because the pipeline writes nothing to orders or customers, there is nothing to roll back on the detection side — aborting a run is as simple as killing the aggregation cursor, with zero data impact and instant recovery. The only writes are to the integrity_reports sink and any quarantine collection, and those are additive; dropping a bad report is a single delete_one. When detection is used to drive remediation, the corrective writes themselves belong to the write-side pattern, and documents that cannot be re-linked should be diverted through fallback routing for invalid documents rather than deleted.

Frequently Asked Questions

Related

• Cross-Collection Validation Patterns — the parent workflow situating read-side detection alongside write-side enforcement.
• MongoDB JSON Schema Validation Architecture — the section reference explaining why the single-document validator cannot express joins at all.
• Enforcing Referential Integrity Across MongoDB Collections — the write-time guard whose escaped orphans this pipeline is designed to catch.
• Atomic Multi-Collection Writes with Validated Transactions — the transactional mechanism that prevents the orphans detection reports on.
• Tracking Validation Failures with MongoDB Atlas Alerts — turning the orphan count into a scheduled, alerting metric.