Docs Menu
Docs Home
/
MongoDB Atlas
/
Atlas Search
/
Tutorials
/
Across Collections

How to Run Atlas Search Queries Using Materialized Views

On this page

  • Create the purchaseOrders Collection
  • Create the Scheduled Triggers
  • 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 against the sample_supplies.sales collection from the sample dataset 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 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:

  1. Create a collection named purchaseOrders in the sample_supplies database.

  2. Create two scheduled triggers:

    • updateMonthlySales, with a function named updateMonthlySales that initializes the monthlyPhoneTransactions materialized view using data from the sample sample_supplies.sales collection.

    • updateMonthlyPurchaseOrders, with a function named updateMonthlyPurchaseOrders that updates the monthlyPhoneTransactions materialized view using data from the sample_supplies.purchaseOrders collection.

  3. Create an Atlas Search index on the monthlyPhoneTransactions materialized view.

  4. Run a query on the monthlyPhoneTransactions materialized view.

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

To create an Atlas Search index, you must have Project Data Access Admin or higher access to the project.

To create the triggers, you must have Project Owner or higher access to the project.

Create the purchaseOrders Collection

1

Connect to the sample_supplies database.

  1. Open mongosh in a terminal window and connect to your cluster. For detailed instructions on connecting, see Connect via mongosh.

  2. Use the sample_supplies database:

    use sample_supplies
2

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'
  }
])
3

Query the new collection.

Query the purchaseOrders collection to confirm the new purchase order entries.

db.purchaseOrders.find().sort( {saleDate: -1} )

The two query results reflect that the purchase order data ends in January of 2018.

Create the Scheduled Triggers

In the following procedures, you create triggers to create a materialized view and schedule a function to update the materialized view daily.

Create the updateMonthlySales Trigger

Procedure

1
In Atlas, go to the Triggers page for your project.

  1. If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.

  2. If it's not already displayed, select your project from the Projects menu in the navigation bar.

  3. In the sidebar, click Triggers under the Services heading.

    The Triggers page displays.

2
Click Add a Trigger to open the Trigger configuration page.
3
Enter configuration values for the Trigger.
UI Field Name
Configuration
Trigger Type
Select Scheduled.
Name
Specify updateMonthlySales.
Schedule Type

  1. Select Basic.

  2. For Repeat once by, select Day of the Month and set the value to your preferred date.

    Alternatively, for testing purposes, set Repeat once by dropdown to a more frequent occurrence, such as Minute or Hour.

Select An Event Type
Select Function.
4
Create the Function.

The function for this trigger defines a monthlyPhoneTransactions materialized view that contains cumulative monthly sales information. The function updates monthly sales information for sales conducted over the phone.

Paste the following code into 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 the Phone.

  • The $group stage groups the sales information by the year-month. This stage outputs documents of 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 monthlyPhoneTransactions collection.

    Based on the _id field, 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.

5
Test the Function.

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 reflects the execution status of the function. Click Save Draft.

Create the updateMonthlyPurchaseOrders Trigger

Procedure

1
In Atlas, go to the Triggers page for your project.

  1. If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.

  2. If it's not already displayed, select your project from the Projects menu in the navigation bar.

  3. In the sidebar, click Triggers under the Services heading.

    The Triggers page displays.

2
Click Add a Trigger to open the Trigger configuration page.
3
Enter configuration values for the Trigger.
UI Field Name
Configuration
Trigger Type
Select Scheduled.
Name
Specify updateMonthlySales.
Schedule Type

  1. Select Basic.

  2. For Repeat once by, 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

4
Create the Function.
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.

5
Test the Function.

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 reflects the execution status of the function.

The updateMonthlyPurchaseOrders function refreshes the monthlyPhoneTransactions materialized view with the January 2018 purchase order data.

6
Click Save.
7
Review and deploy the Sales-App application draft.
8

Use mongosh to query the monthlyPhoneTransactions 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.

9
Review and deploy the Sales-App application draft.

Create an Atlas Search Index on the Materialized View

Create an Atlas Search index on the monthlyPhoneTransactions collection.

1

In Atlas, go to the Clusters page for your project.

  1. If it's not already displayed, select the organization that contains your desired project from the Organizations menu in the navigation bar.

  2. If it's not already displayed, select your desired project from the Projects menu in the navigation bar.

  3. If the Clusters page is not already displayed, click Database in the sidebar.

    The Clusters page displays.

2

Go to the Atlas Search page for your cluster.

You can go the Atlas Search page from the sidebar, the Data Explorer, or your cluster details page.

  1. In the sidebar, click Atlas Search under the Services heading.

  2. From the Select data source dropdown, select your cluster and click Go to Atlas Search.

    The Atlas Search page displays.

  1. Click the Browse Collections button for your cluster.

  2. Expand the database and select the collection.

  3. Click the Search Indexes tab for the collection.

    The Atlas Search page displays.

  1. Click the cluster's name.

  2. Click the Atlas Search tab.

    The Atlas Search page displays.

3

Click Create Search Index

4

Select Visual Editor, then click Next.

5

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

  1. In the Index Name field, enter monthlyPhoneTransactions.

  2. In the Database and Collection section, find the sample_supplies database, and select the monthlyPhoneTransactions collection.

  3. Click Next.

6

Review the default Atlas Search index configuration settings.

7

Click Create Search Index.

8

Close the You're All Set! Modal Window.

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

9

Check the status.

The newly created index appears on the Atlas Search tab. While the index is building, the Status field reads Building. 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.

1

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.

2

Use the sample_supplies database.

Run the following command at mongosh prompt:

use sample_supplies
3

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.

Back

Across Collections