How to Run Queries on Multiple Collections

On this page

Create an App Services Function to Define the On-Demand Materialized View

Create a Scheduled Trigger
Create an Atlas Search Index on the Materialized View
Run a Query on the Materialized View

This tutorial describes how to create an index and run queries on multiple collections using a combination of on-demand materialized views and Atlas App Services scheduled triggers on the sample_supplies.sales collection from the sample dataset.

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 an App Services function named updateMonthlySales in the App Services UI to initialize the monthlysales materialized view using data from the sample sample_supplies.sales collection on your Atlas cluster.
Schedule the updateMonthlySales function to update monthlysales on a periodic basis using an App Services scheduled trigger.
Create an Atlas Search index on the monthlysales collection.
Run a query on the monthlysales collection.

Before you begin, ensure that your Atlas cluster meets the requirements described in the Prerequisites.

Create an App Services Function to Define the On-Demand Materialized View

Create a Javascript function in the App Services UI named updateMonthlySales. The function defines a materialized view that contains cumulative monthly sales information from sample_supplies.sales.

How the `updateMonthlySales` Function Works

The updateMonthlySales function defines a materialized view monthlysales 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: "monthlysales", whenMatched: "replace" } }
   ]
   var monthlysales = context.services.get("mongodb-atlas").db("sample_supplies").collection("sales");
   return monthlysales.aggregate(pipeline);
};

The function uses the following aggregation pipeline stages to update monthlysales:

The $match stage filters the data to process only those sales that were completed over the Phone.
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 the sales_price field to double. Atlas Search $search operators don't support the Decimal128 data type. Changing the sales_price field's data type allows you to query this field using Atlas Search indexes.
The $merge stage writes the output to the monthlysales 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.

Create the Function in the App Services UI

Now that you understand how the updateMonthlySales function works, create the function in the App Services UI:

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.

Create a New Function

To define a new server-side function from the UI:

Click Functions in the left navigation menu.
Click Create New Function.
Enter updateMonthlySales as the name of the function.
Under Authentication, select System.

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 variable exports:

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: "monthlysales", whenMatched: "replace" } }
]
var monthlysales = context.services.get("mongodb-atlas").db("sample_supplies").collection("sales");
return monthlysales.aggregate(pipeline);
};

Click the Run button in the lower right-hand corner of the Function Editor to create the monthlysales materialized view.

Test the Function

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:
```
use sample_supplies
```
Query the sales collection. Note that the last sale in sales 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 monthlysales materialized view.
Query the monthlysales materialized view:
db.monthlysales.find().sort( { _id: -1} )

Update the sales collection with new phone sales data from January of 2018.

db.sales.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'
  },
  {
    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'
  },
] )

Query the sales collection again to confirm the new sales entries. The top 2 query results reflect that the sales data now ends in January of 2018:
db.sales.find().sort( {saleDate: -1} )

Update the Materialized View

Return to your function draft in the App Services UI, and click the Run button in the lower right-hand corner of the Function Editor. The updateMonthlySales function refreshes the monthlysales materialized view using the January 2018 sales data.
Click Save Draft from either the Function Editor or Settings tab.

Confirm the Updated

Return to the mongosh and query the monthlysales materialized view to confirm the update. The top result returned by the query reflects the updated sales data for January 2018:

db.monthlysales.find().sort( { _id: -1} )

Create a Scheduled Trigger

Schedule the App Services function created in the previous step to run once a day to keep the materialized view up-to-date.

Click Triggers

Click Add a Trigger to open the Trigger configuration page.

Enter configuration values for the Trigger.

Set the Trigger Type field to Scheduled.
In the Name field, enter updateMonthlySales.
Set the Schedule Type field to Basic.
In the Repeat once by dropdown, select Day of the Month and set the value to your preferred date.
Note
Alternatively, for testing purposes, set Repeat once by dropdown to a more frequent occurrence, such as Minute or Hour
Set the Select An Event Type field to Function.
In the Function dropdown, select updateMonthlySales.

Click Save.

Review and deploy the `Sales-App` application draft.

Create an Atlas Search Index on the Materialized View

Create an Atlas Search index on the monthlybakesales collection.

Navigate to the Atlas Cluster Overview page.

Click Databases in the top-left corner of Atlas to navigate to the Database Deployments page for your project.

Click the cluster name to view cluster details.

Click the Search tab.

Click Create Search Index

Select Visual Editor, then click Next.

Enter the Index Name, and set the Database and Collection.

In the Index Name field, enter monthlySalesIndex.
In the Database and Collection section, find the sample_supplies database, and select the monthlysales collection.
Click Next.

Review the default Atlas Search index configuration settings.

Click Create Search Index.

Close the You're All Set! Modal Window.

A modal window appears to let you know your index is building. Click the Close button.

Check the status.

The newly created index appears on the 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 monthlybakesales 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.monthlysales` collection.

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

db.monthlysales.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 months in monthlysales had total sales greater than or equal to 10000 dollars.

For complete aggregation pipeline documentation, see the MongoDB Server Manual.

← How to Run a Compound Geo JSON Query How to Use Facets with Atlas Search →