Docs Home → Launch & Manage MongoDB → MongoDB Atlas
How to Run Atlas Search Queries Using Materialized Views
On this page
This tutorial describes how to create an index and run queries against
the sample_supplies.sales
collection from the sample dataset and a new sample_supplies.purchaseOrders
collection
using a combination of the following features:
An on-demand materialized view is a collection that you create and
update using a $merge
aggregation pipeline stage. You can create an
Atlas 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 thesample_supplies
database.Create an App Services function named
updateMonthlySales
in the App Services UI to initialize themonthlyPhoneTransactions
materialized view using data from the samplesample_supplies.sales
collection on your Atlas cluster.Create an App Services function named
updateMonthlyPurchaseOrders
in the App Services UI to update themonthlyPhoneTransactions
materialized view using data from thesample_supplies.purchaseOrders
collection that you created on your Atlas cluster.Schedule the following functions to update the
monthlyPhoneTransactions
materialized view on a periodic basis using App Services scheduled triggers:updateMonthlySales
updateMonthlyPurchaseOrders
Create an Atlas Search index on the
monthlyPhoneTransactions
materialized view.Run a query on the
monthlyPhoneTransactions
materialized view.
Before you begin, ensure that your Atlas cluster meets the requirements described in the Prerequisites.
Required Access
To create an Atlas Search index, you must have Project Data Access Admin
or higher access to the project.
To create an App Services function and trigger, you must have
Project Owner
or higher access to the project.
Create the purchaseOrders
Collection
Connect to the sample_supplies
database.
Open
mongosh
in a terminal window and connect to your cluster. For detailed instructions on connecting, see Connect viamongosh
.Use the
sample_supplies
database:use sample_supplies
Add a new collection.
Add the purchaseOrders
collection with new phone purchase
order data from January of 2018. Run the following commands:
db.purchaseOrders.insertMany( [ { saleDate: ISODate("2018-01-23T21:06:49.506Z"), items: [ { name: 'printer paper', tags: [ 'office', 'stationary' ], price: Decimal128("40.01"), quantity: 2 }, { name: 'notepad', tags: [ 'office', 'writing', 'school' ], price: Decimal128("35.29"), quantity: 2 }, { name: 'pens', tags: [ 'writing', 'office', 'school', 'stationary' ], price: Decimal128("56.12"), quantity: 5 }, { name: 'backpack', tags: [ 'school', 'travel', 'kids' ], price: Decimal128("77.71"), quantity: 2 }, { name: 'notepad', tags: [ 'office', 'writing', 'school' ], price: Decimal128("18.47"), quantity: 2 }, { name: 'envelopes', tags: [ 'stationary', 'office', 'general' ], price: Decimal128("19.95"), quantity: 8 }, { name: 'envelopes', tags: [ 'stationary', 'office', 'general' ], price: Decimal128("8.08"), quantity: 3 }, { name: 'binder', tags: [ 'school', 'general', 'organization' ], price: Decimal128("14.16"), quantity: 3 } ], storeLocation: 'Denver', customer: { gender: 'M', age: 42, email: 'cauho@witwuta.sv', satisfaction: 4 }, couponUsed: true, purchaseMethod: 'Phone' } ])
db.purchaseOrders.insertMany( [ { saleDate: ISODate("2018-01-25T10:01:02.918Z"), items: [ { name: 'envelopes', tags: [ 'stationary', 'office', 'general' ], price: Decimal128("8.05"), quantity: 10 }, { name: 'binder', tags: [ 'school', 'general', 'organization' ], price: Decimal128("28.31"), quantity: 9 }, { name: 'notepad', tags: [ 'office', 'writing', 'school' ], price: Decimal128("20.95"), quantity: 3 }, { name: 'laptop', tags: [ 'electronics', 'school', 'office' ], price: Decimal128("866.5"), quantity: 4 }, { name: 'notepad', tags: [ 'office', 'writing', 'school' ], price: Decimal128("33.09"), quantity: 4 }, { name: 'printer paper', tags: [ 'office', 'stationary' ], price: Decimal128("37.55"), quantity: 1 }, { name: 'backpack', tags: [ 'school', 'travel', 'kids' ], price: Decimal128("83.28"), quantity: 2 }, { name: 'pens', tags: [ 'writing', 'office', 'school', 'stationary' ], price: Decimal128("42.9"), quantity: 4 }, { name: 'envelopes', tags: [ 'stationary', 'office', 'general' ], price: Decimal128("16.68"), quantity: 2 } ], storeLocation: 'Seattle', customer: { gender: 'M', age: 50, email: 'keecade@hem.uy', satisfaction: 5 }, couponUsed: false, purchaseMethod: 'Phone' } ])
Create the updateMonthlySales
Function
Create the updateMonthlySales function in the App Services UI.
How the updateMonthlySales
Function Works
The updateMonthlySales
function defines a
monthlyPhoneTransactions
materialized view that contains cumulative
monthly sales information. The function updates monthly sales
information for sales conducted over the phone.
The following example defines the function:
exports = function(){ var pipeline = [ { $match: {purchaseMethod: "Phone"} }, { $unwind: {path: "$items"}}, { $group: { _id: { $dateToString: { format: "%Y-%m", date: "$saleDate" } }, sales_quantity: { $sum: "$items.quantity"}, sales_price: { $sum: "$items.price"} }}, { $set: { sales_price: { $toDouble: "$sales_price"}}}, { $merge: { into: "monthlyPhoneTransactions", whenMatched: "replace" } } ] var monthlyPhoneTransactions = context.services.get("mongodb-atlas").db("sample_supplies").collection("sales"); return monthlyPhoneTransactions.aggregate(pipeline); };
The function uses the following aggregation pipeline stages to update
monthlyPhoneTransactions
:
The
$match
stage filters the data to process only those sales that were completed over thePhone
.The
$group
stage groups the sales information by the year-month. This stage outputs documents that have the form:{ "_id" : "<YYYY-mm>", "sales_quantity" : <num>, "sales_amount" : <NumberDecimal> } The
$set
stage changes the data type of thesales_price
field todouble
. Atlas Search$search
operators don't support theDecimal128
data type. Changing thesales_price
field's data type allows you to query this field using Atlas Search indexes.The
$merge
stage writes the output to themonthlyPhoneTransactions
collection.Based on the
_id
field, (the default for unsharded output collections), the stage checks if the document in the aggregation results matches an existing document in the collection:When Atlas Search finds a match (that is, a document with the same year-month already exists in the collection), Atlas Search replaces the existing document with the document from the aggregation results as specified in the stage.
When Atlas Search doesn't find a match, Atlas Search inserts the document from the aggregation results into the collection as specified in the stage. This is the default behavior when there is no match for the field.
Procedure
Create a new app.
To define a new server-side function from the UI, you must first create an App Services App:
If you have not already done so, click the App Services tab.
Create the app:
If you are creating your first App Services App in the project, you will be shown an option to start without a template (Build your own App). Select the Build your own App option.
If you have already created at least one App Services App in the project, click Create a New App.
In the Name field, enter
Sales-App
as the name of the function.Under the Link your Database field, select the Use an existing MongoDB Atlas Data Source option.
From the dropdown, select the Atlas cluster you created in the Prerequisites.
Click Create App Service.
Input the updateMonthlySales
function code.
Click the Function Editor tab.
Add the javascript code to the
exports
function. At minimum, the code must assign a function to the global variableexports
:exports = function(){ var pipeline = [ { $match: {purchaseMethod: "Phone"} }, { $unwind: {path: "$items"}}, { $group: { _id: { $dateToString:{ format: "%Y-%m", date: "$saleDate" } }, sales_quantity: { $sum: "$items.quantity"}, sales_price: { $sum: "$items.price"} } }, { $set: { sales_price: { $toDouble: "$sales_price"}}}, { $merge: { into: "monthlyPhoneTransactions", whenMatched: "replace" } } ] var monthlyPhoneTransactions = context.services.get("mongodb-atlas").db("sample_supplies").collection("sales"); return monthlyPhoneTransactions.aggregate(pipeline); }; Click the Run button in the lower right-hand corner of the Function Editor to create the
monthlyPhoneTransactions
materialized view.The Result tab at the bottom of the Function Editor should indicate success without any errors.
Click Save Draft.
Test the function.
Open
mongosh
in a terminal window and connect to your cluster. For detailed instructions on connecting, see Connect viamongosh
.Use the
sample_supplies
database:use sample_supplies Query the
sales
collection. Note that the last sale insales
occurs in December of 2017:db.sales.find().sort( {saleDate: -1} ) Confirm that the materialized view has been created in your
sample_supplies
database:show collections The command lists your collections, including the newly created
monthlyPhoneTransactions
materialized view.Query the
monthlyPhoneTransactions
materialized view:db.monthlyPhoneTransactions.find().sort( { _id: -1} ) The
monthlyPhoneTransactions
materialized view shows the newly added data. The top result reflects that the most recent transaction took place in December 2017.
Create the updateMonthlyPurchaseOrders
Function
Create the updateMonthlyPurchaseOrders function in the App Services UI.
How the updateMonthlyPurchaseOrders
Function Works
The updateMonthlyPurchaseOrders
function adds cumulative monthly
purchase order information to the monthlyPhoneTransactions
materialized view. The function updates the monthly purchase order
information for purchase orders conducted over the phone.
The following example defines the function:
exports = function(){ var pipeline = [ { $match: {purchaseMethod: "Phone"} }, { $unwind: {path: "$items"}}, { $group: { _id: { $dateToString: { format: "%Y-%m", date: "$saleDate" } }, sales_quantity: { $sum: "$items.quantity"}, sales_price: { $sum: "$items.price"} }}, { $set: { sales_price: { $toDouble: "$sales_price"}}}, { $merge: { into: "monthlyPhoneTransactions", whenMatched: "replace" } } ] var monthlyPhoneTransactions = context.services.get("mongodb-atlas").db("sample_supplies").collection("purchaseOrders"); return monthlyPhoneTransactions.aggregate(pipeline); };
The updateMonthlyPurchaseOrders
function uses the same aggregation
pipeline stages to update monthlyPhoneTransactions
as the
updateMonthlySales function.
Procedure
Create a New Function for the purchaseOrders
collection.
To define a new server-side function from the UI:
Return to your App Services App.
Click Functions in the left navigation menu.
Click Create New Function.
Enter
updateMonthlyPurchaseOrders
as the name of the function.Under Authentication, select System.
Input the updateMonthlyPurchaseOrders
Function Code
Click the Function Editor tab.
Add the javascript code to the
exports
function. At minimum, the code must assign a function to the global variableexports
:exports = function(){ var pipeline = [ { $match: {purchaseMethod: "Phone"} }, { $unwind: {path: "$items"}}, { $group: { _id: { $dateToString:{ format: "%Y-%m", date: "$saleDate" } }, sales_quantity: { $sum: "$items.quantity"}, sales_price: { $sum: "$items.price"} } }, { $set: { sales_price: { $toDouble: "$sales_price"}}}, { $merge: { into: "monthlyPhoneTransactions", whenMatched: "replace" } } ] var monthlyPhoneTransactions = context.services.get("mongodb-atlas").db("sample_supplies").collection("purchaseOrders"); return monthlyPhoneTransactions.aggregate(pipeline); }; Click the Run button in the lower right-hand corner of the Function Editor to update the
monthlyPhoneTransactions
materialized view.The Result tab at the bottom of the Function Editor should indicate success without any errors.
The
updateMonthlyPurchaseOrders
function refreshes themonthlyPhoneTransactions
materialized view with the January 2018 purchase order data.Click Save Draft.
Confirm the update.
Return to the
mongosh
and query themonthlyPhoneTransactions
collection to confirm the update:db.monthlyPhoneTransactions.find().sort( { _id: -1} ) The
monthlyPhoneTransactions
materialized view shows the newly added data. The top result reflects that the most recent transaction took place in January 2018.
Create Scheduled Triggers
Schedule the App Services functions created in the previous step to run once a day to keep the materialized view up-to-date.
Enter configuration values for the Trigger.
UI Field Name | Configuration |
---|---|
Trigger Type | Select Scheduled. |
Name | Specify updateMonthlySales . |
Schedule Type |
|
Select An Event Type | Select Function. |
Function | Select updateMonthlySales . |
Enter configuration values for the new Trigger.
UI Field Name | Configuration |
---|---|
Trigger Type | Select Scheduled. |
Name | Specify updateMonthlyPurchaseOrders . |
Schedule Type |
|
Select An Event Type | Select Function. |
Function | Select updateMonthlyPurchaseOrders . |
Create an Atlas Search Index on the Materialized View
Create an Atlas Search index on the monthlyPhoneTransactions
collection.
Check the status.
The newly created index appears on the Atlas Search tab. While the index is building, the Status field reads Build in Progress. When the index is finished building, the Status field reads Active.
Note
Larger collections take longer to index. You will receive an email notification when your index is finished building.
Run a Query on the Materialized View
Run a query against the newly updated and indexed
monthlyPhoneTransactions
collection.
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_supplies
database.
Run the following command at mongosh
prompt:
use sample_supplies
Run a simple Atlas Search query on the sample_supplies.monthlyPhoneTransactions
collection.
The following query counts the number of months in monthlyPhoneTransactions
with total sales greater than or equal to 10000
dollars:
db.monthlyPhoneTransactions.aggregate([ { $search: { "index": "monthlySalesIndex", "range": { "gt": 10000, "path": ["sales_price"] } } }, { $count: 'months_w_over_10000' }, ])
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.
For complete aggregation pipeline documentation, see the MongoDB Server Manual.