使用Stream Analytics将IoT Hub的Modbus格式JSON转换为PowerBI流式数据集

这种嵌套无命名数组的上报格式在Modbus、OPC-UA这类工业IoT设备里确实很常见，Stream Analytics处理这类数据需要逐层拆解数组结构，最终把分散在数组项里的传感器值转成PowerBI友好的扁平格式。下面是具体的实现步骤和查询代码：

步骤说明 #

你的输入数据里，Data字段是一个双层嵌套数组（[[传感器对象1, 传感器对象2]]），我们需要：

1. 先拆解外层数组，取出包含两个传感器对象的内层数组
2. 再拆解内层数组，把每个传感器对象单独提取出来
3. 用行转列的方式把DisplayName的值转成列名（即humidity和temperature），同时关联对应的时间戳

完整Stream Analytics查询 #

WITH Step1 AS (
    -- 拆解外层Data数组，取出内层的传感器数组
    SELECT
        GetArrayElement(GetArrayElement(Data, 0), 0).SourceTimestamp AS [timestamp],
        GetArrayElement(Data, 0) AS SensorArray
    FROM [YourIoTHubInputAlias]
),
Step2 AS (
    -- 拆解内层传感器数组，得到每个单独的传感器数据项
    SELECT
        [timestamp],
        SensorItem.DisplayName AS MetricName,
        -- 把Value转成数值类型（方便PowerBI做数据分析）
        CAST(SensorItem.Value AS FLOAT) AS MetricValue
    FROM Step1
    CROSS APPLY GetArrayElements(SensorArray) AS SensorItem
)
-- 行转列生成PowerBI需要的扁平格式
SELECT
    [timestamp],
    MAX(CASE WHEN MetricName = 'Humidity' THEN MetricValue END) AS humidity,
    MAX(CASE WHEN MetricName = 'Temperature' THEN MetricValue END) AS temperature
FROM Step2
GROUP BY [timestamp]

关键细节解释 #

• GetArrayElement的使用：因为数组没有命名键，我们通过索引（比如GetArrayElement(Data, 0)）来取数组的第一个元素——这是因为你的每个消息里Data的外层数组只有一个元素（就是包含两个传感器的数组）。
• 类型转换：把Value转成FLOAT是为了让PowerBI能直接做数值计算，如果你需要保留原始字符串格式，可以去掉CAST转换。
• 时间戳处理：你的数据里两个传感器的SourceTimestamp完全一致，所以我们直接从第一个传感器对象提取即可；如果后续出现时间戳不一致的情况，可以根据需求调整逻辑。
• 替代PIVOT语法：如果你更习惯用Stream Analytics原生的PIVOT运算符，也可以写成下面的形式：

WITH ParsedData AS (
    SELECT
        GetArrayElement(GetArrayElement(Data, 0), 0).SourceTimestamp AS [timestamp],
        SensorItem.DisplayName AS MetricName,
        CAST(SensorItem.Value AS FLOAT) AS MetricValue
    FROM [YourIoTHubInputAlias]
    CROSS APPLY GetArrayElements(GetArrayElement(Data, 0)) AS SensorItem
)
SELECT [timestamp], humidity, temperature
FROM ParsedData
PIVOT (
    MAX(MetricValue)
    FOR MetricName IN (humidity, temperature)
) AS PivotResult

这个查询输出的结果就是你需要的格式：每行包含timestamp、humidity、temperature三个字段，直接导入PowerBI即可使用。

内容的提问来源于stack exchange，提问作者Dave Banthorpe