如何使用T-SQL将嵌套JSON数据展平到SQL Server表中

原代码的核心问题 #

1. 错误拆分单个JSON对象：当@json是单个交易对象（如示例）时，OPENJSON(@json)会将其拆分为键值对集合（键为orderId/openTime等，值为对应属性内容），此时c.Value是单个属性的值，无法通过JSON_VALUE(c.Value, '$.orderId')获取完整订单信息。
2. 路径转义冗余：$."products"中的转义符多余，直接写$.products即可正确定位商品数组。
3. 不合理的类型转换：将数值/日期类型统一转为NVARCHAR会增加后续数据处理成本，应匹配SQL对应数据类型（如INT、DECIMAL、DATETIME2）。

修正后的实现代码 #

方案1：直接用JSON_VALUE提取顶层字段 + 展开商品数组

适用于stg_transactionsJson表每条记录为单个交易JSON的场景：

SELECT 
    -- 顶层交易字段
    CONVERT(INT, JSON_VALUE(b.JsonPath, '$.orderId')) AS orderId,
    CONVERT(DATETIME2, JSON_VALUE(b.JsonPath, '$.openTime')) AS openTime,
    CONVERT(DATETIME2, JSON_VALUE(b.JsonPath, '$.closeTime')) AS closeTime,
    CONVERT(INT, JSON_VALUE(b.JsonPath, '$.operatorId')) AS operatorId,
    CONVERT(INT, JSON_VALUE(b.JsonPath, '$.terminalId')) AS terminalId,
    CONVERT(INT, JSON_VALUE(b.JsonPath, '$.sessionId')) AS sessionId,
    -- 商品明细字段
    CONVERT(INT, JSON_VALUE(p.Value, '$.productGroupId')) AS productGroupId,
    CONVERT(INT, JSON_VALUE(p.Value, '$.productId')) AS productId,
    CONVERT(INT, JSON_VALUE(p.Value, '$.quantity')) AS quantity,
    CONVERT(DECIMAL(18,2), JSON_VALUE(p.Value, '$.taxValue')) AS taxValue,
    CONVERT(DECIMAL(18,2), JSON_VALUE(p.Value, '$.value')) AS ProductValue,
    CONVERT(INT, JSON_VALUE(p.Value, '$.priceBandId')) AS priceBandId,
    GETDATE() AS DateUpdated
FROM [dbo].[stg_transactionsJson] b
OUTER APPLY OPENJSON(b.JsonPath, '$.products') AS p;

方案2：用OPENJSON WITH子句结构化解析（更易读）

通过WITH子句直接定义JSON字段与SQL类型的映射，可读性和维护性更强：

SELECT 
    t.orderId,
    t.openTime,
    t.closeTime,
    t.operatorId,
    t.terminalId,
    t.sessionId,
    p.productGroupId,
    p.productId,
    p.quantity,
    p.taxValue,
    p.value AS ProductValue,
    p.priceBandId,
    GETDATE() AS DateUpdated
FROM [dbo].[stg_transactionsJson] b
-- 解析顶层交易对象
OUTER APPLY OPENJSON(b.JsonPath)
WITH (
    orderId INT '$.orderId',
    openTime DATETIME2 '$.openTime',
    closeTime DATETIME2 '$.closeTime',
    operatorId INT '$.operatorId',
    terminalId INT '$.terminalId',
    sessionId INT '$.sessionId',
    products NVARCHAR(MAX) '$.products' AS JSON -- 保留商品数组为JSON格式，用于后续展开
) t
-- 展开商品数组
OUTER APPLY OPENJSON(t.products)
WITH (
    productGroupId INT '$.productGroupId',
    productId INT '$.productId',
    quantity INT '$.quantity',
    taxValue DECIMAL(18,2) '$.taxValue',
    value DECIMAL(18,2) '$.value',
    priceBandId INT '$.priceBandId'
) p;

扩展：处理交易数组场景 #

如果stg_transactionsJson中的JsonPath是包含多个交易的数组（如[{"orderId":431,...}, {...}]），上述方案2无需修改，OPENJSON(b.JsonPath)会自动遍历数组中的每个交易对象。

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