Processing nested JSON data in stream analytics for real-time visualization

Question

Hello,

I'm trying to get my sensor data into Power Bi via stream analytics so I can visualize it in real time.

I'm getting JSON messages sent to the IoT Hub that are nested. These messages are from two sensors and can be recognized by the nAdr variable which is located in data.rsp.nAdr.

From the JSON message, I need to read the data for both the first sensor kit (nAdr: 1) and the second sensor kit based on nAdr. I am able to read the data from the sensors field for the temperature sensor called TEMPERATURE the name is the same for both temperature sensors but they have different values. But I don't know how I can read data from the breakdown field for Light indicator and Potentiometer along with TEMPERATURE. Specifically, I need to read data from the value and possibly the unit variables.

To read the temperature data I used the following SQL query:

WITH ParsedData AS (
    SELECT
        GetArrayElement(data.rsp.result.sensors, 0) AS sensor1,
        GetArrayElement(data.rsp.result.sensors, 1) AS sensor2
    FROM
        NestedData
)

SELECT
    sensor1.name AS name1,
    sensor1.unit AS unit1,
    sensor1.value AS value1,
    sensor2.name AS name2,
    sensor2.unit AS unit2,
    sensor2.value AS value2
INTO
    DataOutput
FROM
    ParsedData

there are always 4 sensors in the sensor kit. Two temperature, one light indicator and one potentiometer, hence the designation sensore1 and sensore2

And here is my full JSON message from the first sensor kit:

{
    "mType": "iqrfSensor_ReadSensorsWithTypes",
    "data": {
        "msgId": "testEmbedSensor",
        "rsp": {
            "nAdr": 1,
            "hwpId": 2,
            "rCode": 0,
            "dpaVal": 88,
            "result": {
                "sensors": [
                    {
                        "id": "TEMPERATURE",
                        "type": 1,
                        "name": "Temperature",
                        "shortName": "t",
                        "value": 26,
                        "unit": "°C",
                        "decimalPlaces": 4
                    },
                    {
                        "id": "TEMPERATURE",
                        "type": 1,
                        "name": "Temperature",
                        "shortName": "t",
                        "value": 24.5,
                        "unit": "°C",
                        "decimalPlaces": 4
                    },
                    {
                        "id": "BINARYDATA7",
                        "type": 129,
                        "name": "Binary data7",
                        "shortName": "bin7",
                        "value": 81,
                        "unit": "?",
                        "decimalPlaces": 0,
                        "breakdown": [
                            {
                                "id": "BINARYDATA7",
                                "type": 129,
                                "name": "Light indicator",
                                "shortName": "light",
                                "unit": "%",
                                "decimalPlaces": 1,
                                "value": 36.2
                            }
                        ]
                    },
                    {
                        "id": "BINARYDATA7",
                        "type": 129,
                        "name": "Binary data7",
                        "shortName": "bin7",
                        "value": 73,
                        "unit": "?",
                        "decimalPlaces": 0,
                        "breakdown": [
                            {
                                "id": "BINARYDATA7",
                                "type": 129,
                                "name": "Potentiometer",
                                "shortName": "pot",
                                "unit": "%",
                                "decimalPlaces": 1,
                                "value": 42.5
                            }
                        ]
                    }
                ],
                "sensorIndexes": [
                    0,
                    1,
                    2,
                    3
                ]
            }
        },
        "raw": [
            {
                "request": "01.00.5e.01.ff.ff.0f.00.00.00",
                "requestTs": "2023-03-20T15:58:18.066+01:00",
                "confirmation": "01.00.5e.01.ff.ff.ff.58.01.08.01",
                "confirmationTs": "2023-03-20T15:58:18.091+01:00",
                "response": "01.00.5e.81.02.00.00.58.01.a0.01.01.88.01.81.51.81.49",
                "responseTs": "2023-03-20T15:58:18.346+01:00"
            }
        ],
        "insId": "iqrfgd2-default",
        "statusStr": "ok",
        "status": 0
    }
}

Answer 1

You can use the GetArrayElements function.

I found this example on the Microsoft site:

WITH DynamicCTE AS (
	SELECT   
		i.DeviceId,
		SensorMetadataRecords.ArrayValue.smKey as smKey,
		SensorMetadataRecords.ArrayValue.smValue as smValue
	FROM input i
	CROSS APPLY GetArrayElements(SensorMetadata) AS SensorMetadataRecords 
)

SELECT
	i.DeviceId,
	i.Location.*,
	V.smValue AS 'smVersion',
	M.smValue AS 'smManufacturer'
FROM input i
LEFT JOIN DynamicCTE V ON V.smKey = 'Version' and V.DeviceId = i.DeviceId AND DATEDIFF(minute,i,V) BETWEEN 0 AND 0 
LEFT JOIN DynamicCTE M ON M.smKey = 'Manufacturer' and M.DeviceId = i.DeviceId AND DATEDIFF(minute,i,M) BETWEEN 0 AND 0