SQL Server为JSON查询添加WITH子句指定数据类型解决格式报错

• 运行环境为SQL Server，源数据通过Data Factory从Soap API抽取后存入stage.Bill表，表内xmldata字段实际存储JSON格式数据，字段名是早期测试XML格式返回时遗留未修改。
• API返回的invoiceDetails字段存在两种JSON结构：单Object结构、Array结构，因此通过UNION ALL拼接两段分别适配两种结构的查询逻辑构建业务视图。
• 查询本身可以直接执行，但对生成的视图做排序、筛选操作时会抛出如下错误：

JSON text is not properly formatted. Unexpected character '.' is found at position 12.

• 已验证问题根源：未给JSON解析出的字段显式指定数据类型，添加WITH子句显式声明类型后视图可正常支持排序、筛选操作，但不清楚如何在现有查询结构中正确添加WITH子句。

/*Object - invoiceDetails*/
SELECT 
        XML.[CustomerID],
        XML.[SiteId],
        XML.[Date],
        JSON_VALUE(m.[value], '$.accountNbr') AS [AccountNumber],
        JSON_VALUE(m.[value], '$.actualUsage') AS [ActualUsage]

FROM stage.Bill XML
    CROSS APPLY openjson(XML.xmldata) AS n
    CROSS APPLY openjson(n.value, '$.invoiceDetails') AS m
    WHERE XML.XmlData IS NOT NULL
AND ISJSON (XML.xmldata) > 0
AND n.type = 5
AND m.type = 5

UNION ALL

/*Array - invoiceDetails*/
    SELECT 
        XML.[CustomerID],
        XML.[SiteId],
        XML.[Date],
        JSON_VALUE(o.[value], '$.accountNbr') AS [AccountNumber],
        JSON_VALUE(o.[value], '$.actualUsage') AS [ActualUsage]

FROM stage.Bill XML
    CROSS APPLY openjson(XML.xmldata) AS n
    CROSS APPLY openjson(n.value) AS m
    CROSS APPLY openjson(m.value, '$.invoiceDetails') AS o
    WHERE XML.XmlData IS NOT NULL
AND ISJSON (XML.xmldata) > 0
AND n.type = 4

原写法直接用JSON_VALUE提取字段没有做强制类型绑定，视图生成后会采用延迟解析逻辑，当执行排序、筛选时SQL Server可能尝试对非目标路径的JSON节点做解析，触发格式错误。使用OPENJSON...WITH语法可以在解析层就固定字段提取路径和数据类型，避免延迟解析带来的异常，修改后的代码如下：

/* 适配invoiceDetails为Object结构的分支 */
SELECT 
    b.[CustomerID],
    b.[SiteId],
    b.[Date],
    m.[AccountNumber],
    m.[ActualUsage]
FROM stage.Bill b
CROSS APPLY OPENJSON(b.xmldata) AS n
CROSS APPLY OPENJSON(n.value, '$.invoiceDetails') 
WITH (
    AccountNumber NVARCHAR(50) '$.accountNbr',
    ActualUsage DECIMAL(18,4) '$.actualUsage'
) AS m
WHERE b.XmlData IS NOT NULL
    AND ISJSON(b.xmldata) > 0
    AND n.type = 5

UNION ALL

/* 适配invoiceDetails为Array结构的分支 */
SELECT 
    b.[CustomerID],
    b.[SiteId],
    b.[Date],
    o.[AccountNumber],
    o.[ActualUsage]
FROM stage.Bill b
CROSS APPLY OPENJSON(b.xmldata) AS n
CROSS APPLY OPENJSON(n.value) AS m
CROSS APPLY OPENJSON(m.value, '$.invoiceDetails')
WITH (
    AccountNumber NVARCHAR(50) '$.accountNbr',
    ActualUsage DECIMAL(18,4) '$.actualUsage'
) AS o
WHERE b.XmlData IS NOT NULL
    AND ISJSON(b.xmldata) > 0
    AND n.type = 4

注意事项 #

• WITH子句内定义的字段数据类型可根据实际业务调整：如果accountNbr为纯数字可替换为BIGINT类型，如果actualUsage为整数可替换为INT类型。两个UNION ALL分支的同名字段数据类型必须完全一致，避免隐式类型转换带来的额外开销或报错。
• 去掉了原查询中冗余的m.type = 5判断：OPENJSON搭配WITH子句解析指定路径时，会自动跳过不符合结构的节点，无需额外加类型判断过滤。
• 示例中将原表别名XML替换为b，避免和SQL Server内置的XML数据类型关键字混淆，如果习惯原有别名可自行替换，不影响逻辑执行。
• 该写法直接在OPENJSON层完成字段提取和类型绑定，视图生成时会固定输出列的数据类型，后续排序、筛选时不会触发延迟JSON解析，从根源上避免JSON格式解析错误。

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