sql - Azure 流分析查询返回 0 行

问题描述

我有来自 KEPServerEx 的带有嵌套列表的数据，因此我需要 CROSS APPLY 以将单个值从传入流发送到我的 blob。我利用这篇文章（通过 cql 流分析迭代 json msg 中的嵌套列表）让查询工作了几分钟，但无法再获得正确的输出。

一条消息如下所示：

[
  {
    "timestamp": 1575933997508,
    "values": [
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 5,
        "q": 1,
        "t": 1575933987573
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 196,
        "q": 1,
        "t": 1575933988076
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 56,
        "q": 1,
        "t": 1575933988570
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 104,
        "q": 1,
        "t": 1575933989077
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 24,
        "q": 1,
        "t": 1575933989567
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 177,
        "q": 1,
        "t": 1575933990069
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 168,
        "q": 1,
        "t": 1575933990575
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 113,
        "q": 1,
        "t": 1575933991067
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 189,
        "q": 1,
        "t": 1575933991572
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 96,
        "q": 1,
        "t": 1575933992075
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 15,
        "q": 1,
        "t": 1575933992567
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 179,
        "q": 1,
        "t": 1575933993074
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 22,
        "q": 1,
        "t": 1575933993569
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 98,
        "q": 1,
        "t": 1575933994073
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 9,
        "q": 1,
        "t": 1575933994575
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 142,
        "q": 1,
        "t": 1575933995071
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 54,
        "q": 1,
        "t": 1575933995576
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 174,
        "q": 1,
        "t": 1575933996070
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 188,
        "q": 1,
        "t": 1575933996567
      },
      {
        "id": "Channel1.Device1.RANDOM1",
        "v": 45,
        "q": 1,
        "t": 1575933997073
      }
    ]
  }
]

我用来成功地将上述消息值列表拆分为单个值的查询是：

SELECT
    event.timestamp as messageTS,
    [values].ArrayValue.id,
    [values].ArrayValue.v,
    [values].ArrayValue.t as valueTS
FROM
    brewingmqtt AS event
CROSS APPLY getarrayelements(event.eachvalue) AS [values]

不幸的是，这现在在执行测试时给了我 0 行返回，我无法弄清楚我错过了什么。

标签： sqljsonazureazure-stream-analytics

为什么要eachvalue在 getArrayElements 函数中设置？您的示例数据表明它应该是 [values]。我根据您的示例数据测试了 sql，它对我有用。

SELECT
    event.timestamp as messageTS,
    [values].ArrayValue.id,
    [values].ArrayValue.v,
    [values].ArrayValue.t as valueTS
FROM
    brewingmqtt AS event
CROSS APPLY getarrayelements(event.[values]) AS [values]

输出：

sql - Azure 流分析查询返回 0 行

问题描述

解决方案

推荐阅读