Null and missing fields: real-world lessons and examples

The subtle trap of null and missing fields

When you query a MongoDB collection for documents where a field equals null, you get both documents that explicitly set the field to null and documents where the field doesn't exist at all. This silent collision between null and missing fields can slip past code review and cause real data leaks in production. We've seen e-commerce teams accidentally include users without shipping addresses in bulk operations, and CRM systems fail to filter out incomplete leads. Understanding null and missing fields: real-world lessons and examples is essential for building reliable schemas and queries.

The default behavior: { field: null } matches both

MongoDB treats missing fields and null values as equivalent in most query contexts. When you write { phone: null }, you match three categories of documents: those with phone: null, those with phone: undefined, and those with no phone field at all.

This is convenient for optional fields in user signup flows. A user might not provide a phone number (missing field), or you might explicitly set it to null during data migration. From the application's perspective, both states mean "no phone available." But the convenience hides a trap when you need to distinguish between them.

Consider a ride-hailing backend tracking driver profiles:

// Driver just signed up, never entered phone
db.drivers.insertOne({
  _id: ObjectId("1"),
  name: "Alice",
  email: "alice@example.com"
  // phone field doesn't exist
});

// Driver explicitly removed their phone during settings cleanup
db.drivers.insertOne({
  _id: ObjectId("2"),
  name: "Bob",
  email: "bob@example.com",
  phone: null
});

// Both match this query
const incomplete = await db.drivers.find({ phone: null });
// Returns both Alice and Bob

When you aggregate to send SMS reminders, you might filter with { phone: { $ne: null } }, thinking you're excluding users without phones. But both missing and null fields fail this check, so Alice and Bob both get excluded—correct outcome, but for different reasons. Now add a third scenario: a data migration that populates phone fields for some drivers but leaves others untouched. Your query logic no longer clearly expresses intent.

Using $exists to distinguish missing from null

The $exists operator explicitly checks whether a field is present in the document, regardless of its value. This lets you differentiate between "deliberately set to null" and "never provided."

// Only match documents where phone field exists
db.drivers.find({ phone: { $exists: true } });
// Returns only Bob (phone: null)

// Only match documents where phone field is missing
db.drivers.find({ phone: { $exists: false } });
// Returns only Alice (no phone key)

// Match documents with actual phone numbers
db.drivers.find({ 
  phone: { $exists: true, $ne: null } 
});
// Returns neither Alice nor Bob

// Match documents ready for SMS (have phone)
db.drivers.find({ phone: { $exists: true, $type: "string", $ne: "" } });

In a SaaS multi-tenant system, you might track subscription trial end dates for each account:

// Account never started trial
db.accounts.insertOne({
  _id: ObjectId("1"),
  name: "Acme Corp"
});

// Account trial ended and was explicitly cleared
db.accounts.insertOne({
  _id: ObjectId("2"),
  name: "TechStart Ltd",
  trialEndsAt: null
});

// Account actively in trial
db.accounts.insertOne({
  _id: ObjectId("3"),
  name: "Growth Inc",
  trialEndsAt: new Date("2025-02-28")
});

// Find all accounts that have entered trial (even if expired)
const hasTrialed = await db.accounts.find({ trialEndsAt: { $exists: true } });
// Returns only TechStart Ltd and Growth Inc

// Send reminder emails to accounts currently in trial
const activeTrial = await db.accounts.find({
  trialEndsAt: { $exists: true, $gt: new Date() }
});
// Returns only Growth Inc

Using $exists makes your schema contract explicit: you're saying "trialEndsAt should be set to a date when the account enters the trial flow, or explicitly set to null if the trial ends." Accounts that never had a trial have the field missing entirely. Your colleagues reading the code see the intent immediately.

Partial indexes: leveraging the distinction for performance

MongoDB partial indexes use filter expressions to index only documents that match certain criteria. This is where the null vs. missing distinction becomes powerful for both correctness and speed.

Imagine an e-commerce order system where orders have an optional refundedAt field. Most orders never get refunded, so indexing all of them wastes space. A partial index indexes only refunded orders:

// Create a partial index: only index orders that have been refunded
db.orders.createIndex(
  { refundedAt: 1 },
  { partialFilterExpression: { refundedAt: { $exists: true } } }
);

// This query uses the partial index (fast)
db.orders.find({ refundedAt: { $exists: true, $gt: new Date("2025-01-01") } });

// This query does NOT use the partial index
db.orders.find({ refundedAt: null });

Why? Because the partial index only contains documents where refundedAt exists (is not missing). When you query for null, MongoDB must scan all documents with missing refundedAt fields, which aren't in the index at all.

In a travel platform tracking hotel booking confirmations:

// Index only confirmed bookings for fast lookup
db.bookings.createIndex(
  { confirmationCode: 1 },
  { partialFilterExpression: { confirmationCode: { $exists: true } } }
);

// Fast: uses the index
db.bookings.findOne({ confirmationCode: "ABC123" });

// Slow: full collection scan, finds pending bookings
db.bookings.find({ confirmationCode: null });

The partial index assumes your schema is disciplined: confirmed bookings have a confirmationCode field (possibly updated later), and unconfirmed ones don't have the field at all. If you mix missing and null throughout your data, partial indexes lose their effectiveness.

Schema consistency: the long-term lesson

The deepest lesson from null and missing fields is that consistency in your schema prevents confusion later. You need a team agreement on what each state means.

One pattern that works well: optional fields are missing by default; null means an explicit action cleared them.

// Pattern: missing = never set, null = deliberately cleared
db.users.insertOne({
  _id: ObjectId("1"),
  email: "new@example.com",
  // phone is missing: user hasn't provided it
});

// After user deletes their phone from settings:
db.users.updateOne(
  { _id: ObjectId("1") },
  { $set: { phone: null } }
);

// Query: get users who have provided a phone (ever)
db.users.find({ phone: { $exists: true } });

// Query: get users ready to receive SMS
db.users.find({ phone: { $exists: true, $ne: null, $type: "string" } });

Enforce this consistency in your validation layer. If your schema says deletedAt is optional, decide upfront: do you never set it (missing) or set it to null for soft deletes? Document it in your schema comments.

// In your migration or schema definition:
// deletedAt: ISO date when the record was soft-deleted, null if active
// Never left missing—always explicitly null or set to a date.

db.posts.updateMany(
  { deletedAt: { $exists: false } },
  { $set: { deletedAt: null } }
);
// Now all posts have deletedAt field; queries are unambiguous

Common mistakes

• Assuming { field: null } only matches null values. It matches missing fields too. Use $exists to be explicit.
• Relying on partial indexes without disciplined schema. If your data has both missing and null fields, the partial index won't cover all cases you expect.
• Mixing null and missing across your codebase. One service might leave fields missing while another sets them to null. This creates silent bugs when you query across both patterns.
• Forgetting to test edge cases in integration tests. Add test cases that verify behavior with missing fields, null values, and populated values separately.
• Not documenting schema intent in comments or validation code. Your teammates shouldn't have to reverse-engineer whether a field being missing is intentional or accidental.

Key takeaways

• MongoDB treats missing fields and null values as equivalent in most queries; use $exists to distinguish them.
• Partial indexes only index documents where the field exists, making them more efficient when your schema is consistent.
• Establish a team convention: decide whether optional fields are missing by default or always present (null when not set).
• Enforce schema consistency through migration scripts and validation logic; don't leave it to chance.
• Test your queries and indexes explicitly with both missing and null fields to avoid production surprises.
• Document the intent behind optional fields in your schema definitions so team members understand the expected behavior.