parse nested Json string in Azure Stream Analytics

Question

Input data is streaming every 6s - the json array is below

{
"value": "{\"vrms\":0,\"apeak\":-102.4}",
"EventProcessedUtcTime": "2022-03-28T16:37:59.6289183Z",
"PartitionId": 0,
"EventEnqueuedUtcTime": "2022-03-28T15:37:58.0930000Z",
"IoTHub": {
"MessageId": "MSG_ID",
"CorrelationId": "CORE_ID",
"ConnectionDeviceId": "RUT955",
"ConnectionDeviceGenerationId": "637829563755785214",
"EnqueuedTime": "2022-03-28T15:37:58.0000000Z"
}

I'm trying to access the vrms and apeak value in the input data. I'm using the query below:

SELECT
UDF.parseJson(value).vrms,
UDF.parseJson(value).apeak
INTO
Output
From
Input

However, I keep getting the error
StepSplitter should never put Updatable UDFs and ML callouts in the same step! Step name powerbivisuoutputs0

My parseJson function is

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

Answer 1

Hello @Tran, Hoa ,
Thanks for the question and using MS Q&A platform.

As we understand the ask here is know how to use the JSON.parse , please do let us know if its not accurate.
Just to let know that the JSON.parse function is to be used to query Azure SQL data streams .
Azure SQL Database as reference data in your job, it's possible to have a column that has data in JSON format. An example is shown below.
https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-parsing-json#parse-json-record-in-sql-reference-data

I did went ahead and looked into the JSON which you shared

{
"value": "{\"vrms\":0,\"apeak\":-102.4}"
}

If we try to query this , we get

Select value.vrms from Input

If ths json is updated to

{
"value": {"vrms":0,"apeak":-102.4}
}

Select value.vrms,value.apeak from Input

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators