How to Check for Null and Non-Null Values with Atlas Search

In Atlas, go to the Clusters page for your project.

1. If it is not already displayed, select the organization that contains your desired project from the Organizations menu in the navigation bar.

2. If it is not already displayed, select your desired project from the Projects menu in the navigation bar.

3. If the Clusters page is not already displayed, click Database in the sidebar.

Go to the Atlas Search page for your cluster.

You can go the Atlas Search page from the sidebar, the Data Explorer, or your cluster details page.

Go to the Search Tester.

Click the Query button to the right of the index to query.

View and edit the query syntax.

Click Edit Query to view a default query syntax sample in JSON format.

Run an Atlas Search query with the compound operator to search the users collection.

The following query uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

• must clause to find only documents that have the password field.

• mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

[
  {
    "$search": {
      index: "null-check-tutorial",
      "compound": {
        "must": {
          "exists": {
            "path": "password"
          }
        },
        "mustNot": {
          "wildcard": {
            "path": "password",
            "query": "*",
            "allowAnalyzedField": true
          }
        }
      }
    }
  }
]

SCORE: 1  _id:  "64a6e2e5bceafd4df9153bab"
  name: "Andre Robinson"
  email: "andre.robinson@example.com"
  password: null

Connect to your cluster in mongosh.

Open mongosh in a terminal window and connect to your cluster. For detailed instructions on connecting, see Connect via mongosh.

Use the sample_mflix database.

Run the following command at mongosh prompt:

use sample_mflix

Run an Atlas Search query with the compound and wildcard operators on the users collection.

The following query uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

• must clause to find only documents that have the password field.

• mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

db.users.aggregate([
    {
        $search: {
            "index": "null-check-tutorial",
            "compound": {
                "must": {
                    "exists": {
                        "path": "password"
                    }
                },
                "mustNot": {
                    "wildcard": {
                        "path": "password",
                        "query": "*",
                        "allowAnalyzedField": true
                    }
                }
            }
        }
    }
])

{
  _id: ObjectId("63c84590975d5c7c8bf1ebed"),
  name: 'Andre Robinson',
  email: 'andre.robinson@example.com',
  password: null
}

Connect to your cluster in MongoDB Compass.

Open MongoDB Compass and connect to your cluster. For detailed instructions on connecting, see Connect via Compass.

Use the users collection in the sample_mflix database.

On the Databases screen, click the sample_mflix database, then click the users collection.

Run an Atlas Search query with the compound and wildcard operators on the users collection.

The following query uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

• must clause to find only documents that have the password field.

• mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

To run this query in MongoDB Compass:

1. Click the Aggregations tab.

2. Click Select..., and then configure the following pipeline stage by selecting the $search stage from the dropdown and adding the query for that stage.

   Pipeline Stage	Query
   $search	{ "index": "null-check-tutorial", "compound": { "must": { "exists": { "path": "password" } }, "mustNot": { "wildcard": { "path": "password", "query": "*", "allowAnalyzedField": true } } } }

If you enabled Auto Preview, MongoDB Compass displays the following documents next to the $search pipeline stage:

1
{
2
   _id: ObjectId("63c84590975d5c7c8bf1ebed"),
3
   name: 'Andre Robinson',
4
   email: 'andre.robinson@example.com',
5
   password: null
6
}

Run the following commands to initialize your project.

1. Create a new directory called is-null-query-example and initialize your project with the dotnet new command.

   mkdir is-null-query-example
   cd is-null-query-example
   dotnet new console

2. Add the .NET/C# Driver to your project as a dependency.

   dotnet add package MongoDB.Driver

Replace the contents of the Program.cs file with the following code.

The code example performs the following tasks:

• Imports mongodb packages and dependencies.

• Establishes a connection to your Atlas cluster.

• Uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

  • must clause to find only documents that have the password field.

  • mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

• Iterates over the cursor to print the documents that match the query.

1
using MongoDB.Bson;
2
using MongoDB.Bson.Serialization.Attributes;
3
using MongoDB.Bson.Serialization.Conventions;
4
using MongoDB.Driver;
5
using MongoDB.Driver.Search;
6
7
public class IsNullQuery 
8
{
9
    private const string MongoConnectionString = "<connection-string>";
10
    
11
    public static void Main(string[] args) 
12
    {
13
        // allow automapping of the camelCase database fields to our UserDocument
14
        var camelCaseConvention = new ConventionPack { new CamelCaseElementNameConvention() };
15
        ConventionRegistry.Register("CamelCase", camelCaseConvention, type => true);
16
17
        // connect to your Atlas cluster
18
        var mongoClient = new MongoClient(MongoConnectionString);
19
        var mflixDatabase = mongoClient.GetDatabase("sample_mflix");
20
        var usersCollection = mflixDatabase.GetCollection<UserDocument>("users");
21
22
        // define and run pipeline
23
        var results = usersCollection.Aggregate()
24
            .Search(Builders<UserDocument>.Search.Compound()
25
                .Must(Builders<UserDocument>.Search.Exists(user => user.Password))
26
                .MustNot(Builders<UserDocument>.Search.Wildcard(user => user.Password, "*", true)),
27
                indexName: "null-check-tutorial")
28
                .ToList();
29
30
        // print results
31
        foreach (var user in results) 
32
        {
33
            Console.WriteLine(user.ToJson());
34
        }
35
    }
36
}
37
38
[BsonIgnoreExtraElements]
39
public class UserDocument 
40
{
41
    [BsonIgnoreIfDefault]
42
    public ObjectId Id { get; set; }
43
    public string Name { get; set; }
44
    public string Email { get; set; }
45
    public string Password { get; set; }
46
}

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the following command to query your collection.

After you run the dotnet run command, the program prints the following document to your terminal:

dotnet run Program.cs

1
{
2
  "_id" : ObjectId("63d02602e69635364a445f3b"),
3
  "name" : "Andre Robinson",
4
  "email" : "andre.robinson@example.com",
5
  "password" : null
6
}

Create a file named isnull-query.go.

Copy and paste the following code into the isnull-query.go file.

The code example performs the following tasks:

• Imports mongodb packages and dependencies.

• Establishes a connection to your Atlas cluster.

• Uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

  • must clause to find only documents that have the password field.

  • mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

• Iterates over the cursor to print the documents that match the query.

1
package main
2
3
import (
4
	"context"
5
	"fmt"
6
7
	"go.mongodb.org/mongo-driver/bson"
8
	"go.mongodb.org/mongo-driver/mongo"
9
	"go.mongodb.org/mongo-driver/mongo/options"
10
)
11
12
func main() {
13
	// connect to your Atlas cluster
14
	client, err := mongo.Connect(context.TODO(), options.Client().ApplyURI("<connection-string>"))
15
	if err != nil {
16
		panic(err)
17
	}
18
	defer client.Disconnect(context.TODO())
19
	// set namespace
20
	collection := client.Database("sample_mflix").Collection("users")
21
	// define pipeline stages
22
	searchStage := bson.D{{"$search", bson.D{{"index", "null-check-tutorial"}, {"compound", bson.D{{"must", bson.D{{"exists", bson.D{{"path", "password"}}}}}, {"mustNot", bson.D{{"wildcard", bson.D{{"path", "password"}, {"query", "*"}, {"allowAnalyzedField", true}}}}}}}}}}
23
	// run pipeline
24
	cursor, err := collection.Aggregate(context.TODO(), mongo.Pipeline{searchStage})
25
	if err != nil {
26
		panic(err)
27
	}
28
	// print results
29
	var results []bson.D
30
	if err = cursor.All(context.TODO(), &results); err != nil {
31
		panic(err)
32
	}
33
	for _, result := range results {
34
		fmt.Println(result)
35
	}
36
}

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the following command to query your collection.

go run isnull-query.go

[
  {_id ObjectID("63c84590975d5c7c8bf1ebed")}
  {name Andre Robinson}
  {email andre.robinson@example.com}
  {password <nil>}
]

Ensure that your CLASSPATH contains the following libraries.

Create a file named IsNullQuery.java.

Copy and paste the following code into the IsNullQuery.java file.

The code example performs the following tasks:

• Imports mongodb packages and dependencies.

• Establishes a connection to your Atlas cluster.

• Uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

  • must clause to find only documents that have the password field.

  • mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

• Iterates over the cursor to print the documents that match the query.

1
import static com.mongodb.client.model.Aggregates.limit;
2
import static com.mongodb.client.model.Aggregates.project;
3
import static com.mongodb.client.model.Projections.excludeId;
4
import static com.mongodb.client.model.Projections.fields;
5
import static com.mongodb.client.model.Projections.include;
6
import com.mongodb.client.MongoClient;
7
import com.mongodb.client.MongoClients;
8
import com.mongodb.client.MongoCollection;
9
import com.mongodb.client.MongoDatabase;
10
import org.bson.Document;
11
import java.util.Arrays;
12
13
public class IsNullQuery {
14
15
    public static void main(String[] args) {
16
        // connect to your Atlas cluster
17
        String uri = "<connection-string>";
18
        
19
        try (MongoClient mongoClient = MongoClients.create(uri)) {
20
            // set namespace
21
            MongoDatabase database = mongoClient.getDatabase("sample_mflix");
22
            MongoCollection<Document> collection = database.getCollection("users");
23
            
24
            // define pipeline
25
            Document agg = new Document("$search",
26
                new Document ("index", "null-check-tutorial")
27
                .append("compound", 
28
                    new Document("must", 
29
                    new Document("exists", 
30
                    new Document("path", "password")))
31
                                .append("mustNot", 
32
                    new Document("wildcard", 
33
                    new Document("path", "password")
34
                                        .append("query", "*")
35
                                        .append("allowAnalyzedField", true)))));
36
            // run pipeline and print results
37
            collection.aggregate(Arrays.asList(agg)).forEach(doc -> System.out.println(doc.toJson()));
38
        }
39
    }
40
}

package com.mongodb.drivers;

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Compile and run the IsNullQuery.java file.

javac IsNullQuery.java
java IsNullQuery

{
  "_id": {"$oid": "63c84590975d5c7c8bf1ebed"},
  "name": "Andre Robinson",
  "email": "andre.robinson@example.com",
  "password": null
}

Ensure that you add the following dependency to your project.

Create a file named IsNullQuery.kt.

Copy and paste the following code into the IsNullQuery.kt file.

The code example performs the following tasks:

• Imports mongodb packages and dependencies.

• Establishes a connection to your Atlas cluster.

• Uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

  • must clause to find only documents that have the password field.

  • mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

• Iterates over the cursor to print the documents that match the query.

1
import com.mongodb.kotlin.client.coroutine.MongoClient
2
import kotlinx.coroutines.runBlocking
3
import org.bson.Document
4
5
fun main() {
6
    // connect to your Atlas cluster
7
    val uri = "<connection-string>"
8
    val mongoClient = MongoClient.create(uri)
9
10
    // set namespace
11
    val database = mongoClient.getDatabase("sample_mflix")
12
    val collection = database.getCollection<Document>("users")
13
14
    runBlocking {
15
        // define pipeline
16
        val agg = Document(
17
            "\$search",
18
            Document("index", "null-check-tutorial")
19
                .append(
20
                    "compound",
21
                    Document(
22
                        "must",
23
                        Document(
24
                            "exists",
25
                            Document("path", "password")
26
                        )
27
                    )
28
                        .append(
29
                            "mustNot",
30
                            Document(
31
                                "wildcard",
32
                                Document("path", "password")
33
                                    .append("query", "*")
34
                                    .append("allowAnalyzedField", true)
35
                            )
36
                        )
37
                )
38
        )
39
40
        // run pipeline and print results
41
        val resultsFlow = collection.aggregate<Document>(
42
            listOf(agg)
43
        )
44
        resultsFlow.collect { println(it) }
45
    }
46
47
    mongoClient.close()
48
}

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the IsNullQuery.kt file.

When you run the IsNullQuery.kt program in your IDE, it prints the following document:

Document{{_id=65e0cea967e8cd48b48be599, name=Andre Robinson, email=andre.robinson@example.com, password=null}}

Create a file named isnull-query.js.

Copy and paste the following code into the isnull-query.js file.

The code example performs the following tasks:

• Imports mongodb, MongoDB's Node.js driver.

• Creates an instance of the MongoClient class to establish a connection to your Atlas cluster.

• Uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

  • must clause to find only documents that have the password field.

  • mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

• Iterates over the cursor to print the documents that match the query.

1
const { MongoClient } = require("mongodb");
2
3
// connect to your Atlas cluster
4
const uri =
5
    "<connection-string>";
6
7
const client = new MongoClient(uri);
8
9
async function run() {
10
    try {
11
        await client.connect();
12
13
        // set namespace
14
        const database = client.db("sample_mflix");
15
        const coll = database.collection("users");
16
17
        // define pipeline
18
        const agg = [{
19
            '$search': {
20
              'index': 'null-check-tutorial',
21
              'compound': {
22
                'must': {
23
                  'exists': {
24
                    'path': 'password'
25
                  }
26
                },
27
                'mustNot': {
28
                  'wildcard': {
29
                    'path': 'password', 
30
                    'query': '*', 
31
                    'allowAnalyzedField': true
32
                  }
33
                }
34
              }
35
            }
36
        }];
37
        // run pipeline
38
        const result = await coll.aggregate(agg);
39
40
        // print results
41
        await result.forEach((doc) => console.log(doc));
42
    } finally {
43
        await client.close();
44
    }
45
}
46
run().catch(console.dir);

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the following command to query your collection.

node isnull-query.js

{
  _id: ObjectId("63c84590975d5c7c8bf1ebed"),
  name: 'Andre Robinson',
  email: 'andre.robinson@example.com',
  password: null
}

Create a file named isnull-query.py.

Copy and paste the following code into the isnull-query.py file.

The code example performs the following tasks:

• Imports pymongo, MongoDB's Python driver, and the dns module, which is required to connect pymongo to Atlas using a DNS seed list connection string.

• Creates an instance of the MongoClient class to establish a connection to your Atlas cluster.

• Uses the $search pipeline stage to query the collection. The query uses the following compound operator clauses:

  • must clause to find only documents that have the password field.

  • mustNot clause with the wildcard operator to exclude all documents that have any value in the password field.

• Iterates over the cursor to print the documents that match the query.

1
import pymongo
2
3
# connect to your Atlas cluster
4
client = pymongo.MongoClient('<connection-string>')
5
6
# define pipeline
7
pipeline = [{
8
    '$search': {
9
        'index': 'null-check-tutorial',
10
        'compound': {
11
            'must': {
12
                'exists': {
13
                    'path': 'password'
14
                }
15
            },
16
            'mustNot': {
17
                'wildcard': {
18
                    'path': 'password', 
19
                    'query': '*', 
20
                    'allowAnalyzedField': True
21
                }
22
            }
23
        }
24
    }
25
}]
26
# run pipeline
27
result = client["sample_mflix"]["users"].aggregate(pipeline)
28
29
# print results
30
for i in result:
31
    print(i)

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the following command to query your collection.

python isnull-query.py

{
  '_id': ObjectId('63c84590975d5c7c8bf1ebed'),
  'name': 'Andre Robinson',
  'email': 'andre.robinson@example.com',
  'password': None
}

In Atlas, go to the Clusters page for your project.

1. If it is not already displayed, select the organization that contains your desired project from the Organizations menu in the navigation bar.

2. If it is not already displayed, select your desired project from the Projects menu in the navigation bar.

3. If the Clusters page is not already displayed, click Database in the sidebar.

Go to the Atlas Search page for your cluster.

You can go the Atlas Search page from the sidebar, the Data Explorer, or your cluster details page.

Go to the Search Tester.

Click the Query button to the right of the index to query.

View and edit the query syntax.

Click Edit Query to view a default query syntax sample in JSON format.

Run an Atlas Search query with the compound operator to search the users collection.

The following query searches only for users that do not have a null value in the password field. The query specifies the following:

• Find all documents that don't have a null value in the password field using the wildcard operator.

• Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

  Note

  Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

[
  {
    $search: {
      "index": "null-check-tutorial",
      "compound": {
        "should": [{
          "wildcard": {
            "path": "password",
            "query": "*",
            "allowAnalyzedField": true
            }
          },
          {
            "compound": {
              "mustNot": {
                "exists": {
                  "path": "password"
                }
              },
              "score": { "constant": { "value": 2 } }
            }
          }
        ]
      }
    }
  }
]

SCORE: 2  _id:  "64a6e2f2bceafd4df9153eaf”
  name: "Laura Garcia"
  email: "lgarcia@example.net"
SCORE: 1  _id:  "59b99db4cfa9a34dcd7885b6”
  name: "Ned Stark"
  email: "sean_bean@gameofthron.es"
  password: "$2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu"
SCORE: 1  _id:  "59b99db4cfa9a34dcd7885b7”
  name: "Robert Baratheon"
  email: "mark_addy@gameofthron.es"
  password: "$2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y"
SCORE: 1  _id:  "59b99db5cfa9a34dcd7885b8”
  name: "Jaime Lannister"
  email: "nikolaj_coster-waldau@gameofthron.es"
  password: "$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK"
SCORE: 1  _id:  "59b99db5cfa9a34dcd7885b9”
  name: "Catelyn Stark"
  email: "michelle_fairley@gameofthron.es"
  password: "$2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS"
SCORE: 1  _id:  "59b99db6cfa9a34dcd7885ba”
  name: "Cersei Lannister"
  email: "lena_headey@gameofthron.es"
  password: "$2b$12$FExjgr7CLhNCa.oUsB9seub8mqcHzkJCFZ8heMc8CeIKOZfeTKP8m"
SCORE: 1  _id:  "59b99db6cfa9a34dcd7885bb”
  name: "Daenerys Targaryen"
  email: "emilia_clarke@gameofthron.es"
  password: "$2b$12$NzpbWHdMytemLtTfFKduHenr2NZ.rvxIKuYM4AWLTFaUShxbJ.G3q"
SCORE: 1  _id:  "59b99db6cfa9a34dcd7885bc”
  name: "Jorah Mormont"
  email: "iain_glen@gameofthron.es"
  password: "$2b$12$K8bKkwnpkrjsBPzASZxO/.yj7d9kvupiVtO6JA3Xl106AKXr3pXFK"
SCORE: 1  _id:  "59b99db7cfa9a34dcd7885bd”
  name: "Petyr Baelish"
  email: "aidan_gillen@gameofthron.es"
  password: "$2b$12$qM.YvmiekyYYY7p7phpK3OicbRCDkN7ESwYAnG/o9YnfHC0Mhkmbi"
SCORE: 1  _id:  "59b99db8cfa9a34dcd7885be”
  name: "Viserys Targaryen"
  email: "harry_lloyd@gameofthron.es"
  password: "$2b$12$cpwVmU4DyuQxgwpdrVJhaudzbKOXlHRbf.tpCuHjpAqonuoyvvEG6"

Connect to your cluster in mongosh.

Open mongosh in a terminal window and connect to your cluster. For detailed instructions on connecting, see Connect via mongosh.

Use the sample_mflix database.

Run the following command at mongosh prompt:

use sample_mflix

Run an Atlas Search query with the compound and wildcard operators on the users collection.

The following query searches only for users that do not have a null value in the password field. The query includes the following pipeline stages:

• $search pipeline stage to query the collection. The query uses the compound operator should clause to do the following:

  • Find all documents that don't have a null value in the password field using the wildcard operator.

  • Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

    Note

    Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

• $limit stage to limit the output to 5 results.

• $project stage to:

  • Exclude all fields except name and password.

  • Add a score field.

db.users.aggregate([
    {
        $search: {
            "index": "null-check-tutorial",
            "compound": {
                "should": [{
                        "wildcard": {
                            "path": "password",
                            "query": "*",
                            "allowAnalyzedField": true
                        }
                    },
                    {
                        "compound": {
                            "mustNot": {
                                "exists": {
                                    "path": "password"
                                }
                            },
                            "score": { "constant": { "value": 2 } }
                        }
                    }
                ]
            }
        }
    }, 
    {
    "$limit": 5
    },
    {
        "$project": {
            "_id": 0, 
            "name": 1, 
            "password": 1,
            "score": { "$meta": "searchScore" }
        }
    }
])

{ name: 'Laura Garcia', score: 2 },
{
  name: 'Ned Stark',
  password: '$2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu',
  score: 1
},
{
  name: 'Robert Baratheon',
  password: '$2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y',
  score: 1
},
{
  name: 'Jaime Lannister',
  password: '$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK',
  score: 1
},
{
  name: 'Catelyn Stark',
  password: '$2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS',
  score: 1
}

Connect to your cluster in MongoDB Compass.

Open MongoDB Compass and connect to your cluster. For detailed instructions on connecting, see Connect via Compass.

Use the users collection in the sample_mflix database.

On the Databases screen, click the sample_mflix database, then click the users collection.

Run an Atlas Search query with the compound and wildcard operators on the users collection.

The query uses the following pipeline stages:

• $search pipeline stage to query the collection. The query uses the compound operator should clause to do the following:

  • Find all documents that don't have a null value in the password field using the wildcard operator.

  • Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

    Note

    Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

• $limit stage to limit the output to 5 results.

• $project stage to:

  • Exclude all fields except name and password.

  • Add a score field.

To run this query in MongoDB Compass:

1. Click the Aggregations tab.

2. Click Select..., then configure each of the following pipeline stages by selecting the stage from the dropdown and adding the query for that stage. Click Add Stage to add additional stages.

   Pipeline Stage	Query
   $search	{ "index": "null-check-tutorial", "compound": { "should": [{ "wildcard": { "path": "password", "query": "*", "allowAnalyzedField": true } }, { "compound": { "mustNot": { "exists": { "path": "password" } }, "score": { "constant": { "value": 2 } } } } ] } }
   $limit	5
   $project	{ "_id": 0, "name": 1, "password": 1, "score": { "$meta": "searchScore" } }

If you enabled Auto Preview, MongoDB Compass displays the following documents next to the $project pipeline stage:

1
{ name: 'Laura Garcia', score: 2 },
2
{
3
  name: 'Ned Stark',
4
  password: '$2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu',
5
  score: 1
6
},
7
{
8
  name: 'Robert Baratheon',
9
  password: '$2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y',
10
  score: 1
11
},
12
{
13
  name: 'Jaime Lannister',
14
  password: '$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK',
15
  score: 1
16
},
17
{
18
  name: 'Catelyn Stark',
19
  password: '$2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS',
20
  score: 1
21
}

Run the following commands to initialize your project.

1. Create a new directory called not-null-query-example and initialize your project with the dotnet new command.

   mkdir not-null-query-example
   cd not-null-query-example
   dotnet new console

2. Add the .NET/C# Driver to your project as a dependency.

   dotnet add package MongoDB.Driver

Replace the contents of the Program.cs file with the following code.

The code example performs the following tasks:

• Imports mongodb packages and dependencies.

• Establishes a connection to your Atlas cluster.

• Uses the following pipeline stages:

  • $search pipeline stage to query the collection. The query uses the compound operator should clause to do the following:

    • Find all documents that don't have a null value in the password field using the wildcard operator.

    • Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

      Note

      Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

  • $limit stage to limit the output to 5 results.

  • $project stage to:

    • Exclude all fields except name and password.

    • Add a score field.

• Iterates over the cursor to print the documents that match the query.

1
using MongoDB.Bson;
2
using MongoDB.Bson.Serialization.Attributes;
3
using MongoDB.Bson.Serialization.Conventions;
4
using MongoDB.Driver;
5
using MongoDB.Driver.Search;
6
7
public class NotNullQuery 
8
{
9
    private const string MongoConnectionString = "<connection-string>";
10
    
11
    public static void Main(string[] args) 
12
    {
13
        // allow automapping of the camelCase database fields to our UserDocument
14
        var camelCaseConvention = new ConventionPack { new CamelCaseElementNameConvention() };
15
        ConventionRegistry.Register("CamelCase", camelCaseConvention, type => true);
16
17
        // connect to your Atlas cluster
18
        var mongoClient = new MongoClient(MongoConnectionString);
19
        var mflixDatabase = mongoClient.GetDatabase("sample_mflix");
20
        var usersCollection = mflixDatabase.GetCollection<UserDocument>("users");
21
22
        // define a builder for the search score
23
        var scoreBuilder = new SearchScoreDefinitionBuilder<UserDocument>();
24
25
        // define and run pipeline
26
        var results = usersCollection.Aggregate()
27
            .Search(Builders<UserDocument>.Search.Compound()
28
                .Should(Builders<UserDocument>.Search.Wildcard(user => user.Password, "*", true))
29
                .Should(Builders<UserDocument>.Search.Compound(scoreBuilder.Constant(2))
30
                .MustNot(Builders<UserDocument>.Search.Exists(user => user.Password))),
31
                indexName: "null-check-tutorial")
32
                .Limit(5)
33
                .Project<UserDocument>(Builders<UserDocument>.Projection
34
                    .Include(user => user.Name)
35
                    .Include(user => user.Password)
36
                    .MetaSearchScore(user => user.Score)
37
                    .Exclude(user => user.Id))
38
                .ToList();
39
40
        // print results
41
        foreach (var user in results) 
42
        {
43
            Console.WriteLine(user.ToJson());
44
        }
45
    }
46
}
47
48
[BsonIgnoreExtraElements]
49
public class UserDocument 
50
{
51
    [BsonIgnoreIfDefault]
52
    public string Id { get; set; }
53
    public string Name { get; set; }
54
    [BsonIgnoreIfDefault]
55
    public string Password { get; set; }
56
    public double Score { get; set; }
57
}

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the following command to query your collection.

After you run the dotnet run command, the program prints the following documents to your terminal:

dotnet run Program.cs

{ "name" : "Laura Garcia", "score" : 2.0 }
{ "name" : "Ned Stark", "password" : "$2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu", "score" : 1.0 }
{ "name" : "Robert Baratheon", "password" : "$2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y", "score" : 1.0 }
{ "name" : "Jaime Lannister", "password" : "$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK", "score" : 1.0 }
{ "name" : "Catelyn Stark", "password" : "$2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS", "score" : 1.0 }

Create a file named notnull-query.go.

Copy and paste the following code into the notnull-query.go file.

The code example performs the following tasks:

• Imports mongodb packages and dependencies.

• Establishes a connection to your Atlas cluster.

• Uses the following pipeline stages:

  • $search pipeline stage to query the collection. The query uses the compound operator should clause to do the following:

    • Find all documents that don't have a null value in the password field using the wildcard operator.

    • Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

      Note

      Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

  • $limit stage to limit the output to 5 results.

  • $project stage to:

    • Exclude all fields except name and password.

    • Add a score field.

• Iterates over the cursor to print the documents that match the query.

1
package main
2
3
import (
4
	"context"
5
	"fmt"
6
7
	"go.mongodb.org/mongo-driver/bson"
8
	"go.mongodb.org/mongo-driver/mongo"
9
	"go.mongodb.org/mongo-driver/mongo/options"
10
)
11
12
func main() {
13
	// connect to your Atlas cluster
14
	client, err := mongo.Connect(context.TODO(), options.Client().ApplyURI("<connection-string>"))
15
	if err != nil {
16
		panic(err)
17
	}
18
	defer client.Disconnect(context.TODO())
19
	// set namespace
20
	collection := client.Database("sample_mflix").Collection("users")
21
	// define pipeline stages
22
	searchStage := bson.D{{"$search", bson.D{
23
		{"index", "null-check-tutorial"},
24
		{"compound", bson.D{{
25
			"should", bson.A{bson.D{{
26
				"wildcard", bson.D{
27
					{"path", "password"},
28
					{"query", "*"},
29
					{"allowAnalyzedField", true}}}}, bson.D{{
30
				"compound", bson.D{{
31
					"mustNot", bson.D{{
32
						"exists", bson.D{
33
							{"path", "password"}}}}},
34
					{"score", bson.D{{"constant", bson.D{{"value", 2}}}}},
35
				}}}}}}}}}}
36
	projectStage := bson.D{{"$project", bson.D{{"name", 1}, {"password", 1}, {"_id", 0}, {"score", bson.D{{"$meta", "searchScore"}}}}}}
37
	limitStage := bson.D{{"$limit", 5}}
38
	// run pipeline
39
	cursor, err := collection.Aggregate(context.TODO(), mongo.Pipeline{searchStage, projectStage, limitStage})
40
	if err != nil {
41
		panic(err)
42
	}
43
	// print results
44
	var results []bson.D
45
	if err = cursor.All(context.TODO(), &results); err != nil {
46
		panic(err)
47
	}
48
	for _, result := range results {
49
		fmt.Println(result)
50
	}
51
}

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the following command to query your collection.

go run notnull-query.go

[{name Laura Garcia} {score 2}]
[{name Ned Stark} {password $2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu} {score 1}]
[{name Robert Baratheon} {password $2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y} {score 1}]
[{name Jaime Lannister} {password $2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK} {score 1}]
[{name Catelyn Stark} {password $2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS} {score 1}]

Ensure that your CLASSPATH contains the following libraries.

Create a file named NotNullQuery.java.

Copy and paste the following code into the NotNullQuery.java file.

The code example performs the following tasks:

• Imports mongodb packages and dependencies.

• Establishes a connection to your Atlas cluster.

• Uses the following pipeline stages:

  • $search pipeline stage to query the collection. The query uses the compound operator should clause to do the following:

    • Find all documents that don't have a null value in the password field using the wildcard operator.

    • Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

      Note

      Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

  • $limit stage to limit the output to 5 results.

  • $project stage to:

    • Exclude all fields except name and password.

    • Add a score field.

• Iterates over the cursor to print the documents that match the query.

1
import static com.mongodb.client.model.Aggregates.limit;
2
import static com.mongodb.client.model.Aggregates.project;
3
import static com.mongodb.client.model.Projections.*;
4
import com.mongodb.client.MongoClient;
5
import com.mongodb.client.MongoClients;
6
import com.mongodb.client.MongoCollection;
7
import com.mongodb.client.MongoDatabase;
8
import org.bson.Document;
9
import java.util.Arrays;
10
11
public class NotNullQuery {
12
13
    public static void main(String[] args) {
14
        // connect to your Atlas cluster
15
        String uri = "<connection-string>";
16
17
        try (MongoClient mongoClient = MongoClients.create(uri)) {
18
            // set namespace
19
            MongoDatabase database = mongoClient.getDatabase("sample_mflix");
20
            MongoCollection<Document> collection = database.getCollection("users");
21
22
            // define pipeline
23
            Document agg = new Document("$search",
24
                new Document ("index", "null-check-tutorial")
25
                .append("compound",
26
                    new Document("should", Arrays.asList(
27
                        new Document("wildcard",
28
                                new Document("path", "password")
29
                                .append("query", "*")
30
                                .append("allowAnalyzedField", true)),
31
                                    new Document("compound",
32
                                            new Document("mustNot",
33
                                                    new Document("exists",
34
                                                            new Document("path", "password")))
35
                                                    .append("score",
36
                                                            new Document("constant",
37
                                                                    new Document("value", 2L))))))));
38
39
            // run pipeline and print results
40
            collection.aggregate(Arrays.asList(agg,
41
                            limit(5),
42
                            project(fields(
43
                                    excludeId(),
44
                                    include("name", "password"),
45
                                    computed("score", new Document("$meta", "searchScore"))))))
46
                    .forEach(doc -> System.out.println(doc.toJson()));
47
        }
48
    }
49
}

package com.mongodb.drivers;

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Compile and run the NotNullQuery.java file.

javac NotNullQuery.java
java NotNullQuery

{"name": "Laura Garcia", "score": 2.0}
{"name": "Ned Stark", "password": "$2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu", "score": 1.0}
{"name": "Robert Baratheon", "password": "$2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y", "score": 1.0}
{"name": "Jaime Lannister", "password": "$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK", "score": 1.0}
{"name": "Catelyn Stark", "password": "$2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS", "score": 1.0}

Ensure that you add the following dependency to your project.

Create a file named NotNullQuery.kt.

Copy and paste the following code into the NotNullQuery.kt file.

The code example performs the following tasks:

• Imports mongodb packages and dependencies.

• Establishes a connection to your Atlas cluster.

• Uses the following pipeline stages:

  • $search pipeline stage to query the collection. The query uses the compound operator should clause to do the following:

    • Find all documents that don't have a null value in the password field using the wildcard operator.

    • Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

      Note

      Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

  • $limit stage to limit the output to 5 results.

  • $project stage to:

    • Exclude all fields except name and password.

    • Add a score field.

• Iterates over the cursor to print the documents that match the query.

1
import com.mongodb.client.model.Aggregates.limit
2
import com.mongodb.client.model.Aggregates.project
3
import com.mongodb.client.model.Projections.*
4
import com.mongodb.kotlin.client.coroutine.MongoClient
5
import kotlinx.coroutines.runBlocking
6
import org.bson.Document
7
8
fun main() {
9
    // connect to your Atlas cluster
10
    val uri = "<connection-string>"
11
    val mongoClient = MongoClient.create(uri)
12
13
    // set namespace
14
    val database = mongoClient.getDatabase("sample_mflix")
15
    val collection = database.getCollection<Document>("users")
16
17
    runBlocking {
18
        // define pipeline
19
        val agg = Document(
20
            "\$search",
21
            Document("index", "null-check-tutorial")
22
                .append(
23
                    "compound",
24
                    Document(
25
                        "should", listOf(
26
                            Document(
27
                                "wildcard",
28
                                Document("path", "password")
29
                                    .append("query", "*")
30
                                    .append("allowAnalyzedField", true)
31
                            ),
32
                            Document(
33
                                "compound",
34
                                Document(
35
                                    "mustNot",
36
                                    Document(
37
                                        "exists",
38
                                        Document("path", "password")
39
                                    )
40
                                )
41
                                    .append(
42
                                        "score",
43
                                        Document(
44
                                            "constant",
45
                                            Document("value", 2L)
46
                                        )
47
                                    )
48
                            )
49
                        )
50
                    )
51
                )
52
        )
53
54
        // run pipeline and print results
55
        val resultsFlow = collection.aggregate<Document>(
56
            listOf(
57
                agg,
58
                limit(5),
59
                project(fields(
60
                    excludeId(),
61
                    include("name", "password"),
62
                    computed("score", Document("\$meta", "searchScore"))))
63
            )
64
        )
65
        resultsFlow.collect { println(it) }
66
    }
67
68
    mongoClient.close()
69
}

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the NotNullQuery.kt file.

When you run the NotNullQuery.kt program in your IDE, it prints the following documents:

Document{{name=Laura Garcia, score=2.0}}
Document{{name=Ned Stark, password=$2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu, score=1.0}}
Document{{name=Robert Baratheon, password=$2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y, score=1.0}}
Document{{name=Jaime Lannister, password=$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK, score=1.0}}
Document{{name=Catelyn Stark, password=$2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS, score=1.0}}

Create a file named notnull-query.js.

Copy and paste the following code into the notnull-query.js file.

The code example performs the following tasks:

• Imports mongodb, MongoDB's Node.js driver.

• Creates an instance of the MongoClient class to establish a connection to your Atlas cluster.

• Uses the following pipeline stages:

  • $search pipeline stage to query the collection. The query uses the compound operator should clause to do the following:

    • Find all documents that don't have a null value in the password field using the wildcard operator.

    • Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

      Note

      Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

  • $limit stage to limit the output to 5 results.

  • $project stage to:

    • Exclude all fields except name and password.

    • Add a score field.

• Iterates over the cursor to print the documents that match the query.

1
const { MongoClient } = require("mongodb");
2
3
// connect to your Atlas cluster
4
const uri =
5
    "<connection-string>";
6
7
const client = new MongoClient(uri);
8
9
async function run() {
10
    try {
11
        await client.connect();
12
13
        // set namespace
14
        const database = client.db("sample_mflix");
15
        const coll = database.collection("users");
16
17
        // define pipeline
18
        const agg = [
19
            {
20
                '$search': {
21
                    'index': 'null-check-tutorial',
22
                    'compound': {
23
                        'should': [{
24
                            'wildcard': {
25
                                'path': 'password', 
26
                                'query': '*', 
27
                                'allowAnalyzedField': true
28
                            }
29
                        }, 
30
                        {
31
                            'compound': {
32
                                'mustNot': {
33
                                    'exists': {
34
                                        'path': 'password'
35
                                    }
36
                                },
37
                                'score': { 'constant': { 'value': 2 } }
38
                            }
39
                        }]
40
                    }
41
                }
42
            },
43
            {    
44
                '$limit': 5   
45
            },
46
            {
47
                '$project': {'_id': 0, 'name': 1, 'password': 1, 'score': { '$meta': 'searchScore' }}
48
            }
49
        ];
50
        // run pipeline
51
        const result = await coll.aggregate(agg);
52
53
        // print results
54
        await result.forEach((doc) => console.log(doc));
55
    } finally {
56
        await client.close();
57
    }
58
}
59
run().catch(console.dir);

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the following command to query your collection.

node notnull-query.js

{ name: 'Laura Garcia', score: 2 }
{
  name: 'Ned Stark',
  password: '$2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu',
  score: 1
}
{
  name: 'Robert Baratheon',
  password: '$2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y',
  score: 1
}
{
  name: 'Jaime Lannister',
  password: '$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK',
  score: 1
}
{
  name: 'Catelyn Stark',
  password: '$2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS',
  score: 1
}

Create a file named notnull.py.

Copy and paste the following code into the notnull.py file.

The code example performs the following tasks:

• Imports pymongo, MongoDB's Python driver, and the dns module, which is required to connect pymongo to Atlas using a DNS seed list connection string.

• Creates an instance of the MongoClient class to establish a connection to your Atlas cluster.

• Uses the following pipeline stages:

  • $search pipeline stage to query the collection. The query uses the compound operator should clause to do the following:

    • Find all documents that don't have a null value in the password field using the wildcard operator.

    • Find documents that don't have the password field using the compound operator mustNot clause and replace their score with 2 using the constant option.

      Note

      Atlas Search returns documents in order from highest score to lowest. In this example, you alter the score for documents with a missing password field so that they return first. Otherwise, these documents have a score of 0 and return last. To learn more, see Scoring.

  • $limit stage to limit the output to 5 results.

  • $project stage to:

    • Exclude all fields except name and password.

    • Add a score field.

• Iterates over the cursor to print the documents that match the query.

1
import pymongo
2
3
# connect to your Atlas cluster
4
client = pymongo.MongoClient('<connection-string>')
5
6
# define pipeline
7
pipeline = [{
8
        '$search': {
9
            'index': 'null-check-tutorial',
10
            'compound': {
11
                'should': [
12
                    {
13
                        'wildcard': {
14
                            'path': 'password', 
15
                            'query': '*', 
16
                            'allowAnalyzedField': True
17
                        }
18
                    }, {
19
                        'compound': {
20
                            'mustNot': {
21
                                'exists': {
22
                                    'path': 'password'
23
                                }
24
                            }, 
25
                            'score': {
26
                                'constant': {
27
                                    'value': 2
28
                                }
29
                            }
30
                        }
31
                    }
32
                ]
33
            }
34
        }
35
    }, {
36
        '$project': {
37
            '_id': 0, 
38
            'name': 1, 
39
            'password': 1, 
40
            'score': {
41
                '$meta': 'searchScore'
42
            }
43
        }
44
    }, {
45
        '$limit': 5
46
    }
47
]
48
# run pipeline
49
result = client["sample_mflix"]["users"].aggregate(pipeline)
50
51
# print results
52
for i in result:
53
    print(i)

Before you run the sample, replace <connection-string> with your Atlas connection string. Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the following command to query your collection.

python notnull-query.py

{'name': 'Laura Garcia', 'score': 2.0}
{'name': 'Ned Stark', 'password': '$2b$12$UREFwsRUoyF0CRqGNK0LzO0HM/jLhgUCNNIJ9RJAqMUQ74crlJ1Vu', 'score': 1.0}
{'name': 'Robert Baratheon', 'password': '$2b$12$yGqxLG9LZpXA2xVDhuPnSOZd.VURVkz7wgOLY3pnO0s7u2S1ZO32y', 'score': 1.0}
{'name': 'Jaime Lannister', 'password': '$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK', 'score': 1.0}
{'name': 'Catelyn Stark', 'password': '$2b$12$fiaTH5Sh1zKNFX2i/FTEreWGjxoJxvmV7XL.qlfqCr8CwOxK.mZWS', 'score': 1.0}