[SOLVED] Extract certain values from Arrays in Azure Stream Analytics and then adding other values manually

Extract certain values from Arrays in Azure Stream Analytics and then adding other values manually

I have set up some sensors were I recieve the data (JSON) trough Event Hub to a Stream Analytics job and down to Data Lake Gen2. I Just need some of the values from the data and want to add some values e.g Floor, Room since these data is not included in the data i recieve from the sensors. Since it is not so many sensors, i thought i could add it manually in stream analytics. E.g. Case when serialno equals "1234567" add floor "2". Here i have ran into some trouble because the data is stored in arrays as shown below.

My goal is to sepearate the elements(datapoints) in the array in to seperate events, but still keep the values from the sensor as SerialNo, DateTime and also add values as floor, room etc. I dont know if this is possible to execute?

This is an example of the data i recieve into Stream analytics. In this example it is just two datapoints, but there have been cases with up to nine datapoints. The reason is that the sensors measure many different things (e.g Temperature, humidity) and make their own aggregations as average over 1 hour and 24 hours.

[
  {
    "dsType": "MIBUS",
    "timeStamp": 1583304733314,
    "dateTime": "2020-03-04T06:52:13.0000000Z",
    "serialNo": "1234567",
    "manufacturer": "Trank",
    "battLvl": 0,
    "bridgeId": "03000000",
    "dpCnt": 2,
    "datapoint": [
      {
        "type": "FLOAT",
        "name": "Temperature",
        "size": 32,
        "dataType": "BCD_DIGIT",
        "res": 0.1,
        "resUnit": "Degrees",
        "valueType": "CSV",
        "unit": "C",
        "value": 12.5,
        "scale": 1,
        "min": "-20",
        "max": "55",
        "low": " ",
        "high": " "
      },
      {
        "type": "NUMBER",
        "name": "Humidity",
        "size": 8,
        "dataType": "UINT8",
        "unit": "%",
        "res": 1,
        "resUnit": "%",
        "valueType": "CSV",
        "value": 46,
        "scale": 1,
        "min": " ",
        "max": " ",
        "low": " ",
        "high": " "
      }
    ],
    "uniqueId": "TR1234567",
    "EventProcessedUtcTime": "2020-03-04T07:50:07.7614906Z",
    "PartitionId": 0,
    "EventEnqueuedUtcTime": "2020-03-04T06:52:13.3990000Z"
  },

So the result i wish for from the data shown over looks kind of like this:

SerialNo        DateTime                     name       value       valueType       Floor       Room   
1234567   2020-03-04T06:52:13.0000000Z    Temperature   12,5          CSV             2        Bedroom
1234567   2020-03-04T06:52:13.0000000Z     Humidity      46           CSV             2        Bedroom

I have tried functions ass GetArrayElement, CROSS APPLY, GetRecordProperties etc. without any luck. Probably because i have not applied them in the right way.

I am open to every suggestion to get it right :)

Thanks in advance!

Solution

You can easily convert the array elements into separate events by writing a query like:

SELECT i.serialNo, i.dateTime, sensorData.ArrayValue.name, 
sensorData.ArrayValue.value, sensorData.ArrayValue.valueType
FROM input i
CROSS APPLY GetArrayElements(datapoint) as sensorData

In order to get values for floor and bedroom, you can also use Reference data input (SQL database or blob storage). And then JOIN this reference data with your real time input to get the values of floor and bedroom based on serialno.