Azure Stream Analytics convert array values

Question

Very specific question, if I have the following input in my Streaming Analytics component:

 //some input
 "outputs": [
  {
    "name": "output1",
    "unit": "unit1",
    "value": "95813"
  },
  {
    "name": "output2",
    "unit": "unit2",
    "value": "303883"
  }, // and more array values

How can I get a JSON result that would look as follows:

"outputs":[ {
   "output1":95813,
   "output2":303883
   //etc
 }]

So, I don't need the unit value, and to save space, I'd like to use the 'name' as the key, and the 'value' as the value of the key-value array. This is my current query:

SELECT
input.outputs as outputs
INTO "to-mongodb"
FROM "from-iothub" input

but this of course creates seperate JSON arrays, with the same structure as I do get as my input.

Anyone any idea on how to do this?

In worst case, just filtering out the 'unit' would also already be a great help.

Thanks in advance

Jay Gong · Accepted Answer · 2018-06-19 01:56:06Z

2

You could use user-defined functions in Azure Stream Analytics. Please refer to the sample function I tested for you.

UDF:

function main(arg) {
    var array = arg.outputs;
    var returnJson = {};
    var outputArray = [];
    var map = {};
    for(var i=0;i<array.length;i++){        
        var key=array[i].name;        
        map[key] = array[i].value;      
    }
    outputArray.push(map);
    returnJson = {"outputs" : outputArray};
    return returnJson;  
}

Query:

WITH 
c AS
(
    SELECT 
    udf.processArray(jsoninput) as result
    from jsoninput
)
SELECT
    c.result
INTO
    jaycosmostest
FROM
    c

Test Output:

Hope it helps you.

answered Jun 19, 2018 at 1:56

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rens Groenveld Over a year ago

Thank you so much Jay! This works like a charm! I had no idea you can use custom pieces of Javascript. I got my format exactly as I would like, you are a life saver! Thanks again.

Collectives™ on Stack Overflow

Azure Stream Analytics convert array values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related