question

JulianBalles-5866 avatar image
0 Votes"
JulianBalles-5866 asked JulianBalles-5866 commented

Json Parse in Stream Analytics

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
image.png (121.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KranthiPakala-MSFT avatar image
2 Votes"
KranthiPakala-MSFT answered JulianBalles-5866 commented

Hi @julianballes-5866,

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.




image.png (29.1 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @julianballes-5866,

Following up to see if the above suggestion was helpful?



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

0 Votes 0 ·

Hi @julianballes-5866,

We still have not heard back from you. Just wanted to check if you still need assistance on this query? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue. Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Thank you

0 Votes 0 ·

Thank you for your answer !

0 Votes 0 ·