Azure CosmosDB Query Explorer结果和Azure Functions结果之间的差异 [英] Difference among the Azure CosmosDB Query Explorer's results and the Azure Functions results

问题描述

我在CosmosDB查询资源管理器上执行了以下查询.

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

结果是追随者.

[
  {
    "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"
  }
]

看来问题不存在.

接下来，我替换了静态定义到占位符的纪元值，以将其用作Azure Functions DocumentDB输入绑定的sqlQuery.然后，为了避免

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

然后我将modulationsymbol = %定义为应用程序设置.

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)

它看起来context.bindings.members是一个空列表.它与CosmosDB查询资源管理器的结果不同.

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

为什么会出现这种差异?

Why appeared this differences?

推荐答案

在打印出{epoch}之后，我发现它的类型是字符串，预期的类型是数字而不是字符串.这就是使用相同查询时获得空列表的原因.

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.

第1步，创建可以将字符串转换为数字的UDF.脚本资源管理器->创建用户定义函数

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

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

第2步，在创建ConvertToNumber函数之后，您可以使用它将{epoch}的类型转换为数字.

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

如果您熟悉C#，则可以使用C#创建函数.由于C#是一种强类型语言.我们可以定义一个类，该类用于反序列化来自队列的消息.它将在语言层中转换类型.

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

这篇关于Azure CosmosDB Query Explorer结果和Azure Functions结果之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！