Invalid column name on stream analytics, when column exists

Question

Given this dataset:

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

And this query:

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

I get invalid column name, does not exist on dataChannelId, but measures seem to work fine. How can I access this value without stream analytics complaining?

Answer 1

Your sample json data lack a square bracket ].

sample data:

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

Query 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

Output: