Azure Synapse批量解析数据湖JSON遥测数据为表格的问题

错误原因 #

原查询试图将OPENROWSET返回的多条JSON记录赋值给单个变量@json2，当子查询返回多行结果时，就会触发Subquery returned more than 1 value错误——单个变量无法存储多条数据，后续的OPENJSON也无法正确解析多条JSON的混合内容。

修正后的查询语句 #

SELECT 
    JSON_VALUE(json2.json2, '$.DeviceId') AS Device,
    [MeasureType],
    [MeasureValue],
    [DataPointTimestamp]
FROM
    OPENROWSET(
        BULK 'https://xxx.dfs.core.windows.net/raw/100/103/xxxx/2023/10/23/*.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '0x0b'
    )
    WITH (
        json2 varchar(MAX)
    ) AS json2
CROSS APPLY OPENJSON(json2.json2, '$.MeasureList') WITH (
   [MeasureType] nvarchar(max) '$.MeasureType',
   [MeasureValue] nvarchar(max) '$.MeasureValue',
   [DataPointTimestamp] nvarchar(max) '$.DataPointTimestamp'  
) AS measureItems

关键修改说明 #

1. 移除变量赋值：直接对OPENROWSET返回的每条JSON记录进行处理，避免多条数据存入单个变量的问题
2. 使用CROSS APPLY：将每条JSON记录与OPENJSON解析出的MeasureList数组条目关联，实现批量展开嵌套JSON数组，每条数组元素对应结果集中的一行
3. 关联设备ID：从每条原始JSON中提取DeviceId，与展开后的测量项数据一一对应，生成符合期望的扁平化表格结构

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