Docs Menu

Docs HomeMongoDB Manual

$exists

On this page

  • Definition
  • Compatibility
  • Syntax
  • Query Data on Atlas by Using Atlas Search
  • Examples
  • Exists and Not Equal To
  • Null Values
  • Use a Sparse Index to Improve $exists Performance
$exists

The $exists operator matches documents that contain or do not contain a specified field, including documents where the field value is null.

Note

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

For Atlas Search exists, refer to the exists operator in the Atlas documentation.

Tip

See also:

You can use $exists for deployments hosted in the following environments:

  • MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

To specify an $exists expression, use the following prototype:

{ 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]

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

For data stored in MongoDB Atlas, you can use the Atlas Search exists operator when running $search queries. Running $exists after $search is less performant than running $search with the exists operator.

To learn more about the Atlas Search version of this operator, see the exists operator in the Atlas documentation.

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 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 }
] )

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 }

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.

The following scenario is not optimal because all of the collection's documents are examined:

  • You use a query to retrieve or count documents, and

  • use field: { $exists: true }, and

  • the field has a non-sparse index or does not have an index.

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: "MDB", auditDate: new Date( "2021-05-18T16:12:23Z" ) },
    { _id: 1, symbol: "MDB", auditDate: new Date( "2021-04-21T11:34:45Z" ) },
    { _id: 2, symbol: "MSFT", auditDate: new Date( "2021-02-24T15:11:32Z" ) },
    { _id: 3, symbol: "MSFT", auditDate: null },
    { _id: 4, symbol: "MSFT", auditDate: new Date( "2021-07-13T18:32:54Z" ) },
    { _id: 5, symbol: "AAPL" }
    ] )

    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 →
Share Feedback
© 2023 MongoDB, Inc.

About

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