azure-stream-analytics - 当列存在时，流分析中的列名无效

Question

给定这个数据集：

[
    {
        "dataChannelId": 8516,
        "measures": [
       {
            "dateTime": "2019-01-01T12:00:00",
       },
       {
            "dateTime": "2019-01-02T12:00:00",
       }
}]

这个查询：

WITH 
temp AS
(
    SELECT 
        dataChannelId,
        arrayElement.ArrayValue as element
        FROM GriegInputStream 
        CROSS APPLY GetArrayElements([mesurasdfes]) AS arrayElement  
)
SELECT
    temp.dataChannelId as sensorId, temp.element.dateTime, temp.element.value,temp.element.unit,temp.element.maxValue, temp.element.minValue
INTO
    Sensoroutput
FROM
    temp

我得到无效的列名，dataChannelId 上不存在，但措施似乎工作正常。如何在没有流分析抱怨的情况下访问此值？

标签： azure-stream-analytics

Answer 1

您的示例 json 数据缺少方括号]。

样本数据：

[
    {
        "dataChannelId": 8516,
        "measures": [
           {
                "dateTime": "2019-01-01T12:00:00",
           },
           {
                "dateTime": "2019-01-02T12:00:00",
           }
       ]
    }
]

查询sql：

WITH 
temp AS
(
    SELECT 
        jsoninput.dataChannelId,
        arrayElement.ArrayValue as element
        FROM jsoninput 
        CROSS APPLY GetArrayElements(jsoninput.measures) AS arrayElement  
)
SELECT
    temp.dataChannelId as sensorId, temp.element.dateTime
INTO
    Sensoroutput
FROM
    temp

输出：