在SQL Server中将表内JSON数据提取为行记录的实现方法

你当前使用JSON_VALUE只能提取单个JSON对象的属性，遇到JSON数组时仅返回第一个元素。要实现将所有JSON数据（包含数组和单个对象）都拆分为独立行记录，可以利用SQL Server的OPENJSON函数结合CROSS APPLY来处理，同时兼容两种JSON格式。

解决方案查询语句 #

SELECT 
    j.ItemBarcode,
    j.ItemQty,
    j.QtyMea,
    t.Lines,
    t.StoreCode
FROM [dbo].[json_test] t
CROSS APPLY (
    -- 统一JSON格式：单个对象转为数组，数组保持原样
    SELECT *
    FROM OPENJSON(
        CASE 
            -- 判断是否为单个JSON对象：数组的$[0]会返回第一个元素，单个对象则返回NULL
            WHEN JSON_VALUE(t.Lines, '$[0]') IS NULL THEN '[' + t.Lines + ']'
            ELSE t.Lines
        END
    )
    WITH (
        ItemBarcode BIGINT '$.ItemBarcode',
        ItemQty INT '$.ItemQty',
        QtyMea VARCHAR(10) '$.QtyMea'
    )
) j

关键逻辑说明 #

• CROSS APPLY：将原表每行的JSON数据展开为多行记录，与原表的StoreCode等字段关联。
• 格式统一处理：通过CASE语句判断JSON类型，把单个对象包裹成数组，确保OPENJSON可以用相同逻辑解析所有数据。
• OPENJSON WITH：明确定义要提取的JSON属性及其对应SQL数据类型，避免类型转换问题。

执行该查询后，所有JSON中的元素都会成为独立行：

• StoreCode=1的4个JSON元素会生成4行
• StoreCode=2、3的单个JSON对象各生成1行
• StoreCode=4的2个JSON元素生成2行
  总计8行记录，完全符合需求。

注意：该方法要求SQL Server版本为2016及以上，因为OPENJSON是SQL Server 2016引入的JSON处理函数。

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