azureazure-stream-analyticsstreaming-analytics

Stream Analytics - Processing JSON with no array name


IoT Edge v2 with the modbus module sends data to IoT Hub in the format of:

[
{
    "DisplayName": "Voltage",
    "HwId": "",
    "Address": "400001",
    "Value": "200",
    "SourceTimestamp": "2019-01-03 23:40:24"
},
{
    "DisplayName": "Voltage",
    "HwId": "",
    "Address": "400002",
    "Value": "24503",
    "SourceTimestamp": "2019-01-03 23:40:24"
},
...
]

I want to convert this array to rows using a stream analytics query containing the CROSS APPLY GetArrayElements() but this function requires an array name. Obviously there is no name. Any suggestions?

https://learn.microsoft.com/en-us/stream-analytics-query/getarrayelements-azure-stream-analytics https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-parsing-json


Solution

  • Yes, it needs an array name. CROSS APPLY GetArrayElements() is used for nested array.

    Example:

    [{
          "source": "xda",
          "data": 
            [{
                "masterTag": "UNIFY1",
                "speed": 180
            },
            {
                "masterTag": "UNIFY2",
                "speed": 180
            }],
          "EventEnqueuedUtcTime": "2018-07-20T19:28:18.5230000Z",
      },
      {
          "source": "xda",
          "data": [{
                "masterTag": "UNIFY3",
                "speed": 214
            },
            {
                "masterTag": "UNIFY4",
                "speed": 180
            }],
          "EventEnqueuedUtcTime": "2018-07-20T19:28:20.5550000Z",
      }
    ]
    

    You could use below sql to convert it to rows:

    SELECT 
        jsoninput.source, 
        arrayElement.ArrayValue.masterTag
    INTO 
        output
    FROM jsoninput
    CROSS APPLY GetArrayElements(jsoninput.data) AS arrayElement 
    

    However ,now the input data you provided is a pure array. If you want to convert this array to rows, just use sql:

    select jsoninput.* from jsoninput
    

    enter image description here