Executing SQL query joins on Cosmos DB using Azure Mobile Apps for Node

Question

I'm currently developing a back end for a mobile system using Azure Mobile Apps with Node.js and Azure Cosmos DB (Document DB).

I'm using the example that was given here:

https://github.com/Azure/azure-mobile-apps-node/blob/master/samples/custom-api-sql-stmt/api/completeall.js

So far so good - however, whenever I run this SQL, I get:

TypeError: provider.read is not a function at Function.module.exports.api.execute (x:\x\node_modules\azure-mobile-apps\src\data\index.js:32:18

The SQL I need to run looks like this:

SELECT c.id, c.Email FROM Profile c JOIN d in c.DeviceIDs WHERE d.DeviceID = @deviceId

This is because my data is nested, like this (the Profile collection in Cosmos) - I need to get the records where the DeviceID matches:

[ { "id": "2ca572d0-858d-4376-8537-c228a8379638", "Email": "[email protected]", "Name": "Test User 1", "OrgRoles": null, "DeviceIDs": [ { "DeviceID": "123445555555", "DeviceCode": "1234" } ], "UpdatedDate": "2017-11-10T13:18:32.0110724Z", "CreatedDate": "2017-11-10T13:18:27.220764Z", "IsDeleted": true }]

I would love to be able to do this the other way, using something like this:

context.tables('Profile').where({ DeviceID: id })

but I cannot figure out how on earth to accomplish joins using this method, let alone joins on the same table.

Can anyone help with either method? I get the feeling the Azure Mobile Apps Document DB driver hasn't been finished properly by MS, but I can't seem to get a hold of anyone to see if there's any way I can help.

UPDATE: We have managed to do this via the document db driver directly, but it means that we will need to have some of our api methods connecting to the database one way and some others connecting a different way. Seems a shame, Microsoft should fully invest in Cosmos - it's quite good imho.

Answer 1

As the Azure Mobile Apps Node.js SDK doesn't support Cosmos DB driver, you would need to integrate the SDK azure-documentdb-node into your mobile app.

var documentClient = require("documentdb").DocumentClient;

var endpoint = 'https://<account_name>.documents.azure.com:443/';
var primaryKey = '<primary_key>';
var client = new documentClient(endpoint, { "masterKey": primaryKey });

var databaseUrl = `dbs/<database_name>`;    
var collectionUrl = `${databaseUrl}/colls/Profile`;

var querySpec = {
    'query': 'SELECT c.id, c.Email FROM Profile c JOIN d in c.DeviceIDs WHERE d.DeviceID = @deviceId',

    "parameters": [
        { "name": "@deviceId", "value": '123445555555' }
    ]
}

client.queryDocuments(collectionUrl, querySpec).toArray(function(err, results) {
    if(err) return console.log(err);

    console.log(results);
});