Docs Menu

Docs HomeMongoDB Manual

$exists

On this page

  • Definition
  • Examples
$exists

Syntax: { field: { $exists: <boolean> } }

When <boolean> is true, $exists matches the documents that contain the field, including documents where the field value is null. If <boolean> is false, the query returns only the documents that do not contain the field. [1]

MongoDB $exists does not correspond to SQL operator exists. For SQL exists, refer to the $in operator.

Tip

See also:

[1] Starting in MongoDB 4.2, users can no longer use the query filter $type: 0 as a synonym for $exists:false. To query for null or missing fields, see Query for Null or Missing Fields.

Consider the following example:

db.inventory.find( { qty: { $exists: true, $nin: [ 5, 15 ] } } )

This query will select all documents in the inventory collection where the qty field exists and its value does not equal 5 or 15.

The following examples uses a collection named records with the following documents:

{ a: 5, b: 5, c: null }
{ a: 3, b: null, c: 8 }
{ a: null, b: 3, c: 9 }
{ a: 1, b: 2, c: 3 }
{ a: 2, c: 5 }
{ a: 3, b: 2 }
{ a: 4 }
{ b: 2, c: 4 }
{ b: 2 }
{ c: 6 }

The following query specifies the query predicate a: { $exists: true }:

db.records.find( { a: { $exists: true } } )

The results consist of those documents that contain the field a, including the document whose field a contains a null value:

{ a: 5, b: 5, c: null }
{ a: 3, b: null, c: 8 }
{ a: null, b: 3, c: 9 }
{ a: 1, b: 2, c: 3 }
{ a: 2, c: 5 }
{ a: 3, b: 2 }
{ a: 4 }

The following query specifies the query predicate b: { $exists: false }:

db.records.find( { b: { $exists: false } } )

The results consist of those documents that do not contain the field b:

{ a: 2, c: 5 }
{ a: 4 }
{ c: 6 }

Starting in MongoDB 4.2, users can no longer use the query filter $type: 0 as a synonym for $exists:false. To query for null or missing fields, see Query for Null or Missing Fields.

The following table compares $exists query performance using sparse and non-sparse indexes:

$exists Query
Using a Sparse Index
Using a Non-Sparse Index
{ $exists: true }
Most efficient. MongoDB can make an exact match and does not require a FETCH.
More efficient than queries without an index, but still requires a FETCH.
{ $exists: false }
Cannot use the index and requires a COLLSCAN.
Requires a FETCH.

Queries that use { $exists: true } on fields that use a non-sparse index or that use { $exists: true } on fields that are not indexed examine all documents in a collection. To improve performance, create a sparse index on the field as shown in the following scenario:

  1. Create a stockSales collection:

    db.stockSales.insertMany( [
    { _id: 0, symbol: "ABC", auditDate: new Date( "2021-05-18T16:12:23Z" ) },
    { _id: 1, symbol: "ABC", auditDate: new Date( "2021-04-21T11:34:45Z" ) },
    { _id: 2, symbol: "DEF", auditDate: new Date( "2021-02-24T15:11:32Z" ) },
    { _id: 3, symbol: "DEF", auditDate: null },
    { _id: 4, symbol: "DEF", auditDate: new Date( "2021-07-13T18:32:54Z" ) },
    { _id: 5, symbol: "XYZ" }
    ] )

    The document with an _id of:

    • 3 has a null auditDate value.

    • 5 is missing the auditDate value.

  2. Create a sparse index on the auditDate field:

    db.getCollection( "stockSales" ).createIndex(
    { auditDate: 1 },
    { name: "auditDateSparseIndex", sparse: true }
    )
  3. The following example counts the documents where the auditDate field has a value (including null) and uses the sparse index:

    db.stockSales.countDocuments( { auditDate: { $exists: true } } )

    The example returns 5. The document that is missing the auditDate value is not counted.

Tip

If you only need documents where the field has a non-null value, you:

  • Can use $ne: null instead of $exists: true.

  • Do not need a sparse index on the field.

For example, using the stockSales collection:

db.stockSales.countDocuments( { auditDate: { $ne: null } } )

The example returns 4. Documents that are missing the auditDate value or have a null auditDate value are not counted.

←  Element Query Operators$type →

On this page

Give Feedback
© 2022 MongoDB, Inc.

About

  • Careers
  • Investor Relations
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2022 MongoDB, Inc.