Problem with parsing data from a JSON field

Dragan Antanasković 0 Reputation points
2024-05-20T23:53:38.33+00:00

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?

Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
342 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Gowtham CP 3,730 Reputation points
    2024-05-21T03:19:41.0266667+00:00

    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.

    1 person found this answer helpful.