Records Column (json) to convert one column to multiple rows in Azure Streaming Language

Question

I have a ID column and Records column with the values in different rows between square brackets in it.

Azure Streaming analytics language, I would want to split the values between square brackets pivot into multiple rows .

Answer 1

Hello @N2120 ,

once the JSON format is clear, it is so much easier to experiment and come to a solution.

The result is possible by adding a custom Javascript UDF function:

Start with the job script:

with converted AS
(
SELECT
    InputJson1.id,
	UDF.JavaScriptUDF1(InputJson1."record") as rec
FROM
    InputJson1
)

SELECT
    converted.id,
	CustomerSensorValue.ArrayValue.a AS Record1,
	CustomerSensorValue.ArrayValue.b AS Record2
FROM
converted
	CROSS APPLY GetArrayElements(converted.rec) AS CustomerSensorValue

You need to add a Javascript UDF:

// Sample UDF which returns sum of two values.
function main(arg1) {
    
    let b_added = arg1.replace(/:/g, ',"b":');

    let a_added = b_added.replace(/\[/g, ',{"a":');

    let accolade_added = a_added.replace(/]/g, "}");

    let shortened = accolade_added.substring(2);

    let arrayed = "[".concat(shortened, "]");

    let result = JSON.parse(arrayed);
    
	return result;
}

It converts the record string into a regular array from which we can read the elements.

If was enough to mark the output type as Any:

The test file was:

{ "id":"21a21", "record":"9[97:1][1:3][2:4][19:8][4:2][4:2][3:8][2:8][21:8]" }
{ "id":"21a34", "record":"3[97:1][1:3][2:4]" }

This results in the requested output:

Please check this out in Visual Code first for yourself.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.