如何在T-SQL中解析被转换为字符串的JSON数组

库存系统变更发票行存储方式，原JSON数组类型的Lines字段被转为字符串格式，导致原有解析脚本返回NULL值，无法获取正确发票行明细。

原JSON示例 #

[
    {
        "TaskID": "06896b8d-770e-426f-ac82-000183252c47",
        "InvoiceNumber": "INV-9471D",
        "Lines": "[{\"ProductID\":\"a2540764-3e28-41f0-9ae8-c348a49ee941\",\"SKU\":\"BP13V1\",\"Name\":\"Biodynamic Whole Red Lentils 1kg\",\"Quantity\":1.0,\"Price\":11.95,\"Discount\":0.0,\"Tax\":0.0,\"Total\":11.95,\"AverageCost\":8.9882,\"TaxRule\":\"GST Free Income\",\"Account\":\"202\",\"ProductCustomField10\":\"\"},{\"ProductID\":\"aae9e53e-ad96-493c-99e3-1de193961fa8\",\"SKU\":\"BP6V1\",\"Name\":\"Organic Borlotti Beans 1kg\",\"Quantity\":1.0,\"Price\":9.25,\"Discount\":0.0,\"Tax\":0.0,\"Total\":9.25,\"AverageCost\":5.8832,\"TaxRule\":\"GST Free Income\",\"Account\":\"202\",\"ProductCustomField10\":\"\"}]",
        "TotalBeforeTax": 274.29,
        "Tax": 2.04,
        "Total": 276.33,
        "Paid": 276.33
    }
]

原有T-SQL脚本 #

SELECT
    dbo.Sale.[ID],
    [Invoices_Header].Inv_Number AS Doc_Num,
    [Invoices_Lines].[ProductID],
    [Invoices_Lines].[QTY],
    [Invoices_Lines].[Price],
    [Invoices_Lines].[Discount],
    [Invoices_Lines].[Tax],
    [Invoices_Lines].[TaxRule],
    [Invoices_Lines].[Account]
FROM dbo.Sale
OUTER APPLY OPENJSON([Invoices], '$')
  WITH (
            Inv_Number VARCHAR(100)             '$.InvoiceNumber'
  ) AS [Invoices_Header]
OUTER APPLY OPENJSON([Invoices], '$.Lines')
  WITH (
            ProductID VARCHAR(100)      '$.ProductID',
            SKU VARCHAR(100)            '$.SKU',
            ProductName VARCHAR(100)    '$.Name',
            QTY DECIMAL (8,4)           '$.Quantity',
            Price NUMERIC(19,4)         '$.Price',
            Discount NUMERIC(5,2)       '$.Discount',
            Tax NUMERIC(19,4)           '$.Tax',
            Total NUMERIC(19,4)         '$.Total',
            TaxRule VARCHAR(100)        '$.TaxRule',
            Account VARCHAR(100)        '$.Account',
            AverageCost NUMERIC(19,4)   '$.AverageCost'
  ) AS [Invoices_Lines]

当前错误结果 #

期望结果 #

修改后的T-SQL脚本 #

SELECT
    dbo.Sale.[ID],
    [Invoices_Header].Inv_Number AS Doc_Num,
    [Invoices_Lines].[ProductID] AS Product_ID,
    [Invoices_Lines].[QTY],
    [Invoices_Lines].[Price],
    [Invoices_Lines].[Discount],
    [Invoices_Lines].[Tax],
    LEFT([Invoices_Lines].[TaxRule], CHARINDEX(' ', [Invoices_Lines].[TaxRule]) - 1) AS [Tax Rule],
    [Invoices_Lines].[Account],
    ROUND([Invoices_Lines].[AverageCost], 2) AS [Average Cost]
FROM dbo.Sale
OUTER APPLY OPENJSON([Invoices], '$')
  WITH (
            Inv_Number VARCHAR(100) '$.InvoiceNumber',
            Lines NVARCHAR(MAX)    '$.Lines'  -- 先提取字符串格式的Lines字段
  ) AS [Invoices_Header]
OUTER APPLY OPENJSON([Invoices_Header].Lines)  -- 解析提取出的字符串JSON数组
  WITH (
            ProductID VARCHAR(100)      '$.ProductID',
            QTY DECIMAL (8,4)           '$.Quantity',
            Price NUMERIC(19,4)         '$.Price',
            Discount NUMERIC(5,2)       '$.Discount',
            Tax NUMERIC(19,4)           '$.Tax',
            TaxRule VARCHAR(100)        '$.TaxRule',
            Account VARCHAR(100)        '$.Account',
            AverageCost NUMERIC(19,4)   '$.AverageCost'
  ) AS [Invoices_Lines]

关键修改说明 #

1. 调整发票头解析逻辑：在Invoices_Header的WITH子句中新增Lines NVARCHAR(MAX) '$.Lines'，先将存储为字符串的JSON数组字段提取出来。
2. 修正发票行解析数据源：第二个OPENJSON不再直接读取原JSON的$.Lines路径，而是使用第一步提取出的字符串格式JSON数组[Invoices_Header].Lines进行解析。
3. 适配期望结果格式：
   • 截取TaxRule字段的前半部分，得到GST Free；
   • 对AverageCost做四舍五入保留两位小数；
   • 将ProductID重命名为Product_ID，匹配期望结果的列名。

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