azureazure-stream-analyticsstream-analytics

How to render JSON using Stream Analytics Query


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


Solution

  • 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