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.