How to Check for Null and Non-Null Values with Atlas Search
On this page
This tutorial describes how to use Atlas Search queries to
check your data for null
or non-null
values. It takes you
through the following steps:
Load sample documents with
null
values and missing fields into thesample_mflix.users
collection.Set up an Atlas Search index with dynamic mapping for the
sample_mflix.users
collection.Run an Atlas Search query to find all documents with
null
values for thepassword
field in thesample_mflix.users
collection.Note
To successfully check for
null
values, the field that you are querying can't have polymorphic data. In this example, all documents in thesample_mflix.users
collection have thestring
data type for thepassword
field.Run an Atlas Search query to find all documents that don't contain
null
values for thepassword
field in thesample_mflix.users
collection.
Before you begin, ensure that your Atlas cluster meets the requirements described in the Prerequisites.
To create an Atlas Search index, you must have Project Data Access Admin
or higher access to the project.
Insert Sample Data
In this section, you add sample documents that contain null
values and
missing fields to the sample_mflix.users
collection. These additional
documents allow you to successfully run the sample queries for null
and non-null
values in this tutorial.
Navigate to the Clusters page for your project.
If it is not already displayed, select the organization that contains your desired project from the Organizations menu in the navigation bar.
If it is not already displayed, select your desired project from the Projects menu in the navigation bar.
If the Clusters page is not already displayed, click Database in the sidebar.
Insert documents into the sample_mflix.users
collection.
In the
sample_mflix
database, select theusers
collection.Click Insert Document.
Click the JSON view ({}) to replace the default document.
One at a time, copy and paste the following sample documents and click Insert to add the documents to the collection.
{ "name": "Andre Robinson", "email": "andre.robinson@example.com", "password": null } { "name": "Laura Garcia", "email": "lgarcia@example.net" }
Create an Atlas Search Index with Dynamic Mapping
In this section, you create an Atlas Search index that uses dynamic mapping to automatically index all the dynamically
indexable fields in the sample_mflix.users
collection.
Navigate to the Atlas Search page for your project.
If it is not already displayed, select the organization that contains your desired project from the Organizations menu in the navigation bar.
If it is not already displayed, select your desired project from the Projects menu in the navigation bar.
Click your cluster's name.
Click the Atlas Search tab.
Enter the Index Name, and set the Database and Collection.
In the Index Name field, enter
null-check-tutorial
.Note
If you name your index
default
, you don't need to specify anindex
parameter when using the $search pipeline stage. Otherwise, you must specify the index name using theindex
parameter.In the Database and Collection section, find the
sample_mflix
database, and select theusers
collection.
Specify an index definition.
You can create an Atlas Search index that uses dynamic mappings through the Atlas Search Visual Editor or
Atlas Search JSON Editor in the Atlas User Interface. The
index definition that you create dynamically indexes the fields of
supported types in each document in
the users
collection.
Check for null
Values
➤ Use the Select your language drop-down menu to set the language of the example on this page.
After setting up the Atlas Search index, you can connect to your Atlas cluster
and run queries against fields in the sample_mflix.users
collection.
The following query uses the compound and wildcard
operators to find all documents that have a password
field with a
null
value.
Note
We don't recommend using the wildcard operator to query on collections with large index sizes. To learn more and vote for Atlas Search enhancements to support indexing and querying for null datatype, use the MongoDB Feedback Engine.
Atlas Search returns one document with a password
value that is null
.
Atlas Search doesn't include documents that are missing the password
field
because the query specifies that the password
field must exist.
Find All Non-null
Values
➤ Use the Select your language drop-down menu to set the language of the example on this page.
After setting up the Atlas Search index, you can connect to your Atlas cluster
and run queries against fields in the sample_mflix.users
collection.
The following query uses the compound and wildcard
operators to find documents that don't have a null
value in the
password
field or don't have the password
field.
Atlas Search returns documents with password
values that are not
null
. The first document listed in the results has a higher score
because the constant
option alters the score for documents with a
missing password
field.