How to Run Atlas Search Queries Across Multiple Collections

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 sample_mflix.movies collection.

On the Database screen, click the sample_analytics database and then click the customers collection.

Run an Atlas Search query against the collection.

The following query uses the following stages:

• $lookup to do the following:

  • Join customers and accounts collections in the sample_analytics database based on the account ID of the customers and return the matching documents from the accounts collection in an array field named purchases.

  • Use $search stage in the sub-pipeline to search for customer accounts that must have purchased both CurrencyService and InvestmentStock with preference for an order limit between 5000 to 10000.

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

• $project stage to exclude the specified fields in the results.

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
   $lookup	{ from: "accounts", localField: "accounts", foreignField: "account_id", as: "purchases", pipeline: [ { $search: { index: "lookup-with-search-tutorial", compound: { must: [ { queryString: { defaultPath: "products", query: "products: (CurrencyService AND InvestmentStock)" } } ], should: [ { range: { path: "limit", gte: 5000, lte: 10000, } } ] } } }, { $project: { _id: 0, } } ] }
   $limit	5
   $project	{ _id: 0, address: 0, birthdate: 0, username: 0, tier_and_details: 0, }

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

   name: Elizabeth Ray
   email: arroyocolton@gmail.com
   active: True
   accounts: Array (6)
   purchases: Array (3)
   name: "Lindsay Cowan"
   email: "cooperalexis@hotmail.com"
   accounts: Array (1)
   purchases: Array (empty)
   name: "Katherine David"
   email: "timothy78@hotmail.com"
   accounts: Array (5)
   urchases: Array (2)
   name: "Leslie Martinez"
   email: "tcrawford@gmail.com"
   accounts: Array (2)
   purchases: Array (empty)
   name: "Brad Cardenas"
   email: "dustin37@yahoo.com"
   accounts: Array (5)
   purchases: Array (2)

Expand your query results.

MongoDB Compass might not display all the fields inside objects and all the values inside arrays for the documents it returns in the results. To view all the fields and values, expand the field in the results.

Set up and initialize the .NET/C# project for the query.

1. Create a new directory called lookup-with-search and initialize your project with the dotnet new command.

   mkdir lookup-with-search
   cd lookup-with-search
   dotnet new console

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

   dotnet add package MongoDB.Driver

Copy and paste the query into the Program.cs file.

The following query uses the following stages:

• $lookup to do the following:

  • Join customers and accounts collections in the sample_analytics database based on the account ID of the customers and return the matching documents from the accounts collection in an array field named purchases.

  • Use $search stage in the sub-pipeline to search for customer accounts that must have purchased both CurrencyService and InvestmentStock with preference for an order limit between 5000 to 10000.

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

• $project stage to exclude the specified fields in the results.

1
using MongoDB.Bson;
2
using MongoDB.Bson.Serialization.Attributes;
3
using MongoDB.Bson.Serialization.Conventions;
4
using MongoDB.Driver;
5
using MongoDB.Driver.Core;
6
using MongoDB.Driver.Search;
7
8
public class LookupWithSearch{
9
10
  static void Main(string[] args) {// allow automapping of the camelCase database fields to our MovieDocument
11
    var camelCaseConvention = new ConventionPack { new CamelCaseElementNameConvention() };
12
    ConventionRegistry.Register("CamelCase", camelCaseConvention, type => true);
13
14
    // connect to your Atlas cluster
15
    var mongoClient = new MongoClient("<connection-string>");
16
    
17
    // define namespace
18
    var analyticsDatabase = mongoClient.GetDatabase("sample_analytics");
19
    var accountsCollection = analyticsDatabase.GetCollection<AccountDocument>("accounts");
20
    var customersCollection = analyticsDatabase.GetCollection<CustomerDocument>("customers");
21
22
    // define pipeline stages
23
    var lookupStage = new BsonDocument("$lookup", new BsonDocument{
24
      { "from", "accounts" }, { "localField", "accounts" }, { "foreignField", "account_id" }, 
25
      { "as", "purchases" }, { "pipeline", new BsonArray{
26
        new BsonDocument("$search", new BsonDocument{
27
          { "index", "lookup-with-search-tutorial" }, { "compound", new BsonDocument{
28
            { "must", new BsonArray{
29
              new BsonDocument("queryString", new BsonDocument{
30
                { "defaultPath", "products" }, { "query", "products: (CurrencyService AND InvestmentStock)" }
31
              })
32
            }}, 
33
            { "should", new BsonArray{
34
              new BsonDocument("range", new BsonDocument{
35
                { "path", "limit" }, { "gte", 5000 }, { "lte", 10000 }
36
              })
37
            }}
38
          }}
39
        })
40
      }}
41
    });
42
    var projectStage1 = new BsonDocument("$project", new BsonDocument("_id", 0));
43
    var limitStage = new BsonDocument("$limit", 5);
44
    var projectStage2 = new BsonDocument("$project", new BsonDocument{
45
      { "_id", 0 }, { "address", 0 }, { "birthdate", 0 }, { "username", 0 }, { "tier_and_details", 0 }
46
    });
47
    var aggregationPipeline = new List<BsonDocument> {lookupStage, projectStage1, limitStage, projectStage2};
48
49
    // run pipeline
50
    var results = customersCollection.Aggregate<BsonDocument>(aggregationPipeline).ToList();
51
52
    // print results
53
    foreach (var acct in results) {
54
        Console.WriteLine(acct.ToJson());
55
    }
56
  }            
57
}
58
59
// define fields in the accounts collection
60
[BsonIgnoreExtraElements]
61
public class AccountDocument {
62
    [BsonId]
63
    [BsonRepresentation(BsonType.ObjectId)]
64
    [BsonElement("_id")]
65
    public string Id { get; set; }
66
67
    [BsonElement("account_id")]
68
    public int AccountId { get; set; }
69
70
    [BsonElement("limit")]
71
    public int Limit { get; set; }
72
}
73
74
// define fields in the customers collection
75
[BsonIgnoreExtraElements]
76
public class CustomerDocument {
77
    [BsonId]
78
    [BsonRepresentation(BsonType.ObjectId)]
79
    [BsonElement("_id")]
80
    public ObjectId Id { get; set; }
81
82
    [BsonElement("name")]
83
    public string Name { get; set; }
84
85
    [BsonElement("email")]
86
    public string Email { get; set; }
87
88
    [BsonElement("active")]
89
    public bool Active { get; set; }
90
91
    [BsonElement("accounts")]
92
    public List<int> Accounts { get; set; }
93
}
94
95
// define new array field for matching documents
96
public class CustomerLookedUp: CustomerDocument{
97
    public List<CustomerDocument> Purchases { get; set; }
98
}

Replace the <connection-string> in the query and then save the file.

Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Compile and run the Program.cs file.

dotnet run lookup-with-search.csproj

{
  "name" : "Elizabeth Ray",
  "email" : "arroyocolton@gmail.com",
  "active" : true,
  "accounts" : [371138, 324287, 276528, 332179, 422649,
  387979],
  "purchases" : [
     {
       "_id" : ObjectId("5ca4bbc7a2dd94ee58162402"),
       "account_id" : 422649,
       "limit" : 10000,
       "products" : ["CurrencyService", "InvestmentStock"]
     },
     {
       "_id" : ObjectId("5ca4bbc7a2dd94ee581623a9"),
       "account_id" : 324287,
       "limit" : 10000,
       "products" : ["Commodity", "CurrencyService", "Derivatives", "InvestmentStock"]
     },
     {
       "_id" : ObjectId("5ca4bbc7a2dd94ee58162400"),
       "account_id" : 332179,
       "limit" : 10000,
       "products" : ["Commodity", "CurrencyService", "InvestmentFund", "Brokerage", "InvestmentStock"]
     }
  ]
}
{
  "name" : "Lindsay Cowan",
  "email" : "cooperalexis@hotmail.com",
  "accounts" : [116508],
  "purchases" : []
}
{
  "name" : "Katherine David",
  "email" : "timothy78@hotmail.com",
  "accounts" : [462501, 228290, 968786, 515844, 377292],
  "purchases" : [
    {
      "_id" : ObjectId("5ca4bbc7a2dd94ee581623c9"),
      "account_id" : 228290,
      "limit" : 10000,
      "products" : ["CurrencyService", "InvestmentStock", "InvestmentFund", "Brokerage"] },
    {
      "_id" : ObjectId("5ca4bbc7a2dd94ee581623cb"),
      "account_id" : 515844,
      "limit" : 10000,
      "products" : ["Commodity", "CurrencyService", "InvestmentFund", "Brokerage", "InvestmentStock"]
    }
  ]
}
{
  "name" : "Leslie Martinez",
  "email" : "tcrawford@gmail.com",
  "accounts" : [170945, 951849],
  "purchases" : []
}
{
  "name" : "Brad Cardenas",
  "email" : "dustin37@yahoo.com",
  "accounts" : [721914, 817222, 973067, 260799, 87389],
  "purchases" : [
    {
      "_id" : ObjectId("5ca4bbc7a2dd94ee581623d6"),
      "account_id" : 87389,
      "limit" : 10000,
      "products" : ["CurrencyService", "InvestmentStock"] },
    {
      "_id" : ObjectId("5ca4bbc7a2dd94ee581623d5"),
      "account_id" : 260799,
      "limit" : 10000,
      "products" : ["Brokerage", "InvestmentStock", "Commodity", "CurrencyService"]
    }
  ]
}

Create a file named lookup-with-search-query.go

Copy and paste the query into the lookup-with-search-query.go file.

The following query uses the following stages:

• $lookup to do the following:

  • Join customers and accounts collections in the sample_analytics database based on the account ID of the customers and return the matching documents from the accounts collection in an array field named purchases.

  • Use $search stage in the sub-pipeline to search for customer accounts that must have purchased both CurrencyService and InvestmentStock with preference for an order limit between 5000 to 10000.

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

• $project stage to exclude the specified fields in the results.

1
package main
2
3
import (
4
	"context"
5
	"fmt"
6
	"time"
7
8
	"go.mongodb.org/mongo-driver/v2/bson"
9
	"go.mongodb.org/mongo-driver/v2/mongo"
10
	"go.mongodb.org/mongo-driver/v2/mongo/options"
11
)
12
13
func main() {
14
	var err error
15
	// connect to the Atlas cluster
16
	ctx := context.Background()
17
	clOpts := options.Client().ApplyURI("<connection-string>").SetTimeout(5 * time.Second)
18
	client, err := mongo.Connect(clOpts)
19
	if err != nil {
20
		panic(err)
21
	}
22
	defer client.Disconnect(ctx)
23
	// set namespace
24
	collection := client.Database("sample_analytics").Collection("customers")
25
	// define pipeline
26
	lookupStage := bson.D{{Key: "$lookup", Value: bson.D{
27
		{Key: "from", Value: "accounts"},
28
		{Key: "localField", Value: "accounts"},
29
		{Key: "foreignField", Value: "account_id"},
30
		{Key: "as", Value: "purchases"},
31
		{Key: "pipeline", Value: bson.A{
32
			bson.D{
33
				{Key: "$search", Value: bson.D{
34
					{Key: "index", Value: "lookup-with-search-tutorial"},
35
					{Key: "compound", Value: bson.D{
36
						{Key: "must", Value: bson.A{
37
							bson.D{{Key: "queryString", Value: bson.D{
38
								{Key: "defaultPath", Value: "products"},
39
								{Key: "query", Value: "products: (CurrencyService AND InvestmentStock)"},
40
							}}},
41
						}},
42
						{Key: "should", Value: bson.A{
43
							bson.D{{Key: "range", Value: bson.D{
44
								{Key: "path", Value: "limit"},
45
								{Key: "gte", Value: 5000},
46
								{Key: "lte", Value: 10000},
47
							}}},
48
						}},
49
					}},
50
				}},
51
			},
52
			bson.D{{Key: "$project", Value: bson.D{
53
				{Key: "_id", Value: 0},
54
				{Key: "address", Value: 0},
55
				{Key: "birthdate", Value: 0},
56
				{Key: "username", Value: 0},
57
				{Key: "tier_and_details", Value: 0},
58
			}}},
59
		}},
60
	}}}
61
	limitStage := bson.D{{Key: "$limit", Value: 5}}
62
	projectStage := bson.D{{Key: "$project", Value: bson.D{
63
		{Key: "name", Value: 1},
64
		{Key: "email", Value: 1},
65
		{Key: "active", Value: 1},
66
		{Key: "accounts", Value: 1},
67
		{Key: "purchases", Value: 1},
68
	}}}
69
70
	// run pipeline
71
	cursor, err := collection.Aggregate(ctx, mongo.Pipeline{lookupStage, limitStage, projectStage})
72
	if err != nil {
73
		panic(err)
74
	}
75
	// print results
76
	var results []bson.D
77
	if err = cursor.All(context.TODO(), &results); err != nil {
78
		panic(err)
79
	}
80
	for _, result := range results {
81
		fmt.Println(result)
82
	}
83
}

Replace the <connection-string> in the query and then save the file.

Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the command to query your collection.

go run lookup-with-search-query.go

{"_id":...,"name":"Gary Nichols","email":"laura34@yahoo.com","accounts":[{"$numberInt":"385397"},{"$numberInt":"337979"},{"$numberInt":"325377"},{"$numberInt":"440243"},{"$numberInt":"586395"},{"$numberInt":"86702"}],"purchases":[{"account_id":{"$numberInt":"337979"},"limit":{"$numberInt":"10000"},"products":["Brokerage","Derivatives","InvestmentStock","CurrencyService"]}]}
{"_id":...,"name":"Ashley Lopez","email":"michael16@hotmail.com","accounts":[{"$numberInt":"662207"},{"$numberInt":"816481"}],"purchases":[]}
{"_id":...,"name":"John Parks","email":"zmelton@gmail.com","accounts":[{"$numberInt":"702610"},{"$numberInt":"240640"}],"purchases":[{"account_id":{"$numberInt":"702610"},"limit":{"$numberInt":"10000"},"products":["Commodity","CurrencyService","InvestmentStock"]}]}
{"_id":...,"name":"Jennifer Lawrence","email":"scott50@yahoo.com","accounts":[{"$numberInt":"344885"},{"$numberInt":"839927"},{"$numberInt":"853542"}],"purchases":[{"account_id":{"$numberInt":"853542"},"limit":{"$numberInt":"10000"},"products":["CurrencyService","InvestmentStock"]},{"account_id":{"$numberInt":"344885"},"limit":{"$numberInt":"10000"},"products":["Commodity","InvestmentFund","CurrencyService","InvestmentStock"]},{"account_id":{"$numberInt":"839927"},"limit":{"$numberInt":"10000"},"products":["InvestmentFund","CurrencyService","Brokerage","Commodity","InvestmentStock"]}]}
{"_id":...,"name":"Jacqueline Haynes","email":"virginia36@hotmail.com","accounts":[{"$numberInt":"631901"},{"$numberInt":"814687"}],"purchases":[]}

Ensure that your CLASSPATH contains the following libraries.

Create a file named LookupWithSearchQuery.java.

Copy and paste the query into the LookupWithSearchQuery.java file.

The following query uses the following stages:

• $lookup to do the following:

  • Join customers and accounts collections in the sample_analytics database based on the account ID of the customers and return the matching documents from the accounts collection in an array field named purchases.

  • Use $search stage in the sub-pipeline to search for customer accounts that must have purchased both CurrencyService and InvestmentStock with preference for an order limit between 5000 to 10000.

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

• $project stage to exclude the specified fields in the results.

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 java.util.Arrays;
5
import com.mongodb.client.MongoClient;
6
import com.mongodb.client.MongoClients;
7
import com.mongodb.client.MongoCollection;
8
import com.mongodb.client.MongoDatabase;
9
import org.bson.Document;
10
11
public class LookupWithSearchQuery {
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_analytics");
20
      MongoCollection<Document> collection = database.getCollection("customers");
21
            
22
      // define pipeline
23
      Document agg = new Document("$lookup", 
24
        new Document("from", "accounts")
25
          .append("localField", "accounts")
26
          .append("foreignField", "account_id")
27
          .append("as", "purchases")
28
          .append("pipeline", Arrays.asList(new Document("$search", 
29
            new Document("index", "lookup-with-search-tutorial")
30
              .append("compound", 
31
              new Document("must", Arrays.asList(new Document("queryString", 
32
                new Document("defaultPath", "products")
33
                  .append("query", "products: (CurrencyService AND InvestmentStock)"))))
34
                  .append("should", Arrays.asList(new Document("range", 
35
                    new Document("path", "limit")
36
                      .append("gte", 5000L)
37
                      .append("lte", 10000L)
38
                  )))
39
              )
40
          ), 
41
          new Document("$limit", 5L),
42
          new Document("$project", 
43
          new Document("_id", 0L)
44
            .append("address", 0L)
45
            .append("birthdate", 0L)
46
            .append("username", 0L)
47
            .append("tier_and_details", 0L)
48
        )))
49
      );
50
      // run pipeline and print results
51
      collection.aggregate(Arrays.asList(agg,
52
        limit(5),
53
        project(fields(excludeId(), include("name", "email", "active", "accounts", "purchases")))
54
      ))
55
      .forEach(doc -> System.out.println(doc.toJson()));
56
    }
57
  }
58
}

package com.mongodb.drivers;

Replace the <connection-string> in the query and then save the file.

Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Compile and run the LookupWithSearchQuery.java file.

javac LookupWithSearchQuery.java
java LookupWithSearchQuery

{"name": "Elizabeth Ray", "email": "arroyocolton@gmail.com", "active": true, "accounts": [371138, 324287, 276528, 332179, 422649, 387979], "purchases": [{"account_id": 422649, "limit": 10000, "products": ["CurrencyService", "InvestmentStock"]}, {"account_id": 324287, "limit": 10000, "products": ["Commodity", "CurrencyService", "Derivatives", "InvestmentStock"]}, {"account_id": 332179, "limit": 10000, "products": ["Commodity", "CurrencyService", "InvestmentFund", "Brokerage", "InvestmentStock"]}]}
{"name": "Lindsay Cowan", "email": "cooperalexis@hotmail.com", "accounts": [116508], "purchases": []}
{"name": "Katherine David", "email": "timothy78@hotmail.com", "accounts": [462501, 228290, 968786, 515844, 377292], "purchases": [{"account_id": 228290, "limit": 10000, "products": ["CurrencyService", "InvestmentStock", "InvestmentFund", "Brokerage"]}, {"account_id": 515844, "limit": 10000, "products": ["Commodity", "CurrencyService", "InvestmentFund", "Brokerage", "InvestmentStock"]}]}
{"name": "Leslie Martinez", "email": "tcrawford@gmail.com", "accounts": [170945, 951849], "purchases": []}
{"name": "Brad Cardenas", "email": "dustin37@yahoo.com", "accounts": [721914, 817222, 973067, 260799, 87389], "purchases": [{"account_id": 87389, "limit": 10000, "products": ["CurrencyService", "InvestmentStock"]}, {"account_id": 260799, "limit": 10000, "products": ["Brokerage", "InvestmentStock", "Commodity", "CurrencyService"]}]}

Ensure that you add the following dependency to your project.

Create a file named LookupWithSearchQuery.kt.

Copy and paste the query into the LookupWithSearchQuery.kt file.

The following query uses the following stages:

• $lookup to do the following:

  • Join customers and accounts collections in the sample_analytics database based on the account ID of the customers and return the matching documents from the accounts collection in an array field named purchases.

  • Use $search stage in the sub-pipeline to search for customer accounts that must have purchased both CurrencyService and InvestmentStock with preference for an order limit between 5000 to 10000.

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

• $project stage to exclude the specified fields in the results.

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
import java.util.*
8
9
fun main() {
10
    // connect to your Atlas cluster
11
    val uri = "<connection-string>"
12
    val mongoClient = MongoClient.create(uri)
13
14
    // set namespace
15
    val database = mongoClient.getDatabase("sample_analytics")
16
    val collection = database.getCollection<Document>("customers")
17
18
    runBlocking {
19
        // define pipeline
20
        val agg = Document(
21
            "\$lookup",
22
            Document("from", "accounts")
23
                .append("localField", "accounts")
24
                .append("foreignField", "account_id")
25
                .append("as", "purchases")
26
                .append(
27
                    "pipeline", Arrays.asList(
28
                        Document(
29
                            "\$search",
30
                            Document("index", "lookup-with-search-tutorial")
31
                                .append(
32
                                    "compound",
33
                                    Document(
34
                                        "must", Arrays.asList(
35
                                            Document(
36
                                                "queryString",
37
                                                Document("defaultPath", "products")
38
                                                    .append("query", "products: (CurrencyService AND InvestmentStock)")
39
                                            )
40
                                        )
41
                                    )
42
                                        .append(
43
                                            "should", Arrays.asList(
44
                                                Document(
45
                                                    "range",
46
                                                    Document("path", "limit")
47
                                                        .append("gte", 5000)
48
                                                        .append("lte", 10000)
49
                                                )
50
                                            )
51
                                        )
52
                                )
53
                        ),
54
                        Document("\$limit", 5),
55
                        Document(
56
                            "\$project",
57
                            Document("_id", 0)
58
                                .append("address", 0)
59
                                .append("birthdate", 0)
60
                                .append("username", 0)
61
                                .append("tier_and_details", 0)
62
                        )
63
                    )
64
                )
65
        )
66
67
        // run pipeline and print results
68
        val resultsFlow = collection.aggregate<Document>(
69
            listOf(
70
                agg,
71
                limit(5),
72
                project(fields(excludeId(), include("name", "email", "active", "accounts", "purchases")))
73
            )
74
        )
75
        resultsFlow.collect { println(it) }
76
    }
77
    mongoClient.close()
78
}

Replace the <connection-string> in the query and then save the file.

Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the LookupWithSearchQuery.kt file.

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

Document{{name=Elizabeth Ray, email=arroyocolton@gmail.com, active=true, accounts=[371138, 324287, 276528, 332179, 422649, 387979], purchases=[Document{{account_id=422649, limit=10000, products=[CurrencyService, InvestmentStock]}}, Document{{account_id=324287, limit=10000, products=[Commodity, CurrencyService, Derivatives, InvestmentStock]}}, Document{{account_id=332179, limit=10000, products=[Commodity, CurrencyService, InvestmentFund, Brokerage, InvestmentStock]}}]}}
Document{{name=Lindsay Cowan, email=cooperalexis@hotmail.com, accounts=[116508], purchases=[]}}
Document{{name=Katherine David, email=timothy78@hotmail.com, accounts=[462501, 228290, 968786, 515844, 377292], purchases=[Document{{account_id=228290, limit=10000, products=[CurrencyService, InvestmentStock, InvestmentFund, Brokerage]}}, Document{{account_id=515844, limit=10000, products=[Commodity, CurrencyService, InvestmentFund, Brokerage, InvestmentStock]}}]}}
Document{{name=Leslie Martinez, email=tcrawford@gmail.com, accounts=[170945, 951849], purchases=[]}}
Document{{name=Brad Cardenas, email=dustin37@yahoo.com, accounts=[721914, 817222, 973067, 260799, 87389], purchases=[Document{{account_id=87389, limit=10000, products=[CurrencyService, InvestmentStock]}}, Document{{account_id=260799, limit=10000, products=[Brokerage, InvestmentStock, Commodity, CurrencyService]}}]}}

Create a file named lookup-with-search-query.js.

Copy and paste the sample query into the lookup-with-search-query.js file.

The following query uses the following stages:

• $lookup to do the following:

  • Join customers and accounts collections in the sample_analytics database based on the account ID of the customers and return the matching documents from the accounts collection in an array field named purchases.

  • Use $search stage in the sub-pipeline to search for customer accounts that must have purchased both CurrencyService and InvestmentStock with preference for an order limit between 5000 to 10000.

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

• $project stage to exclude the specified fields in the results.

1
const MongoClient = require("mongodb").MongoClient;
2
const assert = require("assert");
3
4
const agg = [
5
    {
6
      '$lookup': {
7
        'from': 'accounts', 
8
        'localField': 'accounts', 
9
        'foreignField': 'account_id', 
10
        'as': 'purchases', 
11
        'pipeline': [
12
          {
13
            '$search': {
14
              'index': 'lookup-with-search-tutorial', 
15
              'compound': {
16
                'must': [
17
                  {
18
                    'queryString': {
19
                      'defaultPath': 'products', 
20
                      'query': 'products: (CurrencyService AND InvestmentStock)'
21
                    }
22
                  }
23
                ], 
24
                'should': [
25
                  {
26
                    'range': {
27
                      'path': 'limit', 
28
                      'gte': 5000, 
29
                      'lte': 10000
30
                    }
31
                  }
32
                ]
33
              }
34
            }
35
          }, {
36
            '$project': {
37
              '_id': 0
38
            }
39
          }
40
        ]
41
      }
42
    }, {
43
      '$limit': 5
44
    }, {
45
      '$project': {
46
        '_id': 0, 
47
        'address': 0, 
48
        'birthdate': 0, 
49
        'username': 0, 
50
        'tier_and_details': 0
51
      }
52
    }
53
  ];
54
55
MongoClient.connect(
56
  "<connection-string>",
57
  { useNewUrlParser: true, useUnifiedTopology: true },
58
  async function (connectErr, client) {
59
    assert.equal(null, connectErr);
60
    const coll = client.db("sample_analytics").collection("customers");
61
    let cursor = await coll.aggregate(agg);
62
    await cursor.forEach((doc) => console.log(doc));
63
    client.close();
64
  }
65
);

Replace the <connection-string> in the query and then save the file.

Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Query your collection.

Run the following command to query your collection:

node lookup-with-search-query.js

{
  name: 'Elizabeth Ray',
  email: 'arroyocolton@gmail.com',
  active: true,
  accounts: [ 371138, 324287, 276528, 332179, 422649, 387979 ],
  purchases: [
    { account_id: 422649, limit: 10000, products: [Array] },
    { account_id: 324287, limit: 10000, products: [Array] },
   { account_id: 332179, limit: 10000, products: [Array] }
  ]
}
{
  name: 'Lindsay Cowan',
  email: 'cooperalexis@hotmail.com',
  accounts: [ 116508 ],
  purchases: []
}
{
  name: 'Katherine David',
  email: 'timothy78@hotmail.com',
  accounts: [ 462501, 228290, 968786, 515844, 377292 ],
  purchases: [
    { account_id: 228290, limit: 10000, products: [Array] },
    { account_id: 515844, limit: 10000, products: [Array] }
  ]
}
{
  name: 'Leslie Martinez',
  email: 'tcrawford@gmail.com',
  accounts: [ 170945, 951849 ],
  purchases: []
}
{
  name: 'Brad Cardenas',
  email: 'dustin37@yahoo.com',
  accounts: [ 721914, 817222, 973067, 260799, 87389 ],
  purchases: [
    { account_id: 87389, limit: 10000, products: [Array] },
    { account_id: 260799, limit: 10000, products: [Array] }
  ]
}

Create a file named lookup-with-search-query.py.

Copy and paste the query into the lookup-with-search-query.py file.

The following query uses the following stages:

• $lookup to do the following:

  • Join customers and accounts collections in the sample_analytics database based on the account ID of the customers and return the matching documents from the accounts collection in an array field named purchases.

  • Use $search stage in the sub-pipeline to search for customer accounts that must have purchased both CurrencyService and InvestmentStock with preference for an order limit between 5000 to 10000.

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

• $project stage to exclude the specified fields in the results.

1
import pymongo
2
3
# connect to your Atlas cluster
4
client = pymongo.MongoClient('<connection-string>')
5
6
# define pipeline
7
pipeline = [
8
  {
9
    '$lookup': {
10
      'from': 'accounts', 
11
      'localField': 'accounts', 
12
      'foreignField': 'account_id', 
13
      'as': 'purchases', 
14
      'pipeline': [
15
        {
16
          '$search': {
17
            'index': 'lookup-with-search-tutorial', 
18
            'compound': {
19
              'must': [
20
                {
21
                  'queryString': {
22
                    'defaultPath': 'products', 
23
                    'query': 'products: (CurrencyService AND InvestmentStock)'
24
                  }
25
                }
26
              ], 
27
              'should': [
28
                {
29
                  'range': {
30
                    'path': 'limit', 
31
                    'gte': 5000, 
32
                    'lte': 10000
33
                  }
34
                }
35
              ]
36
            }
37
          }
38
        }, 
39
        { '$project': { '_id': 0 }  }
40
      ]
41
    }
42
  }, 
43
  { '$limit': 5 }, 
44
  {
45
    '$project': {
46
      '_id': 0, 
47
      'address': 0, 
48
      'birthdate': 0, 
49
      'username': 0, 
50
      'tier_and_details': 0
51
    }
52
  }
53
]
54
55
# run pipeline
56
result = client['sample_analytics']['customers'].aggregate(pipeline)
57
58
# print results
59
for i in result:
60
    print(i)

Replace the <connection-string> in the query and then save the file.

Ensure that your connection string includes your database user's credentials. To learn more, see Connect via Drivers.

Run the command to query your collection.

python lookup-with-search-query.py

{'name': 'Elizabeth Ray', 'email': 'arroyocolton@gmail.com', 'active': True, 'accounts': [371138, 324287, 276528, 332179, 422649, 387979], 'purchases': [{'account_id': 422649, 'limit': 10000, 'products': ['CurrencyService', 'InvestmentStock']}, {'account_id': 324287, 'limit': 10000, 'products': ['Commodity', 'CurrencyService', 'Derivatives', 'InvestmentStock']}, {'account_id': 332179, 'limit': 10000, 'products': ['Commodity', 'CurrencyService', 'InvestmentFund', 'Brokerage', 'InvestmentStock']}]}
{'name': 'Lindsay Cowan', 'email': 'cooperalexis@hotmail.com', 'accounts': [116508], 'purchases': []}
{'name': 'Katherine David', 'email': 'timothy78@hotmail.com', 'accounts': [462501, 228290, 968786, 515844, 377292], 'purchases': [{'account_id': 228290, 'limit': 10000, 'products': ['CurrencyService', 'InvestmentStock', 'InvestmentFund', 'Brokerage']}, {'account_id': 515844, 'limit': 10000, 'products': ['Commodity', 'CurrencyService', 'InvestmentFund', 'Brokerage', 'InvestmentStock']}]}
{'name': 'Leslie Martinez', 'email': 'tcrawford@gmail.com', 'accounts': [170945, 951849], 'purchases': []}
{'name': 'Brad Cardenas', 'email': 'dustin37@yahoo.com', 'accounts': [721914, 817222, 973067, 260799, 87389], 'purchases': [{'account_id': 87389, 'limit': 10000, 'products': ['CurrencyService', 'InvestmentStock']}, {'account_id': 260799, 'limit': 10000, 'products': ['Brokerage', 'InvestmentStock', 'Commodity', 'CurrencyService']}]}

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.

Switch to the sample_training database.

Run the following command at mongosh prompt:

use sample_training

switched to db sample_training

Run the following $unionWith with an Atlas Search $search query.

The following query searches both the companies and inspections collections for the term mobile in the name and business_name fields respectively.

db.companies.aggregate([
{
  "$search": {
    "text": {
      "query": "Mobile",
      "path": "name"
    }
  }
}, {
  "$project": {
    "score": {
      "$meta": "searchScore"
    },
    "_id": 0,
    "number_of_employees": 1,
    "founded_year": 1,
    "name": 1
  }
}, {
  "$set": {
    "source": "companies"
  }
}, {
  "$limit": 3
}, {
  "$unionWith": {
    "coll": "inspections",
    "pipeline": [
      {
        "$search": {
          "text": {
            "query": "Mobile",
            "path": "business_name"
          }
        }
      }, {
        "$set": {
          "source": "inspections"
        }
      }, {
        "$project": {
          "score": {
            "$meta": "searchScore"
          },
          "source": 1,
          "_id": 0,
          "business_name": 1,
          "address": 1
        }
      },  {
        "$limit": 3
      }, {
        "$sort": {
          "score": -1
        }
      }
    ]
  }
}
])

[
  {
    name: 'XLR8 Mobile',
    number_of_employees: 21,
    founded_year: 2006,
    score: 2.0815043449401855,
    source: 'companies'
  },
  {
    name: 'Pulse Mobile',
    number_of_employees: null,
    founded_year: null,
    score: 2.0815043449401855,
    source: 'companies'
  },
  {
    name: 'T-Mobile',
    number_of_employees: null,
    founded_year: null,
    score: 2.0815043449401855,
    source: 'companies'
  },
  {
    business_name: 'T. MOBILE',
    address: { city: 'BROOKLYN', zip: 11209, street: '86TH ST', number: 440 },
    score: 2.900916337966919,
    source: 'inspections'
  },
  {
    business_name: 'BOOST MOBILE',
    address: { city: 'BRONX', zip: 10458, street: 'E FORDHAM RD', number: 261 },
    score: 2.900916337966919,
    source: 'inspections'
  },
  {
    business_name: 'SPRING MOBILE',
    address: {
      city: 'SOUTH RICHMOND HILL',
      zip: 11419,
      street: 'LIBERTY AVE',
      number: 12207
    },
    score: 2.900916337966919,
    source: 'inspections'
  }
]

db.companies.aggregate([
{
  "$search": {
    "text": {
      "query": "mobile",
      "path": "name",
      "score": {
        "boost": {
          "value": 1.6
        }
      }
    }
  }
}, {
  "$project": {
    "score": {
      "$meta": "searchScore"
    },
    "_id": 0,
    "number_of_employees": 1,
    "founded_year": 1,
    "name": 1
  }
}, {
  "$addFields": {
    "source": "companies",
    "source_count": "$$SEARCH_META.count.lowerBound"
  }
}, {
  "$limit": 3
}, {
  "$unionWith": {
    "coll": "inspections",
    "pipeline": [
      {
        "$search": {
          "text": {
            "query": "mobile",
            "path": "business_name"
          }
        }
      }, {
        "$project": {
          "score": {
            "$meta": "searchScore"
          },
          "business_name": 1,
          "address": 1,
          "_id": 0
        }
      }, {
        "$limit": 3
      }, {
        "$set": {
          "source": "inspections",
          "source_count": "$$SEARCH_META.count.lowerBound"
        }
      }, {
        "$sort": {
          "score": -1
        }
      }
    ]
  }
}, {
  "$facet": {
    "allDocs": [],
    "totalCount": [
      {
        "$group": {
          "_id": "$source",
          "firstCount": {
            "$first": "$source_count"
          }
        }
      }, {
        "$project": {
          "totalCount": {
            "$sum": "$firstCount"
          }
        }
      }
    ]
  }
}
])

[
  {
    allDocs: [
      {
        name: 'XLR8 Mobile',
        number_of_employees: 21,
        founded_year: 2006,
        score: 3.33040714263916,
        source: 'companies',
        source_count: Long("52")
      },
      {
        name: 'Pulse Mobile',
        number_of_employees: null,
        founded_year: null,
        score: 3.33040714263916,
        source: 'companies',
        source_count: Long("52")
      },
      {
        name: 'T-Mobile',
        number_of_employees: null,
        founded_year: null,
        score: 3.33040714263916,
        source: 'companies',
        source_count: Long("52")
      },
      {
        business_name: 'T. MOBILE',
        address: {
          city: 'BROOKLYN',
          zip: 11209,
          street: '86TH ST',
          number: 440
        },
        score: 2.900916337966919,
        source: 'inspections',
        source_count: Long("456")
      },
      {
        business_name: 'BOOST MOBILE',
        address: {
          city: 'BRONX',
          zip: 10458,
          street: 'E FORDHAM RD',
          number: 261
        },
        score: 2.900916337966919,
        source: 'inspections',
        source_count: Long("456")
      },
      {
        business_name: 'SPRING MOBILE',
        address: {
          city: 'SOUTH RICHMOND HILL',
          zip: 11419,
          street: 'LIBERTY AVE',
          number: 12207
        },
        score: 2.900916337966919,
        source: 'inspections',
        source_count: Long("456")
      }
    ],
    totalCount: [
      { _id: 'companies', totalCount: Long("52") },
      { _id: 'inspections', totalCount: Long("456") }
    ]
  }
]

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 sample_training.companies collection.

On the Database screen, click the sample_training database and then click the companies collection.

Run the Atlas Search query against the collection.

The following query searches both the companies and inspections collections for the term mobile in the name and business_name fields respectively.

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.

   Basic Example

   Facet Example

   This query uses the following stages:

   • $search to search for companies that include mobile in the name.

   • $unionWith to do the following:

     • Use $search stage in the sub-pipeline to search for inspections of companies that include mobile in the name.

     • Perform a union of documents from the companies and documents from the inspections collections.

   • $set stage to add a new field named source that identifies the collection of the output documents.

     • $limit stage to limit the output to 3 results from each collection.

     • $project stage to:

       • Include only the specified fields in the results.

       • Add a field named score.

   Pipeline Stage	Query
   $search	{ "text": { "query": "Mobile", "path": "name" } }
   $project	{ "score": { "$meta": "searchScore", }, "_id": 0, "number_of_employees": 1, "founded_year": 1, "name": 1 }
   $set	{ "source": "companies" }
   $limit	3
   $unionWith	{ "coll": "inspections", "pipeline": [ { "$search": { "text": { "query": "Mobile", "path": "business_name", } } }, { "$set": { "source": "inspections", } }, { "$project": { "score": { "$meta": "searchScore" }, "source": 1, "_id": 0, "business_name": 1, "address": 1 } }, { "$limit": 3 }, { "$sort": { "score": -1 } } ] }

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

   name: "XLR8 Mobile"
   number_of_employees: 21
   founded_year: 2006
   score: 2.0815043449401855
   source: "companies"
   name: "Pulse Mobile"
   number_of_employees: null
   founded_year: null
   score: 2.0815043449401855
   source: "companies"
   name: "T-Mobile"
   number_of_employees: null
   founded_year: null
   score: 2.0815043449401855
   source: "companies"
   business_name: "T. MOBILE"
   address: Object
   source: "inspections"
   score: 2.900916337966919
   business_name: "BOOST MOBILE"
   address: Object
   source: "inspections"
   score: 2.900916337966919
   business_name: "SPRING MOBILE"
   address: Object
   source: "inspections"
   score: 2.900916337966919

   This query uses the following stages:

   • $search to search for companies that include mobile in the name.

   • $project stage to:

     • Include only the specified fields in the results.

     • Add a field named score.

   • $addFields stage to add the following new fields:

     • A new field named source that identifies the collection of the output documents.

     • A field name source_count that shows a count of the output documents.

   • $unionWith to do the following:

     • Use $search stage in the sub-pipeline to search for inspections of companies that include mobile in the name.

     • Perform a union of documents from the companies and documents from the inspections collections.

   • $project stage to:

     • Include only the specified fields in the results.

     • Add a field named score.

   • $limit stage to limit the output to 3 results from each collection.

   • $set stage to add the following new fields:

     • A new field named source that identifies the collection of the output documents.

     • A new field named source_count that shows a count of the output documents.

   Pipeline Stage	Query
   $search	{ text: { query: "mobile", path: "name", score: { boost: { value: 1.6 } } } }
   $project	{ "score": { "$meta": "searchScore", }, "_id": 0, "number_of_employees": 1, "founded_year": 1, "name": 1 }
   $addFields	{ source: "companies", source_count: "$$SEARCH_META.count.lowerBound" }
   $limit	3
   $unionWith	{ coll: "inspections", pipeline: [ { $search: { text: { query: "mobile", path: "business_name" } } }, { $project: { score: { $meta: "searchScore" }, business_name: 1, address: 1, _id: 0 } }, { $limit: 3, }, { $set: { source: "inspections", source_count: "$$SEARCH_META.count.lowerBound" } }, { $sort: { score: -1 } } ] }
   $facet	{ allDocs: [], totalCount: [ { $group: { _id: "$source", firstCount: { $first: "$source_count" } } }, { $project: { totalCount: { $sum: "$firstCount" } } } ] }

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

   allDocs: Array (6)
     0: Object
       name: "XLR8 Mobile"
       number_of_employees: 21
       founded_year: 2006
       score: 3.33040714263916
       source: "companies"
       source_count: 52
     1: Object
       name: "Pulse Mobile"
       number_of_employees: null
       founded_year: null
       score: 3.33040714263916
       source: "companies"
       source_count: 52
     2: Object
       name: "T-Mobile"
       number_of_employees: null
       founded_year: null
       score: 3.33040714263916
       source: "companies"
       source_count: 52
     3: Object
       business_name: "T. MOBILE"
       address: Object
       score: 2.900916337966919
       source: "inspections"
       source_count: 456
     4: Object
       business_name: "BOOST MOBILE"
       address: Object
       score: 2.900916337966919
       source: "inspections"
       source_count: 456
     5: Object
       business_name: "SPRING MOBILE"
       address: Object
       score: 2.900916337966919
       source: "inspections"
       source_count: 456
   totalCount: Array (2)
     0: Object
       _id: "companies"
       totalCount: 52
     1: Object
       _id: "inspections"
       totalCount: 456