SQL Server解析JSON时nvarchar转decimal报错求助

问题背景 #

使用指定SQL解析JSON文件时，单条测试数据可正常运行，但包含1000条数据的文件触发错误：

Error converting data type nvarchar to decimal.

经排查问题出在quantity字段，移除该字段后可正常返回全部数据，但业务需要保留该字段。

原SQL语句：

SELECT 
    A.subscription_id   AS subscriptionid,
    A.customer_id       AS customerid,
    A.customer_domain   AS customerdomain,
    A.mpn_id            AS mpnid,
    A.resource_group    AS resourcegroup,
    A.resource_name     AS resourcename,
    A.resource_type     AS resourcetype,
    A.[resource region] AS resourceregion,
    A.meter_id          AS meterid,
    A.meter_name        AS metername,
    A.meter_category    AS metercategory,
    A.meter_subcategory AS metersubcategory,
    A.unit              AS unit,
    A.quantity          AS quantity,
    A.msrp              AS msrp,
    A.unit_price        AS unitprice,
    A.billing_cycle     AS billingcycle,
    A.usage_date        AS usagedate,
    A.resource_tags     AS resourcetags,
    B.contract_no       AS contractno,
    B.contract_line_no  AS contractlineno,
    B.eu_no             AS euno,
    B.eu_name           AS euname
FROM
    OPENJSON(@json, '$.body."items"')
    WITH(
        subscription_id     VARCHAR(50),
        customer_id         VARCHAR(50),
        customer_domain     VARCHAR(50),
        mpn_id              VARCHAR(50),
        resource_group      VARCHAR(50),
        resource_name       VARCHAR(50),
        resource_type       VARCHAR(50),
        [resource region]   VARCHAR(50),
        meter_id            VARCHAR(50),
        meter_name          VARCHAR(50),
        meter_category      VARCHAR(50),
        meter_subcategory   VARCHAR(50),
        unit                VARCHAR(50),
        quantity            DECIMAL(18,8),
        msrp                DECIMAL(18,8),
        unit_price          DECIMAL(18,8),
        billing_cycle       VARCHAR(50),
        usage_date          VARCHAR(50),
        resource_tags       VARCHAR(50),
        subscription_contract_ref   NVARCHAR(MAX) as JSON
    ) AS A
CROSS APPLY 
    OPENJSON(A.subscription_contract_ref)
    WITH(
        contract_no         INT,
        contract_line_no    INT,
        eu_no               INT,
        eu_name             VARCHAR(50)
    ) as B

解决方案 #

错误原因是某条数据的quantity字段值无法直接转换为DECIMAL(18,8)（比如是字符串格式数字、null、包含非数字字符等），可通过以下两种方式处理：

方法1：安全转换避免查询中断 #

修改OPENJSON的WITH子句，先将quantity读取为字符串类型，再用TRY_CONVERT做安全转换（转换失败时返回NULL，而非抛出错误）：

SELECT 
    A.subscription_id   AS subscriptionid,
    A.customer_id       AS customerid,
    A.customer_domain   AS customerdomain,
    A.mpn_id            AS mpnid,
    A.resource_group    AS resourcegroup,
    A.resource_name     AS resourcename,
    A.resource_type     AS resourcetype,
    A.[resource region] AS resourceregion,
    A.meter_id          AS meterid,
    A.meter_name        AS metername,
    A.meter_category    AS metercategory,
    A.meter_subcategory AS metersubcategory,
    A.unit              AS unit,
    -- 安全转换quantity，失败返回NULL；如需默认值可改为ISNULL(TRY_CONVERT(...), 0)
    TRY_CONVERT(DECIMAL(18,8), A.quantity) AS quantity,
    TRY_CONVERT(DECIMAL(18,8), A.msrp) AS msrp,
    TRY_CONVERT(DECIMAL(18,8), A.unit_price) AS unitprice,
    A.billing_cycle     AS billingcycle,
    A.usage_date        AS usagedate,
    A.resource_tags     AS resourcetags,
    B.contract_no       AS contractno,
    B.contract_line_no  AS contractlineno,
    B.eu_no             AS euno,
    B.eu_name           AS euname
FROM
    OPENJSON(@json, '$.body."items"')
    WITH(
        subscription_id     VARCHAR(50),
        customer_id         VARCHAR(50),
        customer_domain     VARCHAR(50),
        mpn_id              VARCHAR(50),
        resource_group      VARCHAR(50),
        resource_name       VARCHAR(50),
        resource_type       VARCHAR(50),
        [resource region]   VARCHAR(50),
        meter_id            VARCHAR(50),
        meter_name          VARCHAR(50),
        meter_category      VARCHAR(50),
        meter_subcategory   VARCHAR(50),
        unit                VARCHAR(50),
        -- 先读取为字符串类型
        quantity            NVARCHAR(50),
        msrp                NVARCHAR(50),
        unit_price          NVARCHAR(50),
        billing_cycle       VARCHAR(50),
        usage_date          VARCHAR(50),
        resource_tags       VARCHAR(50),
        subscription_contract_ref   NVARCHAR(MAX) as JSON
    ) AS A
CROSS APPLY 
    OPENJSON(A.subscription_contract_ref)
    WITH(
        contract_no         INT,
        contract_line_no    INT,
        eu_no               INT,
        eu_name             VARCHAR(50)
    ) as B

方法2：定位并修复异常数据 #

如果需要找出具体的错误记录，可以先读取quantity为字符串，筛选出无法转换的条目：

SELECT 
    A.subscription_id,
    A.quantity,
    -- 其他需排查的字段可按需添加
    A.subscription_contract_ref
FROM
    OPENJSON(@json, '$.body."items"')
    WITH(
        subscription_id     VARCHAR(50),
        quantity            NVARCHAR(50),
        subscription_contract_ref   NVARCHAR(MAX) as JSON
    ) AS A
WHERE TRY_CONVERT(DECIMAL(18,8), A.quantity) IS NULL
  AND A.quantity IS NOT NULL

找到异常数据后，修正JSON中的对应值（比如将非数字内容改为合法数值），再用原SQL解析即可。

说明 #

• TRY_CONVERT支持SQL Server 2012及以上版本，会尝试转换数据类型，失败时返回NULL，不会中断整个查询。

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