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
}
}