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

Novin George Thomas 0 Reputation points
2024-01-12T08:22:59.1733333+00:00

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"
    }
  }
]
Azure IoT Edge
Azure IoT Edge
An Azure service that is used to deploy cloud workloads to run on internet of things (IoT) edge devices via standard containers.
535 questions
Azure IoT Hub
Azure IoT Hub
An Azure service that enables bidirectional communication between internet of things (IoT) devices and applications.
1,116 questions
Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
330 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Novin George Thomas 0 Reputation points
    2024-01-12T09:46:15.4133333+00:00

    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
    0 comments