Stream Analytics - Processing JSON with no array name

Question

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

score 1 · Accepted Answer · answered Jan 04 '19 at 02:43

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

Thanks. But the problem is there is no array name and I cannot change that. The data is sent via an external system. — KevinH, Jan 04 '19 at 02:46
@KevinH So,would you please share what the format of the output you want from you source data?Why you want to use the GetArrayElements method? Please add it into your question? — Jay Gong, Jan 04 '19 at 02:52

score 0 · Answer 2 · answered Jan 05 '19 at 17:54

0

You don't have to use GetArrayElements. Just selecting json array as input format is enough. Stream analytics reads each object in the array as a record. Same with line or whitespace separated jain objects, each object is read as a record.

answered Jan 05 '19 at 17:54

Vignesh Chandramohan

1,306
10
15

yes, no need to use GetArrayElements. ASA treats each record as a row from input. – iAviator Nov 07 '21 at 15:32

Stream Analytics - Processing JSON with no array name

2 Answers2