Stream Analytics query giving Null while filtering Modbus module payload from azure iotedge

Question

Hi Team, I'm getting invalid syntax error while trying to extract the "Values" Array. I have tried multiple query patterns to list all displayNames and its corresponding Value in columns, please find sample input json below

[
{
    "PublishTimestamp": "2024-01-10 15:52:57",
    "Content": [
      {
        "HwId": "PowerMeter-0a:01:01:01:01:01",
        "Data": [
          {
            "CorrelationId": "MessageType1",
            "SourceTimestamp": "2024-01-10 15:14:54",
            "Values": [
              {
                "DisplayName": "KWH",
                "Address": "400001",
                "Value": "354"
              },
              {
                "DisplayName": "v_freq",
                "Address": "400009",
                "Value": "2839"
              },
              {
                "DisplayName": "v_dist",
                "Address": "400008",
                "Value": "45"
              }
            ]
          },
          {
            "CorrelationId": "MessageType1",
            "SourceTimestamp": "2024-01-10 15:14:55",
            "Values": [
              {
                "DisplayName": "Active_Power_Total",
                "Address": "400006",
                "Value": "116"
              },
              {
                "DisplayName": "VYB",
                "Address": "400003",
                "Value": "228"
              },
              {
                "DisplayName": "VBR",
                "Address": "400005",
                "Value": "224"
              },
              {
                "DisplayName": "Frequency",
                "Address": "400007",
                "Value": "137"
              },
              {
                "DisplayName": "VRY",
                "Address": "400004",
                "Value": "222"
              },
              {
                "DisplayName": "KVAH",
                "Address": "400002",
                "Value": "410"
              },
              {
                "DisplayName": "KWH",
                "Address": "400001",
                "Value": "371"
              },
              {
                "DisplayName": "v_freq",
                "Address": "400009",
                "Value": "1091"
              },
              {
                "DisplayName": "v_dist",
                "Address": "400008",
                "Value": "24"
              }
            ]
          }
        ]
      }
    ],
    "EventProcessedUtcTime": "2024-01-10T16:34:00.6908086Z",
    "PartitionId": 0,
    "EventEnqueuedUtcTime": "2024-01-10T15:53:02.1310000Z",
    "IoTHub": {
      "MessageId": null,
      "CorrelationId": null,
      "ConnectionDeviceId": "edgevm1",
      "ConnectionDeviceGenerationId": "638400574809585722",
      "EnqueuedTime": "2024-01-10T15:53:01.7730000Z"
    }
  }
]

Answer 1

Hi @AshokPeddakotla-MSFT Thank you for the support, I have tried that but there is Reserved keyword Values so I modified like this but still its having error .

SELECT
    Content.HwId,
    Data.CorrelationId,
    Data.SourceTimestamp,
    NwValues.DisplayName,
    NwValues.Value
INTO
    [accelerometerAnomaly]
FROM
   [iciiothub-1] As inputStream
CROSS APPLY GetArrayElements(inputStream.Content) AS Content
CROSS APPLY GetArrayElements(Content.Data) AS Data
CROSS APPLY GetArrayElements(Data.[Values]) AS NwValues