cross-collection-tutorials

cross-collection-tutorials

For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt

<!-- Tab options on this page. Append to the .md URL to filter: ?tabs=<id,...> select specific tabs (e.g. ?tabs=nodejs,shell) ?allTabs=true include every tab (no param) default: one tab per tabset

Available tabs: other tabs: basic, facet, fts-vs, data-explorer, vib, json-editor, cloud, local, maven bom, gradle bom, maven, gradle -->

How to Run MongoDB Search Queries Across Multiple Collections

Run MongoDB Search queries across multiple collections using $lookup, $unionWith, or materialized views. Select your deployment type, interface, and language below to view the appropriate tutorial.

This page contains tutorials that demonstrate the different ways you can run MongoDB Search queries across multiple collections:

To join collections and search across them, use [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup with [$search.](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search
To combine search results from multiple collections, use [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith with [$search.](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search
To consolidate multiple collections so you can index and search them together, use materialized views.

Before you begin, ensure that your cluster meets the requirements described in the [Prerequisites.](https://www.mongodb.com/docs/search/about/design-patterns.md#std-label-fts-design-patterns-prereqs

Search Joined Collections

Starting in v6.0, the MongoDB [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup aggregation stage supports [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search inside the [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup pipeline option. Using [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup, you can join multiple collections in the same database at query-time and run a [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search query to further narrow down your search.

This tutorial demonstrates how to run a [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup query with [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search against the accounts and customers collections in the sample_analytics database. It takes you through the following steps:

Create a default MongoDB Search index on the accounts collection in the sample_analytics database.
Run a [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup query with [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search to find customers from the customers collection whose accounts have purchased both CurrencyService and InvestmentStock products in the accounts collection.

Considerations

To run [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup query with [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search, your cluster must run MongoDB v6.0 or later. To upgrade your MongoDB version, see [Upgrade Major MongoDB Version for a Cluster.](https://www.mongodb.com/docs/atlas/tutorial/major-version-change.md#std-label-upgrade-major-MongoDB-version

[$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup queries are less performant because MongoDB Search does a full document lookup on the database for each document in the collection.

To learn how to improve the performance of $lookup queries, see [embeddedDocuments.](https://www.mongodb.com/docs/search/indexes/field-types/embedded-documents-type.md#std-label-embedded-documents-arrays-anti-pattern

Create the MongoDB Search Index

In this section, you can learn how to create a MongoDB Search index named lookup-with-search-tutorial on all the fields in the sample_analytics.accounts collection.

Connect to your deployment by using MongoDB Compass.

Open Compass and connect to your cluster. For detailed instructions, see [Connect to a Cluster via Compass.](https://www.mongodb.com/docs/atlas/compass-connection.md#std-label-atlas-connect-via-compass

Specify the database and collection.

On the Database screen, click the name of the database, then click the name of the collection.

Create the MongoDB Search index.

Click the Indexes tab, then select Search Indexes.

Click Create Atlas Search Index to open the index creation dialog box.

Specify a name for the index.

Your MongoDB Search index is named default by default. If you keep this name, then your index will be the default Search index for any MongoDB Search query that does not specify a different index option in its [operators](https://www.mongodb.com/docs/search/query/operators-collectors/overview.md#std-label-fts-operators. If you are creating multiple indexes, we recommend that you maintain a consistent, descriptive naming convention across your indexes.

Specify the JSON (Javascript Object Notation) MongoDB Search index definition.

json { "mappings": { "dynamic": true } }

Click Create Search Index.

Run `$lookup` with `$search` to Search the Collections

In this section, you can learn how to connect to your deployment and run the sample query against the indexed collections in the sample_analytics database.

Connect to your cluster in MongoDB Compass.

Open MongoDB Compass and connect to your cluster. For detailed instructions on connecting, see [Connect to a Cluster via Compass.](https://www.mongodb.com/docs/atlas/compass-connection.md#std-label-atlas-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 a MongoDB Search query against the collection.

The following query uses the following stages:

[$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-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](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-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](https://www.mongodb.com/docs/manual/reference/operator/aggregation/limit.md#mongodb-pipeline-pipe.-limit stage to limit the output to 5 results.
[$project](https://www.mongodb.com/docs/manual/reference/operator/aggregation/project.md#mongodb-pipeline-pipe.-project stage to exclude the specified fields in the results.

To run this query in MongoDB Compass:

Click the Aggregations tab.

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](https://www.mongodb.com/docs/compass/current/aggregation-pipeline-builder/#set-the-documents-limit-or-auto-preview-documents Auto Preview, MongoDB Compass displays the following documents next to the $project pipeline stage:

```json 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.

Combine Query Results

Starting in v6.0, the MongoDB [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith aggregation stage supports [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search inside the [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith pipeline option. Using [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith, you can combine [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search results from multiple collections in the same database in the result set.

Warning:

Queries using [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith with [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search in the sub-pipeline might fail during an upgrade from a replica set to a sharded cluster running MongoDB v7.0 if the collection is not spread across multiple shards. To mitigate this, deploy more than one shard on your cluster and shard the collection such that multiple chunks are spread across multiple shards.

This tutorial demonstrates how to run a [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith query with [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search against the companies and inspections collections in the sample_training database. It takes you through the following steps:

Create a default MongoDB Search index on the companies and inspections collections in the sample_training database.
Run a [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith query with [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search to perform a union of companies with mobile in their name from both the companies and inspections collections.

Note:

To run a [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith query with [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search, your cluster must run MongoDB v6.0 or higher. To upgrade your MongoDB version, see [Upgrade Major MongoDB Version for a Cluster.](https://www.mongodb.com/docs/atlas/tutorial/major-version-change.md#std-label-upgrade-major-MongoDB-version

Create the MongoDB Search Indexes

In this section, you can learn how to create a MongoDB Search index named default on all the fields in the companies collection in the sample_training database. You will create another MongoDB Search index named default on all the fields in the inspections collection in the sample_training database. You must perform the following steps for each collection.

Create the MongoDB Search Index

In this section, you will create a MongoDB Search index named default on all the fields in the companies collection in the sample_training database. You will create another MongoDB Search index named default on all the fields in the inspections collection in the sample_training database. You must perform the following steps for each collection.

Connect to your deployment by using MongoDB Compass.

Specify the database and collection.

On the Database screen, click the name of the database, then click the name of the collection.

Create the MongoDB Search index.

Click the Indexes tab, then select Search Indexes.

Click Create Atlas Search Index to open the index creation dialog box.

Specify the JSON (Javascript Object Notation) MongoDB Search index definition.

json { "mappings": { "dynamic": true } }

Click Create Search Index.

Run `$unionWith` with `$search` to Search the Collections

In this section, you can learn how to connect to your deployment and run the sample query against the indexed collections in the sample_training database.

Run `$unionWith` with `$search` to Search the Collections

In this section, you will connect to your deployment and run the sample query against the indexed collections in the sample_training database.

Connect to your cluster in MongoDB Compass.

Use the sample_training.companies collection.

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

Run the MongoDB Search query against the collection.

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

To run this query in MongoDB Compass:

Click the Aggregations tab.

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

This query uses the following stages:

[$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search to search for companies that include mobile in the name.
[$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith to do the following:
- Use [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-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](https://www.mongodb.com/docs/manual/reference/operator/aggregation/set.md#mongodb-pipeline-pipe.-set stage to add a new field named source that identifies the collection of the output documents.
- [$limit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/limit.md#mongodb-pipeline-pipe.-limit stage to limit the output to 3 results from each collection.
- [$project](https://www.mongodb.com/docs/manual/reference/operator/aggregation/project.md#mongodb-pipeline-pipe.-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
}
}
]
}`

```json 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 ```

Expand your query results.

Run MongoDB Search Queries Using Materialized Views

This tutorial describes how to create an index and run queries against the sample_supplies.sales collection from the [sample dataset](https://www.mongodb.com/docs/atlas/import/load-sample-data.md#std-label-sample-data and a new sample_supplies.purchaseOrders.

An on-demand materialized view is a collection that you create and update using a $merge aggregation pipeline stage. You can create an MongoDB Search index on the materialized view and then run queries on the materialized view using the $search aggregation pipeline stage.

This tutorial takes you through the following steps:

Create a collection named purchaseOrders in the sample_supplies database.
Create a materialized view named monthlyPhoneTransactions that retrieves data from the sample_supplies.sales and sample_supplies.purchaseOrders collections.
Create a MongoDB Search index on the monthlyPhoneTransactions materialized view.
Run a query on the monthlyPhoneTransactions materialized view.

Create the `purchaseOrders` Collection

Connect to your deployment by using MongoDB Compass.

Specify the database and create a new collection.

On the Databases screen, click the sample_supplies database.

Click Create Collection.

In the Create Collection dialog, enter purchaseOrders in the Collection Name field.

Click Create Collection.

Add documents to the collection.

Click the newly created purchaseOrders collection.

Click Add Data and select Insert document.

In the Insert Document dialog, enter the following document:

json { "saleDate": { "$date": "2018-01-23T21:06:49.506Z" }, "items": [ { "name": "printer paper", "tags": ["office", "stationary"], "price": 40.01, "quantity": 2 }, { "name": "notepad", "tags": ["office", "writing", "school"], "price": 35.29, "quantity": 2 }, { "name": "pens", "tags": ["writing", "office", "school", "stationary"], "price": 56.12, "quantity": 5 }, { "name": "backpack", "tags": ["school", "travel", "kids"], "price": 77.71, "quantity": 2 }, { "name": "notepad", "tags": ["office", "writing", "school"], "price": 18.47, "quantity": 2 }, { "name": "envelopes", "tags": ["stationary", "office", "general"], "price": 19.95, "quantity": 8 }, { "name": "envelopes", "tags": ["stationary", "office", "general"], "price": 8.08, "quantity": 3 }, { "name": "binder", "tags": ["school", "general", "organization"], "price": 14.16, "quantity": 3 } ], "storeLocation": "Denver", "customer": { "gender": "M", "age": 42, "email": "cauho@witwuta.sv", "satisfaction": 4 }, "couponUsed": true, "purchaseMethod": "Phone" }

Click Insert.

Click Add Data and select Insert Document again.

In the Insert Document dialog, enter the following document:

json { "saleDate": { "$date": "2018-01-25T10:01:02.918Z" }, "items": [ { "name": "envelopes", "tags": ["stationary", "office", "general"], "price": 8.05, "quantity": 10 }, { "name": "binder", "tags": ["school", "general", "organization"], "price": 28.31, "quantity": 9 }, { "name": "notepad", "tags": ["office", "writing", "school"], "price": 20.95, "quantity": 3 }, { "name": "laptop", "tags": ["electronics", "school", "office"], "price": 866.5, "quantity": 4 }, { "name": "notepad", "tags": ["office", "writing", "school"], "price": 33.09, "quantity": 4 }, { "name": "printer paper", "tags": ["office", "stationary"], "price": 37.55, "quantity": 1 }, { "name": "backpack", "tags": ["school", "travel", "kids"], "price": 83.28, "quantity": 2 }, { "name": "pens", "tags": ["writing", "office", "school", "stationary"], "price": 42.9, "quantity": 4 }, { "name": "envelopes", "tags": ["stationary", "office", "general"], "price": 16.68, "quantity": 2 } ], "storeLocation": "Seattle", "customer": { "gender": "M", "age": 50, "email": "keecade@hem.uy", "satisfaction": 5 }, "couponUsed": false, "purchaseMethod": "Phone" }

Click Insert.

Verify the inserted documents.

Click the Documents tab if you're not already on it.

Navigate to the search bar and select the Options dropdown.

In the Sort field, enter:

json { "saleDate": -1 }

Click Find.

The query results should display the two purchase order documents, with the most recent (January 25, 2018) displayed first.

Create and Update the Materialized View

In this section, you can learn how to create a materialized view that stores cumulative sales data for each month. This view uses data from the sample_supplies.sales and sample_supplies.purchaseOrders collections. Then, create a function to update the materialized view each month.

Connect to your cluster in MongoDB Compass.

Navigate to the sample_supplies database.

On the Database screen, click the sample_supplies database to access its collections.

Create a materialized view using the sales collection.

First, you'll run an aggregation pipeline on the sales collection to create the materialized view:

Click on the sales collection.

Click the Aggregations tab.

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.

Stage 1: $match

javascript { purchaseMethod: "Phone" }
Stage 2: $unwind

javascript { path: "$items" }
Stage 3: $group

javascript { _id: { $dateToString: { format: "%Y-%m", date: "$saleDate" } }, sales_quantity: { $sum: "$items.quantity" }, sales_price: { $sum: "$items.price" } }
Stage 4: $set

javascript { sales_price: { $toDouble: "$sales_price" } }
Stage 5: $merge

javascript { into: "monthlyPhoneTransactions", whenMatched: "replace" }

Click Run to execute the aggregation pipeline. This creates the initial monthlyPhoneTransactions collection from the sales data.

Update the materialized view with data from the purchaseOrders collection.

Next, you'll run a similar aggregation pipeline on the purchaseOrders collection to add that data to the materialized view:

Navigate back to the database view and click on the purchaseOrders collection.

Click the Aggregations tab.

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.

Stage 1: $match

javascript { purchaseMethod: "Phone" }
Stage 2: $unwind

javascript { path: "$items" }
Stage 3: $group

javascript { _id: { $dateToString: { format: "%Y-%m", date: "$saleDate" } }, sales_quantity: { $sum: "$items.quantity" }, sales_price: { $sum: "$items.price" } }
Stage 4: $set

javascript { sales_price: { $toDouble: "$sales_price" } }
Stage 5: $merge

javascript { into: "monthlyPhoneTransactions", whenMatched: "replace" }

Click Run to execute the aggregation pipeline. This updates the monthlyPhoneTransactions collection with data from the purchaseOrders collection.

Verify that the materialized view was created correctly.

Navigate back to the database view and click on the newly created monthlyPhoneTransactions collection.

Click the Documents tab to view the documents in the collection.

You should see documents with monthly aggregated data. The first few documents might look like:

json { "_id": "2013-01", "sales_quantity": 200, "sales_price": 2941.47 } { "_id": "2013-02", "sales_quantity": 191, "sales_price": 4822.15 } { "_id": "2013-03", "sales_quantity": 74, "sales_price": 2429.12 }

Set up a schedule to update the materialized view.

For production environments, we recommend that you set up a scheduled task to update the materialized view. You can do this by using the following methods:

MongoDB Scheduled Triggers: If using MongoDB Atlas, create a scheduled trigger through the Atlas UI to run this aggregation on a monthly basis.
Cron Job: Set up a cron job on your server that runs a script to connect to MongoDB and execute the aggregation pipeline.
Application Logic: Implement scheduled updates in your application using the example code shown in the programming language examples of this guide.

Create a MongoDB Search Index on the Materialized View

In this section, you can learn how to create a MongoDB Search index on the monthlyPhoneTransactions collection.

Connect to your deployment by using MongoDB Compass.

Specify the database and collection.

On the Database screen, click the name of the database, then click the name of the collection.

Create the MongoDB Search index.

Click the Indexes tab, then select Search Indexes.

Click Create Atlas Search Index to open the index creation dialog box.

Specify a name for the index.

Specify the JSON (Javascript Object Notation) MongoDB Search index definition.

json { "mappings": { "dynamic": true } }

Click Create Search Index.

Run a Query on the Materialized View

In this section, you can learn how to run a query against the newly updated and indexed monthlyPhoneTransactions collection.

Connect to your cluster in MongoDB Compass.

Use the sample_supplies database.

On the Database screen, click the sample_supplies database and then click the monthlyPhoneTransactions collection.

Run a simple MongoDB Search query against the collection.

The following query counts the number of months in monthlyPhoneTransactions with total sales greater than or equal to 10000 dollars.

To run this query in MongoDB Compass:

Click the Aggregations tab.

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,
}`

json { "months_w_over_10000": 4 }

Interpret the query results.

The above query returns 4, indicating that only 4 months out of all the months in the monthlyPhoneTransactions materialized view had total sales greater than or equal to 10000 dollars. This result reflects data from both the sample_supplies.sales and sample_supplies.purchaseOrders collections.

This page contains tutorials that demonstrate the different ways you can run MongoDB Search queries across multiple collections:

To join collections and search across them, use [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup with [$search.](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search
To combine search results from multiple collections, use [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-unionWith with [$search.](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search
To consolidate multiple collections so you can index and search them together, use materialized views.

Before you begin, ensure that your cluster meets the requirements described in the [Prerequisites.](https://www.mongodb.com/docs/search/about/design-patterns.md#std-label-fts-design-patterns-prereqs

Search Joined Collections

Create a default MongoDB Search index on the accounts collection in the sample_analytics database.
Run a [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup query with [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search to find customers from the customers collection whose accounts have purchased both CurrencyService and InvestmentStock products in the accounts collection.

Considerations

Create the MongoDB Search Index

In this section, you can learn how to create a MongoDB Search index named lookup-with-search-tutorial on all the fields in the sample_analytics.accounts collection.

Connect to the deployment by using mongosh.

In your terminal, connect to your Atlas cloud-hosted deployment or local deployment from [mongosh](https://www.mongodb.com/docs/mongodb-shell.md#mongodb-binary-bin.mongosh. For detailed instructions on how to connect, see Connect to a Deployment.

Switch to the database that contains the collection for which you want to create the index.

shell use sample_analytics

Output:

shell switched to db sample_analytics

Run the db.collection.createSearchIndex() method to create the index.

shell db.accounts.createSearchIndex( "lookup-with-search-tutorial", { "mappings": { "dynamic": true } } )

Output:

text lookup-with-search-tutorial

Run `$lookup` with `$search` to Search the Collections

In this section, you can learn how to connect to your deployment and run the sample query against the indexed collections in the sample_analytics database.

Connect to your deployment by using [mongosh](https://www.mongodb.com/docs/mongodb-shell.md#mongodb-binary-bin.mongosh.

Open [mongosh](https://www.mongodb.com/docs/mongodb-shell.md#mongodb-binary-bin.mongosh in a terminal window and connect to your deployment. For detailed instructions on connecting, see [Connect to a Cluster via mongosh.](https://www.mongodb.com/docs/atlas/mongo-shell-connection.md#std-label-connect-mongo-shell

Switch to the sample_analytics database.

Run the following command at [mongosh](https://www.mongodb.com/docs/mongodb-shell.md#mongodb-binary-bin.mongosh prompt:

sh use sample_analytics

Output:

sh switched to db sample_analytics

Run the following [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup with MongoDB Search [$search](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search query.

The following query uses the following stages:

[$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-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](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-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](https://www.mongodb.com/docs/manual/reference/operator/aggregation/limit.md#mongodb-pipeline-pipe.-limit stage to limit the output to 5 results.
[$project](https://www.mongodb.com/docs/manual/reference/operator/aggregation/project.md#mongodb-pipeline-pipe.-project stage to exclude the specified fields in the results.

json db.customers.aggregate([ { $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 } } ])

Output:

json [ { 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' ] } ] } ]

Combine Query Results

Starting in v6.0, the MongoDB [$unionWith](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionWith.md#mongodb-pipeline-pipe.-u

（本文超出站点单文档体积上限，已截断）

cross-collection-tutorials

cross-collection-tutorials

How to Run MongoDB Search Queries Across Multiple Collections

Search Joined Collections

Considerations

Create the MongoDB Search Index

Run $lookup with $search to Search the Collections

Combine Query Results

Create the MongoDB Search Indexes

Create the MongoDB Search Index

Run $unionWith with $search to Search the Collections

Run $unionWith with $search to Search the Collections

Run MongoDB Search Queries Using Materialized Views

Create the purchaseOrders Collection

Create and Update the Materialized View

Create a MongoDB Search Index on the Materialized View

Run a Query on the Materialized View

Search Joined Collections

Considerations

Create the MongoDB Search Index

Run $lookup with $search to Search the Collections

Combine Query Results

Run `$lookup` with `$search` to Search the Collections

Run `$unionWith` with `$search` to Search the Collections

Run `$unionWith` with `$search` to Search the Collections

Create the `purchaseOrders` Collection

Run `$lookup` with `$search` to Search the Collections