How to render JSON using Stream Analytics Query

Question

I have Inputs in the form of JSON stored in Blob Storage I have Output in the form of SQL Azure table.

My wrote query and successfully moving value of specific property in JSON to corresponding Column of SQL Azure table.

Now for one column I want to copy entire JSON payload as Serialized string in one sql column , I am not getting proper library function to do that.

SELECT
     CASE 
        WHEN GetArrayLength(E.event) > 0
            THEN GetRecordPropertyValue(GetArrayElement(E.event, 0), 'name')
        ELSE ''
    END AS EventName 
    ,E.internal.data.id as DataId
    ,E.internal.data.documentVersion as DocVersion

    ,E.context.custom As CustomDimensionsPayload

Into OutputTblEvents
FROM InputBlobEvents E

This CustomDimensionsPayload should be a JSON actually

If your input json column "CustomDimensionsPayload" is an object (as opposed to a string), this is currently not possible in azure stream analytics. — Vignesh Chandramohan
– Vignesh Chandramohan, Commented Jan 9, 2017 at 2:44

davenpcj · Accepted Answer · 2017-03-07 13:58:20Z

2

I made a user defined function which did the job for me:

function main(InputJSON) {
    var InputJSONString = JSON.stringify(InputJSON);
    return InputJSONString;
}

Then, inside the Query, I used the function like this:

SELECT udf.ConvertToJSONString(COLLECT()) AS InputJSON
INTO outputX
FROM inputY

edited Mar 7, 2017 at 13:58

davenpcj

12.7k5 gold badges43 silver badges38 bronze badges

answered Mar 7, 2017 at 13:22

Per Schjetne

464 bronze badges

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

1 Comment

Christian Weiss Over a year ago

Did you add a GROUP BY? It fails for me with the following error: Stream Analytics job has validation errors: Query compilation error: Function 'collect' is either not supported or not usable in this context. User defined function calls must start with "udf." prefix. Aggregate functions must be used together with a GROUP BY clause..

AJ_ · Accepted Answer · 2020-04-09 07:40:08Z

0

You need to just reference the input object itself instead of COLLECT() if you want the entire payload to be converted. I was trying to do this also so figured I'd add what i did.

I used the same function suggested by PerSchjetne, query then becomes

SELECT udf.JSONToString(IoTInputStream)
INTO [SQLTelemetry]
FROM [IoTInputStream]

Your output will now be the full JSON string, including all the metadata extras that IOT hub adds on.

answered Apr 9, 2020 at 7:40

AJ_

937 bronze badges

Collectives™ on Stack Overflow

How to render JSON using Stream Analytics Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related