azure stream analytics custom json output

Question

I need to convert simple json telemetry packet to custom json output. Can you please help me to achieve this.

INPUT to stream analytics

{"Id":80,"deviceId":"10004","temperature":21.94489404790873,"humidity":63.377043919318496}

Output of stream analytics should be as follows

{
 "SiteId":[
 80
],
"Name":"xxxx", -->hard coded value /reading from reference input
"Address":"xxxxx",-->hard coded value /reading from reference input
"telemetry":{
 "temperature":21.94489404790873,
 "humidity":63.377043919318496
}
}

}

score 0 · Accepted Answer · answered Feb 02 '20 at 16:16

Assuming that your reference data looks something like this:

[
    {
        "id" : 80,
        "name" : "xxxx",
        "address" : "xxxx"
    },

    {
        "id" : 90,
        "name" : "yyyy",
        "address" : "yyyy"
    }
]

you can try the following query:

with telemetry as (select i.id, i.temperature, i.humidity, R.address, R.name from input i inner join RefData R on R.Id = i.Id )

select udf.sitefunc(Id), name, address,  udf.telemetryfunc(temperature, humidity) as telemetry
into output
from telemetry

And two User Defined Functions(UDF), that you have to add in order to make it work, are:

sitefunc:

function UDFSample(arg1) {
    'use strict';
    var sites = [
        arg1
    ];
    return sites;
}

and telemetryfunc:

function UDFSample(arg1, arg2) {
    'use strict';
    var telemetry = {
        temperature : arg1,
        humidity : arg2
    };
    return telemetry;
}

Essentially, these two functions help you to convert the values to the desired JSON formatted output.

Local test output looks like this - but if your output is Blob Storage with JSON serialization, you will get the same format as in your example.

thanks for the detailed inputs I will try on these. – mbr Feb 03 '20 at 05:30 — mbr, Feb 03 '20 at 05:30

azure stream analytics custom json output

1 Answers1