Json Parse in Stream Analytics

Question

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:

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" } }

Answer

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:

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.

Json Parse in Stream Analytics

1 answer