Problem with parsing data from a JSON field

Question

I'm trying to process the log files with the following structure per record:

{"sessionId":"8e46140b-6bda-1a09-5a2d-8b1c89157083","scheduleId":"30251","screen":"35894","eventType":"201","mergedEventId":"c43b9eb0-9855-efd8-02e5-11d6de7a4caf","duration":"","variables":[{"Name":"scr_ins","Value":"e507ec9c"},{"Name":"seq","Value":"0"},{"Name":"zone","Value":"ContentPane"},{"Name":"type","Value":"html_app"},{"Name":"widget","Value":"slider"},{"Name":"content_name","Value":"16Apr_Value_Opti_Clip_A_1920x1080"},{"Name":"content_id","Value":"43900"}],"timestamp":"2024-04-20T06:55:46.318Z","deviceTimeStamp":"2024-04-20T16:55:46+10:00"}

This is a single record from the log file. As seen from this sample, the record contains the JSON field named "variables", and it is a JSON array. I need to extract certain values from this array. I have first tried with a Javascript UDF, but when I try to test this query, I'm getting the following error:

SyntaxError: JSON.parse Error: Invalid character at position:2 at ExtractValueFromVariables (Unknown script code:8:9)

My Javascript code in the UDF is correct, as it works flawlessly when I run it in a Javascript emulator with sample data. I can send it if someone wants to check it.

I have also tried the SQL function OPENJSON, but it seems it doesn't work in an Azure Stream Analytics job. Can anybody tell me what I need to do?

Answer 1

Hello Dragan Antanasković ,

Thank you for reaching out on Microsoft Q&A.

To extract values from the variables JSON array in Azure Stream Analytics, you can use the GetArrayElements function with CROSS APPLY. This method efficiently handles the array and avoids the need for a JavaScript UDF.

Sample Query

SELECT
  sessionId,
  scheduleId,
  screen,
  eventType,
  mergedEventId,
  duration,
  v.Name AS variableName,
  v.Value AS variableValue,
  timestamp,
  deviceTimeStamp
FROM YourInput
CROSS APPLY GetArrayElements(variables) AS v

• GetArrayElements: Breaks down the variables array into individual elements.
• CROSS APPLY: Joins the input stream with the elements of the variables array, allowing access to Name and Value.

If you found this solution helpful, consider accepting it.