Extracting Array inside a Property in Azure Stream Analytics

Question

I have had no luck so far extracting certain values in a wide format out of a JSON string via a stream analytics job.

The JSON has the following format:

{"devicename":"demo","msgtime":"2018-04-13T11:00:00.0000000Z",
"payload":[{"Sensor":"one","Value":1.817,"Unit":"W"}, 
{"Sensor":"two","Value":0.481,"Unit":"W"}, 
{"Sensor":"three","Value":0.153,"Unit":"W"}]}}

I am trying to get it in the following format:

name    one     two     three

demo    1.817   0.481   0.153

…       …       …       …

I tried getting the values with "Cross APPLY GetPropertyValues(input)", but I can't get them in a wide format.

score -1 · Answer 1 · answered Apr 16 '18 at 04:00

try code like below

  SELECT
            localInput.devicename,
            udf.getValue('one', localInput.payload) as One,
            udf.getValue('two', localInput.payload) as Two,
            udf.getValue('three', localInput.payload) as Three
        FROM localInput;

  function main(identifier, arr) {
    var result = null;

    if (Object.prototype.toString.call(arr) == "[object Array]") {
        for (i = 0; i < arr.length; i++) {
            if (arr[i].type == identifier) {
                result = arr[i].value;
            }
        }
    }
    return result;
  }

Extracting Array inside a Property in Azure Stream Analytics

1 Answers1