Json Parse in Stream Analytics

Julian Balles 1 Reputation point
2021-03-13T08:55:59.617+00:00

Hello World, i'm trying to parse some data in Stream Analytics but i don't seem to be successfull.
This is what i send into my IoT Hub:
look below

I want to access the Channel1_Temp and MyVariable2.

In my Query Sample the Data Locks like: 77385-image.png

Can someone please help me to write the Query ?

Would be highly appreciated!

Thank you!

Julian

*Edit: I dont know why the formatting got all messed up. Sorry for that.

{ "body": { "MessageType": "TagValues", "WagoProtocol": "1.5.0", "CollectionId": 0, "TagData": [ { "Time": "2021-03-13T09:45:56.419+01:00", "Values": { "Channel1_Temp": 20.44740104675293, "MyVariable2": 22478 } }, { "Time": "2021-03-13T09:45:58.468+01:00", "Values": { "Channel1_Temp": 20.44740104675293, "MyVariable2": 22560 } }, { "Time": "2021-03-13T09:46:00.469+01:00", "Values": { "Channel1_Temp": 20.44740104675293, "MyVariable2": 22640 } } ] }, "enqueuedTime": "Sat Mar 13 2021 09:46:22 GMT+0100 (Mitteleuropäische Normalzeit)", "properties": { "MessageType": "TagValues", "ProtocolVersion": "1.5.0" } }

Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
343 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee
    2021-03-15T19:59:59.377+00:00

    Hi @Julian Balles ,

    Welcome to Microsoft Q&A forum and thanks for reaching out.

    You can use GetArrayElements (Azure Stream Analytics) function to achieve your requirement. This function is useful for parsing arrays and nested objects in JSON and AVRO formatted input event data.

    Based in the below JSON formatted input data I have wrote a sample query to retrieve Channel1_Temp & MyVariable2 values. Please try similar query based on your source column structure.

    Sample source used (Note: getArrayElements is input name):

    {  
     "body": {  
     "MessageType": "TagValues",  
     "WagoProtocol": "1.5.0",  
     "CollectionId": 0,  
     "TagData": [  
     {  
     "Time": "2021-03-13T09:45:56.419+01:00",  
     "Values": {  
     "Channel1_Temp": 20.44740104675293,  
     "MyVariable2": 22478  
     }  
     },  
     {  
     "Time": "2021-03-13T09:45:58.468+01:00",  
     "Values": {  
     "Channel1_Temp": 20.44740104675293,  
     "MyVariable2": 22560  
     }  
     },  
     {  
     "Time": "2021-03-13T09:46:00.469+01:00",  
     "Values": {  
     "Channel1_Temp": 20.44740104675293,  
     "MyVariable2": 22640  
     }  
     }  
     ]  
     },  
     "enqueuedTime": "Sat Mar 13 2021 09:46:22 GMT+0100 (Mitteleuropäische Normalzeit)",  
     "properties": {  
     "MessageType": "TagValues",  
     "ProtocolVersion": "1.5.0"  
     }  
    }  
    

    Query to be used:

    SELECT  
        TagDataRecords.ArrayValue.[Values].[Channel1_Temp] as Channel1_Temp,  
        TagDataRecords.ArrayValue.[Values].[MyVariable2] as MyVariable2  
    FROM getArrayElements  
    CROSS APPLY GetArrayElements(body.TagData) AS TagDataRecords  
    

    Result:
    77838-image.png

    For more samples, please refer to this: Parse JSON and Avro data in Azure Stream Analytics

    Hope this helps.

    ----------

    Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

    2 people found this answer helpful.