pass stream with array to multiple rows in SQL by Stream Analytics

Question

I have a stream from IoT Hub like:

{
    "topic": "saveData",
    "deviceId": "testDevice",
    "data": [
        {
            "timestamp": "2018-04-06T11:46:11.842305",
            "device": "baiTest",
            "variable": "Status01_Test",
            "name": "m_01_test",
            "value": 365
        },
        {
            "timestamp": "2018-04-06T11:46:11.842306",
            "device": "hmuTest",
            "variable": "Status02_Test",
            "name": "m_02_test",
            "value": 817
        },
        {
            "timestamp": "2018-04-06T11:46:11.842307",
            "device": "vwzTest",
            "variable": "Status03_Test",
            "name": "m_03_test",
            "value": 247
        }
    ]
}

I want to pass this stream in a SQL DB like this:

deviceId    timestamp                   device   variable       name       value
testDevice  2018-04-06T11:46:11.842305  baiTest  Status01_Test  m_01_test  365
testDevice  2018-04-06T11:46:11.842306  hmuTest  Status02_Test  m_02_test  817
testDevice  2018-04-06T11:46:11.842307  vwzTest  Status03_Test  m_03_test  247

My code so far is:

WITH itemList AS ( 
    SELECT deviceId, GetArrayElement(data,0) as datas
    FROM [iotHub] WHERE topic = 'saveData' )
SELECT deviceId, datas.timestamp, datas.device, datas.variable, datas.name, datas.value
INTO [sqlTable]
FROM itemList

But this only stores the first index [0] of the data.array into the SQL. I think storing the array could be handled with the **GetArrayElements** function but I was not able to manage it.

score 2 · Accepted Answer · answered Apr 09 '18 at 03:24

You should use GetArrayElements to flatten complex json in Azure Stream Analytics. Please refer to the following query.

SELECT   
  iothubAlias.deviceId,
  arrayElement.ArrayValue.timestamp,
  arrayElement.ArrayValue.device,
  arrayElement.ArrayValue.variable,
  arrayElement.ArrayValue.name,
  arrayElement.ArrayValue.value
FROM [iothub-input] as iothubAlias  
CROSS APPLY GetArrayElements(iothubAlias.data) AS arrayElement

You will get the result what you want.

pass stream with array to multiple rows in SQL by Stream Analytics

1 Answers1