Docs Menu

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:

  1. 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.
  2. Schedule the updateMonthlySales function to update monthlysales on a periodic basis using an App Services scheduled trigger.
  3. Create an Atlas Search index on the monthlysales collection.
  4. Run a query on the monthlysales collection.

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

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.

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.

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

1

To define a new server-side function from the UI, you must first create an App Services App:

  1. If you have not already done so, click the App Services tab.
  2. 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.
  3. In the Name field, enter Sales-App as the name of the function.
  4. Under the Link your database field, select the Use an existing MongoDB Atlas Data Source option.
  5. From the dropdown, select the Atlas cluster you created in the Prerequisites.
  6. Click Create App Service.
2

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

  1. Click Functions in the left navigation menu.
  2. Click Create New Function.
  3. Enter updateMonthlySales as the name of the function.
  4. Under Authentication, select System.
3
  1. Click the Function Editor tab.
  2. 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);
    };
  3. Click the Run button in the lower right-hand corner of the Function Editor to create the monthlysales materialized view.
4
  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
  3. Query the sales collection. Note that the last sale in sales occurs in December of 2017:

    db.sales.find().sort( {saleDate: -1} )
  4. 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.

  5. Query the monthlysales materialized view:

    db.monthlysales.find().sort( { _id: -1} )
  6. 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'
    },
    ] )
  7. 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} )
5
  1. 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.
  2. Click Save Draft from either the Function Editor or Settings tab.
6

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} )

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

1
2
3
  1. Set the Trigger Type field to Scheduled.
  2. In the Name field, enter updateMonthlySales.
  3. Set the Schedule Type field to Basic.
  4. 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

  5. Set the Select An Event Type field to Function.
  6. In the Function dropdown, select updateMonthlySales.
4
5

Create an Atlas Search index on the monthlybakesales collection.

1

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

2
3
4
5
6
  1. In the Index Name field, enter monthlySalesIndex.
  2. In the Database and Collection section, find the sample_supplies database, and select the monthlysales collection.
  3. Click Next.
7
8
9

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

10

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 against the newly updated and indexed monthlybakesales collection.

1

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

2

Run the following command at mongosh prompt:

use sample_supplies
3

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 QueryHow to Use Facets with Atlas Search →
Give Feedback
© 2022 MongoDB, Inc.

About

  • Careers
  • Investor Relations
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2022 MongoDB, Inc.