如何在Stream Analytics中查询含多数组的JSON以生成指定输出

我明白你的困扰：用CROSS JOIN处理IoT Hub传来的JSON数据时，因为TimeStamp和Checkin两个数组的笛卡尔积，得到了4条结果，但你需要的是两个数组按索引位置一一对应的2条输出。这确实是初学者容易踩的坑，下面给你具体的解决方案：

核心思路：避免笛卡尔积，按索引匹配数组元素 #

CROSS JOIN会把两个数组的所有元素两两组合，所以2个元素的数组交叉后就是4条数据。我们需要的是遍历数组的索引，同时从两个数组中提取对应位置的元素，这可以通过CROSS APPLY结合数组函数实现。

完整查询语句 #

把[YourInputAlias]替换成你在Stream Analytics里配置的输入别名即可：

SELECT
    input.DeviceId,
    input.Message.Streak,
    GetArrayElement(input.Message.TimeStamp, idx.ArrayIndex).Time AS TimeStamp,
    GetArrayElement(input.Message.Checkin, idx.ArrayIndex).Time AS Checkin,
    input.Message.Status
FROM
    [YourInputAlias] input
CROSS APPLY GetArrayElements(GenerateArray(0, GetArrayLength(input.Message.TimeStamp) - 1)) AS idx
-- 可选：确保两个数组长度一致，防止索引越界
WHERE
    GetArrayLength(input.Message.TimeStamp) = GetArrayLength(input.Message.Checkin)

逐行解释 #

• GenerateArray(0, GetArrayLength(input.Message.TimeStamp)-1)：生成一个从0开始到数组最后一个索引的数字数组（比如你的TimeStamp有2个元素，就生成[0,1]）。
• CROSS APPLY GetArrayElements(...) AS idx：遍历这个索引数组，每次取出一个索引值存在idx.ArrayIndex里。
• GetArrayElement(..., idx.ArrayIndex).Time：用当前索引分别从TimeStamp和Checkin数组里提取对应位置的Time值，这样就实现了两个数组元素的一一配对。
• WHERE条件：建议加上这条，避免当两个数组长度不一致时出现索引越界的问题，保证输出的准确性。

测试结果 #

用你提供的输入数据运行这个查询，会得到你期望的输出：

内容的提问来源于stack exchange，提问作者Swapnil Kushwaha