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
nullvalues and missing fields into thesample_mflix.userscollection.Set up an Atlas Search index with dynamic mapping for the
sample_mflix.userscollection.Run an Atlas Search query to find all documents with
nullvalues for thepasswordfield in thesample_mflix.userscollection.Run an Atlas Search query to find all documents that don't contain
nullvalues for thepasswordfield in thesample_mflix.userscollection.
Before you begin, ensure that your Atlas cluster meets the requirements described in the Prerequisites.
Required Access
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 Database Deployments 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 Database Deployments page is not already displayed, click Database in the sidebar.
Click your cluster's Browse Collections button.
Insert documents into the sample_mflix.users collection.
In the
sample_mflixdatabase, select theuserscollection.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@gmail.com", "password": null } { "name": "Laura Garcia", "email": "lgarcia@gmail.com" }
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 Search tab.
Click Create Index.
Select a Configuration Method and click Next.
For a guided experience, select Visual Editor.
To edit the raw index definition, select JSON Editor.
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 anindexparameter when using the $search pipeline stage. Otherwise, you must specify the index name using theindexparameter.In the Database and Collection section, find the
sample_mflixdatabase, and select theuserscollection.
Specify an index definition.
You can create an Atlas Search index that uses dynamic mappings through the Visual Editor or
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.
Click Create Search Index.
Close the You're All Set! Modal Window.
A modal window appears to let you know your index is building. Click the Close button.
Wait for the index to finish building.
The index should take about one minute to build. While it is
building, the Status column reads Build in
Progress. When it is finished building, the
Status column reads Active.
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
To successfully check for null values, the field that you
are querying can't have polymorphic data. For example, all
other documents in the sample_mflix.users collection
have the string data type for the password field.
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.