如何用CROSS APPLY OPENJSON解析传感器二维JSON数据并关联对应轴值

我有一个3通道传感器的数据，每10分钟采集一次，数据以JSON格式存储在表的单个列中。每个时间戳ts对应一组Y值数组（Intensities）和对应的X值数组（XAxis_nm）。

单列JSON示例（列名假设为colJsonText）：

{"ts": "2024-04-17T10:10:00", "Intensities": [101, 102, 103], "XAxis_nm": [410, 420, 430]}

我需要查询后得到这样的结果：每行展示时间戳、对应的单个Intensity值和匹配位置的单个XAxis_nm值。

我尝试了以下查询（包含2行示例数据）：

SELECT ts, Intensity, XAxis_nm FROM OPENJSON('[{"ts": "2024-04-17T10:10:00", "Intensities": [101, 102, 103], "XAxis_nm": [410, 420, 430]}, {"ts": "2024-04-17T10:20:00", "Intensities": [201, 202, 203], "XAxis_nm": [410, 420, 430]}]') WITH (ts nvarchar(32), Intensities NVARCHAR(MAX) AS JSON, XAxis_nm NVARCHAR(MAX) AS JSON) AS a CROSS APPLY OPENJSON (a.Intensities) WITH (Intensity INT '$')

但当前查询结果中XAxis_nm显示的是整个数组，而非对应位置的单个值，请问如何修改查询以得到目标结果？

可以通过给数组元素添加行号，再根据行号匹配对应位置的Intensities和XAxis_nm值来实现。以下是完整的SQL示例（包含扩展的测试数据）：

DECLARE @t TABLE (id INT IDENTITY(1,1) not null, rawjson nvarchar(2000) NULL)
INSERT INTO @t VALUES
('[
{"Sensor": "S01", "ts": "2024-04-17T10:10:00", "Intensities": [101, 102, 103], "XAxis_nm": [410, 420, 430], "Context": ["410nm", "420nm", "430nm"]}
, {"Sensor": "S01", "ts": "2024-04-17T10:20:00", "Intensities": [201, 202, 203], "XAxis_nm": [410, 420, 430], "Context": ["410nm", "420nm", "430nm"]}
, {"Sensor": "S01", "ts": "2024-04-17T10:30:00", "Intensities": [210, 102, 203], "XAxis_nm": [410, 420, 430], "Context": ["410nm", "420nm", "430nm"]}
, {"Sensor": "S02", "ts": "2024-04-17T10:30:00", "Intensities": [210, 1020, 203], "XAxis_nm": [420, 410, 430], "Context": ["410nm", "420nm", "430nm"]}
]')

select 
    c.ts,
    c.X,
    Intensity = JSON_VALUE(c.json, '$['+ CAST(c.RowNum-1 as varchar(5)) +']'),
    c.SensorName,
    Context = JSON_VALUE(c.json1, '$['+ CAST(c.RowNum-1 as varchar(5)) +']')
from (
    SELECT 
        RowNum = ROW_NUMBER() OVER(PARTITION BY Sensor, ts ORDER BY ts ASC),
        ts = CAST(ts as DateTime), 
        X = Cast(XValue as int), 
        SensorName = Sensor,
        Intensities as json,
        Context as json1
    FROM @t t
    CROSS APPLY OPENJSON(t.rawjson) WITH (
                ts nvarchar(32), 
                Sensor nvarchar(32), 
                XAxis_nm nvarchar(max) as JSON,
                Intensities NVARCHAR(MAX) AS JSON,
                Context NVARCHAR(MAX) AS JSON
                )
    CROSS APPLY OPENJSON (XAxis_nm) WITH (XValue INT '$')
) c 

核心思路 #

1. 用OPENJSON解析外层JSON，提取ts、Sensor以及需要处理的数组字段（XAxis_nm、Intensities、Context）。
2. 展开XAxis_nm数组，同时用ROW_NUMBER()按Sensor和ts分组生成行号，标记每个X值的位置。
3. 利用JSON_VALUE，根据行号（数组索引从0开始，所以行号减1）从Intensities和Context数组中提取对应位置的值，实现数组元素的一一匹配。

内容的提问来源于stack exchange，提问作者Joe Kunze