SQL Server SQL服务器JSON:将数据类型nvarchar转换为decimal时出错...有时

cnjp1d6j  于 2022-12-10  发布在  其他
关注(0)|答案(1)|浏览(246)

I'm having a weird problem. I have the following SQL statement trying to parse a JSON file:

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

When I run the following sample, everything works:

DECLARE @json nvarchar(max)
SET @json =
N'
{
    "body": {
        "items": [
            {
"subscription_id": "CENSORED",
"offer_id": "CENSORED",
"offer_name": "CENSORED",
"customer_id": "CENSORED",
"customer_domain": "CENSORED",
"mpn_id": "CENSORED",
"resource_group": "CENSORED",
"resource_name": "CENSORED",
"resource_type": "storageAccounts",
"resource region": null,
"meter_id": "CENSORED",
"meter_name": "LRS Data Stored",
"meter_category": "Storage",
"meter_subcategory": "Tables",
"unit": "1 GB/Month",
"quantity": 0.000096,
"msrp": 0,
"total_msrp": 0,
"unit_price": 0,
"total_price": 0,
"billing_cycle": "2022-08",
"usage_date": "08/01/2022",
"resource_tags": null,
"subscription_contract_ref": {
"contract_no": 123456,
"contract_line_no": 4,
"eu_no": 987654,
"eu_name": "Company A",
"sku_no": null
}
}
        ]
    }
}

When I have this record in the middle of a 1000 item JSON file, SQL Server seems to choke on this record with the following error:
Error converting data type nvarchar to decimal.
I have isolated this down to it having a problem with the quantity attribute, as when I remove it from the SELECT statement, all 1000 rows will appear in my results, but I REALLY need the quantity attribute.
Does anyone have any ideas?

owfi6suc

owfi6suc1#

我发现OPENJSON内部的类型转换是不可靠的--莫名其妙地返回Error converting data type nvarchar to decimal或类似的数据类型。我的解决方案是使用NVARCHAR(MAX)作为OPENJSON WITH语句内部的类型,然后在SELECT语句外部转换为所需的数据类型。

相关问题