How to Run $lookup with an Atlas Search $search Query
On this page
Starting in v6.0, the MongoDB $lookup aggregation stage
supports $search inside the $lookup
pipeline option. Using $lookup, you can join
multiple collections in the same database at query-time and run a
$search query to further narrow down your search.
Note
$lookup queries are not very performant because Atlas Search
does a full document lookup on the database for each document in the
collection. To learn more, see
Reduce $lookup Operations.
This tutorial demonstrates how to run a $lookup query
with $search against the accounts and customers
collections in the sample_analytics database. It takes you through
the following steps:
Set up an Atlas Search index with dynamic mapping for the
accountscollection in thesample_analyticsdatabase.Run
$lookupquery with$searchto find customers from thecustomerscollections whose accounts have purchased bothCurrencyServiceandInvestmentStockproducts in theaccountscollection.
Before you begin, ensure that your Atlas cluster meets the requirements described in the Prerequisites.
Note
To run $lookup query with $search, your
cluster must run MongoDB v6.0 or later. If not, Atlas Search displays the
following error message:
$_internalSearchMongotRemote is not allowed within a $lookup's sub-pipeline.
To learn more, see Upgrade Major MongoDB Version for a Cluster.
Required Access
To create an Atlas Search index, you must have Project Data Access Admin or higher access to the project.
Create the Atlas Search Index
Create an Atlas Search index named lookup-with-search-tutorial on all the
fields in the sample_analytics.accounts collection.
Navigate to the Atlas Search page for your project.
If it is not already displayed, select the organization that contains your desired project from the Organizations menu in the navigation bar.
If it is not already displayed, select your desired project from the Projects menu in the navigation bar.
Click your cluster's name.
Click the Atlas Search tab.
Create an Atlas Search index.
Click Create Search Index.
Select an Atlas Search Configuration Method and click Next.
For a guided experience, select the Atlas Search Visual Editor.
To edit the raw index definition, select the Atlas Search JSON Editor.
Enter the Index Name, and set the Database and Collection.
In the Index Name field, enter
lookup-with-search-tutorial.Note
If you name your index
default, you don't need to specify anindexparameter when using the $search pipeline stage. Otherwise, you must specify the index name using theindexparameter.In the Database and Collection section, find the
sample_analyticsdatabase, and select theaccountscollection.
Specify an index definition.
The following index definition dynamically indexes the fields of supported types in the collection. You can use the Atlas Search Visual Editor or the Atlas Search JSON Editor in the Atlas user interface to create the index.
Click Create Search Index.
Close the You're All Set! Modal Window.
A modal window displays to let you know your index is building. Click the Close button.
Wait for the index to finish building.
The index should take about one minute to build. While it is
building, the Status column reads Build in
Progress. When it is finished building, the
Status column reads Active.
Run $lookup with $search to Search the Collections
➤ Use the Select your language drop-down menu on this page to set the language of the examples in this section.
Connect to your Atlas cluster and run the sample query against the
indexed collections in the sample_analytics database.