在SQL Server中如何将JSON行表转换为结构化表格

需求说明 #

现有一张表，其中value字段存储JSON格式字符串，需要将JSON中的各个键提取为独立列，生成结构化表格。

原始输入表 #

目标结构化表格 #

测试数据 #

DECLARE @r TABLE (
   value VARCHAR(255)
)
INSERT INTO @r VALUES
     (N'{"Date":"2022-10-31","Delta":5,"Comment":null}'),
     (N'{"Date":"2022-11-01","Delta":5,"Comment":null}');

解决方案 #

使用SQL Server的OPENJSON函数解析JSON字符串，配合CROSS APPLY关联原表，精准提取JSON中的键为结构化列：

SELECT 
    Date,
    Delta,
    Comment
FROM @r
CROSS APPLY OPENJSON(value)
WITH (
    Date DATE '$.Date',
    Delta INT '$.Delta',
    Comment VARCHAR(50) '$.Comment'
);

说明 #

• OPENJSON函数负责将JSON字符串转换为行集，WITH子句可以指定输出列的名称、数据类型以及对应的JSON路径，既能拆分字段，又能直接转换数据类型。
• CROSS APPLY用于关联原表每行数据与解析后的JSON结果，确保每条JSON记录都能被正确拆分映射。

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