Querying MongoDB collections via the Azure Mobile Service node.js backend

In my previous post I talked about using Azure Mobile Services tables to use a Mongo database as the storage layer instead of the default SQL Azure. The post showed how to implement create, update, delete and simple read operations, but it didn’t have a good querying capability, as the table read operation was returning either one or all of the items in the collection. In this post we’ll look into an undocumented feature of the node.js runtime where we can get the query parameters passed by the user and add support for paging, sorting, selection and some filtering so that we can fully (or mostly) use a MongoDB collection from a node.js mobile service.

Query components

There are some posts which showed a node.js mobile service querying a MongoDB collection, but the querying is done either via custom query string parameters, or hardcoded in the code itself. A better experience, however, would be to have the runtime respect the OData query parameters which the client sends, so that the code for the client applications can be written more naturally. In other words, the client should be able to write this code:

var table = client.GetTable();
var last10Orders = await table
    .OrderByDescending(o => o.OrderDate)
    .Take(10)
    .ToListAsync();

And the runtime should apply those options when querying the Mongo collection. The code above sends the following query string in the HTTP request: /tables/order?$top=10&$orderby=OrderDate desc. The problem is that the OData query parameters (the ones starting with ‘$’) aren’t exposed by the parameters property in the request object on a read script. There is, however, one way to retrieve those in a read operations, using an undocumented (until this blog post, that is) function of the query object which exposes those: getComponents. With it, we can retrieve all OData parameters sent by the client, such as skip/top (paging), ordering, selection and filtering. Let’s change the read script to print out the query components

function read(query, user, request) {
    console.log(query.getComponents());
    request.execute();
}

Now if you send the request above to the server and look at the logs for your mobile service, you’ll see all the information which can be obtained from the query components:

{ filters: null,
  selections: [],
  projection: null,
  ordering: { OrderDate: false },
  skip: null,
  take: 10,
  table: 'complexOrders',
  context: undefined,
  includeTotalCount: false,
  version: 2 }

The components contain all the information which we need to read the OData query parameters in our scripts. So let’s start using them.

Paging (take / skip)

Let’s get back to the previous post to see what we had when retrieving multiple items from the Mongo collection

function returnMultipleObjects(collection, query, mongoHelper, request) {
    // TODO: look at query parameters. For now, return all items.
    collection.find().toArray(function(err, items) {
        if (err) {
            console.log('error querying collection: ', err);
            request.respond(200, { error: err });
        } else {
            items.forEach(function(item) {
                mongoHelper.mongoIdToMobileServiceId(item);
            });
            request.respond(200, items);
        }
    });
}

We were using the collection.find method, and it can take additional parameters for querying options, among those limit and skip, which are equivalent to the $top and $skip OData parameters respectively. And we can start adding those options to the find method:

function returnMultipleObjects(collection, query, mongoHelper, request) {
    var findOptions = {};

    var queryComponents = query.getComponents();

    applyTopAndSkip(findOptions, queryComponents);
    applyOrdering(findOptions, queryComponents);
    applySelect(findOptions, queryComponents);
    var findQuery = getFilterQuery(queryComponents, request);
    if (findQuery === null) {
        // response already sent
        return;
    }

    collection.find({}, findOptions).toArray(function(err, items) {
        if (err) {
            console.log('error querying collection: ', err);
            request.respond(200, { error: err });
        } else {
            items.forEach(function(item) {
                mongoHelper.mongoIdToMobileServiceId(item);
            });
            request.respond(200, items);
        }
    });

    function applyTopAndSkip(findOptions, queryComponents) {
        if (queryComponents.take) {
            findOptions.limit = queryComponents.take;
        }

        if (queryComponents.skip) {
            findOptions.skip = queryComponents.skip;
        }
    }

    function applySelect(findOptions, queryComponents) { }
    function applyOrdering(findOptions, queryComponents) { }
    function applyFilter(queryComponents, request) { }
    function getFilterQuery(queryComponents, request) { return {}; }
}

Now if we send a request to /tables/order?$skip=3&$top=5, we’ll only receive 5 documents, skipping the first 3 (in the default collection order – we’ll get to the ordering soon). One more note: if the request doesn’t contain a $top query parameter, the value of queryComponents.take will not be zero (it will have the value of 50, which is the default maximum number of items which is returned by the node.js runtime), so it’s safe to check for that value when defining the limit in the find options.

Ordering

Using paging (skip / take) is more powerful when you can define the order in which the paging is performed. For example, paging by the client name, or order date for example. As we saw in the output of the query components, the ordering is given as an object, with the keys representing the field, and the value a Boolean representing whether the order requested is ascending or descending. We then need that format in the format expected by the MongoDB node package (array with field name / ordering).

function applyOrdering(findOptions, queryComponents) {
    var orderBy = [];
    var ordering = queryComponents.ordering;
    for (var orderField in ordering) {
        if (ordering.hasOwnProperty(orderField)) {
            var ascending = queryComponents.ordering[orderField] ? 'ascending' : 'descending';
            orderBy.push([ orderField, ascending ]);
        }
    }

    if (orderBy.length) {
        findOptions.sort = orderBy;
    }
}

And now we can execute the code snippet shown in the beginning of this post – /tables/order?$top=10&orderby=orderDate desc.

Selection

MongoDB documents (and entities stored in databases in general) often have more information (properties / columns) than is required, and retrieving all of them would be unecessary cost (in bandwidth and processing). OData and MongoDB support selecting which fields are to be retrieved, and we can map once more the query components into the format expected by the MongoDB node driver:

function applySelect(findOptions, queryComponents) {
    var selects = queryComponents.selections;
    if (selects && selects.length) {
        if (selects.length === 1 && selects[0] === '*') {
            // Same as no $select, nothing to do
        } else {
            findOptions.fields = {};
            selects.forEach(function(field) {
                findOptions.fields[field] = 1;
            });
        }
    }
}

And now if all we want is the name of the client of the last 10 orders, we can do that: /tables/order?$top=10&$orderby=orderDate desc&$select=client.

Filtering

Often instead of requesting all elements of a collection (even if paged), a client will only request certain documents which match a certain criteria. For example, we only want the orders for clients starting with the letter ‘J’:

var table = Client.GetTable();
var items = await table
    .Where(o => o.Client.StartsWith("J"))
    .OrderByDescending(o => o.OrderDate)
    .Take(10)
    .ToListAsync();

The filter above will be sent as the $filter OData query parameter to the server: /tables/order?$top=10&$orderby=orderDate desc&$filter=startswith(client,'J'). And if we log the query components, we’ll see the filter expression in the ‘queryString’ value of the filters object, as shown below. There are other properties in the filters (args, type), but they are not used in this scenario (the code that converts between OData and the query string is shared between the server runtime and the JavaScript client SDK; they’re used in the client only).

{ filters: 
   { queryString: 'startswith(client,'J')',
     args: [],
     type: 'LiteralExpression' },
  selections: [],
  projection: null,
  ordering: { orderDate: false },
  skip: 0,
  take: 10,
  table: 'complexOrders',
  context: undefined,
  includeTotalCount: false,
  version: 4 }

And with the filter, we can now pass them into the collection.find method of the node.js MongoDB library. It’s not an easy task, though – the filter is given as a string, so it’s up to us to parse it and convert it into the proper format expected by the node driver. Here I show how to support a few constructs (simple binary operators, ‘startswith’ function), but a complete implementation would likely need to parse the query string into an expression tree and traverse it to create the equivalent MongoDB query object, but that would go beyond the scope of this post (which was mostly to introduce the query components functionality).

function getFilterQuery(queryComponents, request) {
    // Simple case: filter that excludes everything; no need to talk to the DB
    if (queryComponents.filters && queryComponents.filters.queryString === 'false') {
        request.respond(200, []);
        return null;
    }

    var findQuery = convertFilter(queryComponents.filters);
    if (findQuery === null) {
        request.respond(500, { error: 'Unsupported filter: ' + queryComponents.filters.queryString });
        return null;
    }

    return findQuery;
}

function convertFilter(filters) {
    var findQuery = {};

    var startsWith = [ /^startswith(([^,]+),'([^']+)')/, function(p) {
        var field = p[1];
        var value = p[2];
        var result = {};
        result[field] = new RegExp('^' + value);
        return result;
    } ];

    var binaryOperator = [ /^(([^s]+)s+([^s]{2})s(.+)$/, function(p) {
        var field = p[1];
        var operator = p[2];
        var value = p[3].slice(0, -1); // remove ending ')'
        if (/datetime'd{4}-d{2}-d{2}Td{2}:d{2}:d{2}.d{3}Z'/.test(value)) {
            // Date literal
            value = new Date(Date.parse(value.slice(9, -1)));
        } else if (/^'.+'$/.test(value)) {
            // String literal
            value = value.slice(1, -1);
        } else {
            // Number
            value = parseFloat(value);
        }

        var result = {};
        if (operator === 'eq') {
            result[field] = value;
        } else {
            result[field] = {};
            result[field]['$' + operator] = value;
        }
        return result;
    } ];

    var supportedFilters = [startsWith, binaryOperator];

    if (filters) {
        // Easy cases
        if (filters.queryString === 'true') {
            return {};
        }

        var foundMatch = false;
        for (var i = 0; i < supportedFilters.length; i++) {
            var match = filters.queryString.match(supportedFilters[i][0]);
            if (match) {
                findQuery = supportedFilters[i][1](match);
                foundMatch = true;
                break;
            }
        }

        if (!foundMatch) {
            return null;
        }
    }

    return findQuery;
}

And with that we can now execute fairly complex queries in the MongoDB using all supported mobile services client SDKs.

Wrapping up

In this post I showed an undocumented feature in the query object which allows the read script to have access to the OData query parameters, and how to use that to perform complex querying into a MongoDB collection. The functionality could be improved (for example, better support for converting the filter filtering, for example), but it enables the scenario I used in this (and the previous) post for extending the same table abstraction that all clients support to other backend storages.

If you want to get the full code for this post, you can find it in my blog samples repository. As always, feel free to leave comments / suggestions in the comments section of this post, or in our MSDN forums.