Docs Home → MongoDB Manual

$exists

On this page

Definition

$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.

Examples

Exists and Not Equal To

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.

Null Values

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

db.spices.insertMany( [
   { saffron: 5, cinnamon: 5, mustard: null },
   { saffron: 3, cinnamon: null, mustard: 8 },
   { saffron: null, cinnamon: 3, mustard: 9 },
   { saffron: 1, cinnamon: 2, mustard: 3 },
   { saffron: 2, mustard: 5 },
   { saffron: 3, cinnamon: 2 },
   { saffron: 4 },
   { cinnamon: 2, mustard: 4 },
   { cinnamon: 2 },
   { mustard: 6 }
] )

$exists: true

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

db.spices.find( { saffron: { $exists: true } } )

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

{ saffron: 5, cinnamon: 5, mustard: null }
{ saffron: 3, cinnamon: null, mustard: 8 }
{ saffron: null, cinnamon: 3, mustard: 9 }
{ saffron: 1, cinnamon: 2, mustard: 3 }
{ saffron: 2, mustard: 5 }
{ saffron: 3, cinnamon: 2 }
{ saffron: 4 }

$exists: false

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

db.spices.find( { cinnamon: { $exists: false } } )

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

{ saffron: 2, mustard: 5 }
{ saffron: 4 }
{ mustard: 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.

Use a Sparse Index to Improve $exists Performance

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

$exists QueryUsing a Sparse IndexUsing 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.

Tip

See also:

Query for Null or Missing Fields