Azure Stream Analytics能否处理Modbus/OPC UA数据并查询嵌套JSON值？

Hey there! I see you're having trouble pulling nested values from your Modbus simulator data in Azure Stream Analytics. Let's break down why your current query returns null and fix it step by step.

First, let's recap your sample JSON structure to make sure we're aligned:

{
  "body": {
    "PublishTimestamp": "2020-06-23 05:34:22",
    "Content": [
      {
        "HwId": "PowerMeter-0a:01:01:01:01:01",
        "Data": [
          {
            "CorrelationId": "DefaultCorrelationId",
            "SourceTimestamp": "2020-06-23 05:34:21",
            "Values": [
              {
                "DisplayName": "humidity",
                "Address": "400002",
                "Value": "47"
              },
              {
                "DisplayName": "Temperature",
                "Address": "400001",
                "Value": "78"
              }
            ]
          }
        ]
      }
    ]
  }
}

Why Your Current Query Returns Null #

Two main issues are causing this:

1. Nested Arrays: body.Content, Content[0].Data, and Data[0].Values are all arrays (marked by [] brackets). You can't directly access properties like data or Values on an array—you need to first expand each array into individual rows.
2. Case Sensitivity: Your query uses body.Content.data but the JSON has Data (capital D). Azure Stream Analytics is case-sensitive when referencing JSON properties, so this mismatch also leads to null results.

Step-by-Step Solution #

To access the nested Value fields, use CROSS APPLY with GetArrayElements() to expand each array layer by layer. Here's how to implement it:

1. Full Query to Extract All Values

This query flattens all nested arrays and lets you access every field you need:

SELECT
    -- Extract nested value details
    v.ValueItem.DisplayName,
    v.ValueItem.Address,
    v.ValueItem.Value,
    -- Include context like timestamps and device ID
    d.DataItem.SourceTimestamp,
    c.ContentItem.HwId,
    body.PublishTimestamp
INTO
    [YourOutputSink] -- Replace with your actual output sink name
FROM
    temperature
-- Expand the top-level Content array
CROSS APPLY GetArrayElements(body.Content) AS c(ContentItem)
-- Expand the Data array inside each Content item
CROSS APPLY GetArrayElements(c.ContentItem.Data) AS d(DataItem)
-- Expand the Values array inside each Data item
CROSS APPLY GetArrayElements(d.DataItem.Values) AS v(ValueItem)

2. Filter for Specific Values (e.g., Temperature)

If you only want to fetch the Temperature reading, add a WHERE clause to narrow down results:

SELECT
    v.ValueItem.Value AS TemperatureReading,
    d.DataItem.SourceTimestamp AS ReadingTimestamp
FROM
    temperature
CROSS APPLY GetArrayElements(body.Content) AS c(ContentItem)
CROSS APPLY GetArrayElements(c.ContentItem.Data) AS d(DataItem)
CROSS APPLY GetArrayElements(d.DataItem.Values) AS v(ValueItem)
WHERE
    v.ValueItem.DisplayName = 'Temperature'

Key Takeaways #

• Always Expand Arrays: Anytime you see [] in your JSON, use CROSS APPLY GetArrayElements() to turn array items into individual rows before accessing their properties.
• Watch Case Sensitivity: Double-check that your query uses the exact same capitalization as the JSON properties (e.g., Data not data).
• Layered Expansion: Work from the outermost array to the innermost one—expand Content first, then Data, then Values.

内容的提问来源于stack exchange，提问作者Avinash singh