Parsing/expanding escaped array in Stream Job?

Question

I'm using an Azure Stream Job to parse incoming JSON data from an IoT Hub. I'm even using ...

CROSS APPLY GetArrayElements(event.NestedRows) as nestedrows

... to expand and denormalize additional events within each event - works great, no issues.

However, I have a new JSON property that is of type string and it is actually an embedded JSON array. For example:

{ 
    "escapedArray": "[ 1, 2, 3 ]" 
}

I'd like to use CROSS APPLY on this array as well, however I don't see any way to parse the string and convert it to a JSON array.

I considered a User Defined Function (UDF), but I read that it can only return scalers, and not arrays.

Is there a trick I'm missing inside the Stream Job to parse this string, or do I have to expand it in the event stream, prior to the Stream Job?

(FYI, I have no way to change this stream in the device event source.)

-John

Answer 1

According to the offical tutorial, you can create an UDF in Stream Analytics to convert a string to an array.

Defined an UDF udf.toJson as below.

function main(arrStr) {
    return JSON.parse(arrStr);
}

Then use the UDF in a Query for the property escapedArray to return an array.

SELECT
    UDF.toJson(escapedArray) as uarr
INTO
    [YourOutputAlias]
FROM
    [YourInputAlias]

My test result is as the figure below.

Hope it helps.