CODEWITHSUNDEEP
azureazure-stream-analytics
Keviguana
Keviguana

Reputation: 1

Specify Columns and Rows from Property Values in Query Language from Azure Stream Analytics

Hope I'm describing this correctly: Is it possible to create columns and rows only from property values? With my current code, the columns are the property names, and the rows are the values. Current Output Preferred Output

JSON Message: { deviceName: "FillerDevice", timestamp: "2019-07-17T16:42:10Z", tags: [ { do4: 1 }, { do5: 0 } ] }

Upvotes: 0

Views: 190

Answers (1)

quervernetzt
quervernetzt

Reputation: 11651

The following approach creates columns per defined keys of properties within an array.

You could do it with an User Defined Function assuming you know what keys to look for beforehand:

ASA Query

select
    Input.deviceName,
    Input.timestamp,
    UDF.ExtractData(Input.tags, 'do4') as do4,
    UDF.ExtractData(Input.tags, 'do5') as do5
into Output
from Input

UDF

function ExtractData(array, key) {
    'use strict';

    var value = null;

    if (array != null
        && key != null
        && Array.isArray(array)
        && isString(key)) {

        // find all objects indexes of objects in the array with the key
        var indexes = [], i;
        for (i = 0; i < array.length; i++) {
            if (array[i].hasOwnProperty(key)) {
                indexes.push(i);
            }  
        }

        if (indexes.length > 0) {
            // in case it occurs multiple times take the first one
            // you could also implement other conditions here
            var indexToUse = indexes[0];

            // set return value
            value = array[indexToUse][key];
        } 
    }

    return value;
}


function isString(value) {
    return typeof value === 'string' || value instanceof String;
}

Upvotes: 1

Related Questions