Azure Stream Analytics - split one column to multiple rows

Question

I have a json input where I have value of data between square brackets in column A

I need to split the contents of column A into separate columns

Input:

I need to split that data into

SPLIT is not available. Pls advice if I can achieve this using cross apply? substring? charindex? Column A can square brackets values might change, some rows might have 4,some have 2 etc.

Answer 1

Input data does not seem to be of Json. If your data is comma separated array values, you can use cross apply and convert each value into a separate row. Then you can use, substring function to convert them into separate columns.

Query:

select 
substring(Rowvalue.arrayvalue,2,1) as col1, 
substring(Rowvalue.arrayvalue,4,1) as col2, 
substring(Rowvalue.arrayvalue,6,1) as col3
from input i
CROSS APPLY GetArrayElements(i.SensorReadings.columnA) AS Rowvalue 

Input data:

Output data: