SQL Server中如何将字符串格式JSON列展开/规范化为行与列

假设你的表名为 PricePoints，包含 PricePointId（整数类型）和 Prices（存储JSON字符串的nvarchar(max)类型）列，用以下SQL语句可以实现需求：

SELECT 
    pp.PricePointId,
    p.id,
    p.component_id,
    p.starting_quantity,
    p.ending_quantity,
    p.unit_price,
    p.price_point_id,
    p.formatted_unit_price,
    p.segment_id
FROM PricePoints pp
CROSS APPLY OPENJSON(pp.Prices)
WITH (
    id INT '$.id',
    component_id INT '$.component_id',
    starting_quantity INT '$.starting_quantity',
    ending_quantity INT '$.ending_quantity',
    unit_price VARCHAR(20) '$.unit_price',
    price_point_id INT '$.price_point_id',
    formatted_unit_price VARCHAR(20) '$.formatted_unit_price',
    segment_id INT '$.segment_id'
) AS p

代码说明 #

• OPENJSON(pp.Prices)：解析Prices列中的JSON数组，返回数组中每个对象的键值对。
• WITH子句：将JSON对象的每个键映射为SQL列，同时指定对应的数据类型和JSON路径（$.键名表示当前JSON对象下的该键）。
• CROSS APPLY：将原表的每一行与OPENJSON返回的该行对应的所有JSON对象行进行关联，实现一行拆多行的效果。

示例结果 #

执行后会得到类似以下的结构（对应你提供的示例数据）：

为什么STRING_SPLIT不适用 #

STRING_SPLIT只能将字符串按指定分隔符拆分为行，但无法解析JSON的层级结构，所以无法直接提取每个JSON对象中的键值对并转换为列。

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