Difference among the Azure CosmosDB Query Explorer

I executed a following query on the CosmosDB Query Explorer.

SELECT c.id, c.created_at FROM c 
WHERE c.created_at_epoch <= 1499871600 - 86400*31 
AND (CEILING(1499871600/86400) - CEILING(c.created_at_epoch / 86400)) % 31 = 0

That's result is followings.

[
  {
    "id": "70251cbf-44b3-4cd9-991f-81127ad78bca",
    "created_at": "2017-05-11 18:46:16"
  },
  {
    "id": "0fa31de2-4832-49ea-a0c6-b517d64ede85",
    "created_at": "2017-05-11 18:48:22"
  },
  {
    "id": "b9959d15-92e7-41c3-8eff-718c4ab2be6e",
    "created_at": "2017-05-11 19:01:43"
  }
]

It looks that problem does not exist.

Next, I replaced epoch values that defined statically to placeholders for using it as sqlQuery of the Azure Functions DocumentDB input bindings. Then, I replaced a modulations symbol to %modulationsymbol% for avoiding this issue

SELECT c.id, c.created_at FROM c 
WHERE c.created_at_epoch <= {epoch} - 86400*31 
AND (CEILING({epoch}/86400) - CEILING(c.created_at_epoch / 86400))  %modulationsymbol% 31 = 0

And I defined modulationsymbol = % as an application setting.

Then, I specified the function as follows.

// index.js
module.exports = function (context, myQueueItem) {
    context.log(context.bindings.members, myQueueItem);
    context.done();
};

// function.json
{
  "bindings": [
    {
      "name": "myQueueItem",
      "type": "queueTrigger",
      "direction": "in",
      "queueName": "myqueue",
      "connection": "MYSTORAGE"
    },
    {
      "type": "documentDB",
      "name": "members",
      "databaseName": "myproject-db",
      "collectionName": "member",
      "sqlQuery": "SELECT c.id, c.created_at FROM c  WHERE {epoch} - c.created_at_epoch >= 86400*31  AND (CEILING({epoch}/86400) - CEILING(c.created_at_epoch / 86400)) %modulationsymbol% 31 = 0",
      "connection": "MYCOSMOSDB",
      "direction": "in"
    }
  ],
  "disabled": true
}

Afterward, I triggered the function and this result is followings.

2017-07-05T03:57:29.640 Function started (Id=d980521e-d23a-4bda-a730-57a236bcd011)
2017-07-05T03:57:30.594 [] { epoch: 1499871600 }
2017-07-05T03:57:30.594 Function completed (Success, Id=d980521e-d23a-4bda-a730-57a236bcd011, Duration=951ms)

It looks context.bindings.members is a empty list. It differenced from the CosmosDB Query Explorer's result.

Why appeared this differences?

After print out the {epoch}, I found its type is string and the expected type is number instead of string. That is the reason of getting empty list when use the same query.

To fix this issue, you could convert the type to number before using it to filter your query result. Steps below for your reference.

Step 1, Create a UDF which could convert string to number. Script Explorer->Create User Defined Function

function toNumber(ts) { 
   return parseInt(ts);
}

Step 2, After created the ConvertToNumber function, you could use it to convert the type of {epoch} to number.

SELECT c.id, c.created_at FROM c 
WHERE c.created_at_epoch <= udf.ConvertToNumber({epoch}) - 86400*31 
AND (CEILING(udf.ConvertToNumber({epoch})/86400) - CEILING(c.created_at_epoch / 86400))  %modulationsymbol% 31 = 0

If you are familiar with C#, you could create the function with C#. Since C# is a strongly typed language. We can define a class which is used to deserialize the message from queue. It will convert the type in language layer.

public class EpochMessage
{
    public int epoch { get; set; }
}

The whole function could be like this.

using System;

public static void Run(EpochMessage myQueueItem, TraceWriter log, IEnumerable<dynamic> members)
{
    log.Info(context.bindings.members, myQueueItem);
}

public class EpochMessage
{
    public int epoch { get; set; }
}