Wait... MongoDB Doesn't Support Stored Procedures

When we first set out to add aggregations and lookups into MeshyDB, we thought of adding endpoints that included aggregation statements and lookup statements as parameters. This approach seemed to fit the idea of adding an API layer on top of MongoDB. However, when we actual sat down to use it we quickly realized that this method was tedious, time consuming and prone to errors. Long story short, it just wasn’t pleasant to work with. So, we ended up throwing it all away.

What we realized we needed was a better experience creating queries and a lower learning curve for implementing those queries into an application. Perhaps it was our SQL background that influenced this decision, but eventually we settled on something very common in the SQL world … Stored Procedures—or as we like to call them Projections!

What is a Projection?

A projection is a saved MongoDB query. This query can contain any number and combination of groupings, aggregations, filtering and lookups. These differ from views which are indexed and take up disk space. A projection is more like a stored procedure that allows you to re-use complex queries in a consistent manner. Interacting with these saved queries is similar to interacting with Meshes. You get a secure endpoint that you can reference to get a paged result. By giving each projection a dedicated route, we were able to achieve two goals that are weaknesses in MongoDB.

Maintainability

Remember when I said we hated the idea of passing up queries to the API? Well we decided the way to simplify this was to create a query builder in the MeshyDB Admin Portal. From here you can write up your query, preview the results and then save it off for use in your app. To use it you just need to know the name of the projection you provided when you created it. If you are using this projection in a dozen different places in your app and your logic changes, you don’t need to make that change a dozen times. Simply go back to the query builder and modify your Projection.

Security

By making projections a tangible object that our system maintains, we are able to associate permissions to it. This level of control is not something that is native to MongoDB. Rather in MongoDB, you need to grant full read access before you can aggregate over a collection.

Projections allow you as an admin to create a query that only shows the data you want and filter it according to your business rules. You can then associate permissions to the projection to make sure users are only seeing the data you want them to see.

Creating Your First Projection

Step 1 - Identify Your Mesh (Collection)

To create a projection, you first need to navigate to the new Projections section under your tenant. From here you want to click “+ Add Projection”. This will open a new model where you can begin defining your projection.

Now you will need to provide a name for your projection. This value should indicate the purpose or function of the projection. In this example we want to show popular states with their location counts, so we are going to call this “PopularStates”.

Once you’ve given your projection a name you will need to indicate which Mesh you want to project. Since we are aggregating location data we will search for and select the “locations” mesh.

Step 2 - Building Out a Stage

Now that we’ve identified what we want to project, let’s add our first stage. In this example we want to count the number of locations for each state and only show states with a large amount of locations. Let’s break this down its parts:

The first thing we need to do is identify how we are grouping the data. This is done by specifying the “_id” property of the $group stage. Whatever value is present here is how the data will group. In our example we are grouping by the “State” property of the location mesh data, so we want to have a value of “$State”. If you wanted to group by multiple values you would provide an object here instead of a string value.

The second part to this is the counting. Since MongoDB does not have a $count operator, we need to get clever here and $sum up the number of 1’s produced from the grouping. This essentially does the same thing as counting the number of records under each grouping.

Step 3 - Previewing Our First Stage

Before we get too deep into the weeds, lets preview the results we get form this first stage. You can preview the results by click the “Preview” button.

Clicking the “Preview” button will return the first 20 records in a paged result. In this example we are expecting to see States with their counts. If for whatever reason you are not seeing the results you expect, you can go back to the first stage and make the necessary modifications. Previewing does not save changes.

Step 4 - Adding Consecutive Stages

The goal of this projection is to show popular locations. Now that we have locations and their counts, we can begin to filter down the results to only show locations that meet a specific criteria. In our example we are going to exclude states with 1 or fewer locations. To achieve this let’s add a second $match stage to perform a filter against the results of stage 1.

It’s important to remember that each stage works off the results of the prior stage. Since we have a count property now exposed (see preview from prior stage) we can filter by that value. This can be done using MongoDB’s $gte operator with a value of 2. The code below shows how to exclude any location with a count not greater than or equal to 2.

Lastly let's rename the _id property to give our State value more clarity. To do this we will use the $project operator and rename the _id property to state. We also need to bring forward Count so it does not get removed from the final result.

After a quick preview we can easily see that our states are now paired down to only those that contain 2 locations and the new property we added called State is present.

Step 5 - Saving Your Projection

Now that we have the data, we can save this off by clicking the “Create” button. This projection is now saved and ready to use in our application. Remember, at any point we can come back, make modifications and save those changes.

Step 6 - Using Your Projection

Now that we have a projection we can interact with it via the MeshyDB secure API. In this example we are going to assume you already have a basic understanding of how authorization works. If you need a refresher on how to generate access tokens please see our docs.

To get our projected data we simply need to invoke a GET on /{tenant}/projections/{projection name}. Since our projection name is “PopularStates” the route would look something like this.

Request

GET /{tenant}/projections/PopularStates

Authorization: Bearer <access_token>

Response

{
    "results": [
        {
            "_id": "MO",
            "State": "MO",
            "Count": 2
        },
        {
            "_id": "WI",
            "State": "WI",
            "Count": 4
        }
    ],
    "page": 1,
    "pageSize": 25,
    "totalRecords": 2
}

Data will always be paged. If you want to change the page or page size of the result set simply provide query string parameters for page and pageSize to whatever you need.

Next Steps

There you have it, a simple yet practical implementation of MongoDB aggregations and filtering. Keep in mind projections can contain all sorts of operations not covered in this article. To see a complete list of supported operations or to view our API documentation checkout our docs.

We hope you found this article helpful. Please tweet at us your ideas for projections @meshydb and let us know your thoughts of this new feature. We would love to hear what you are going to create!