1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Benjamin Schneider
  • 11
  • 2
  • That looks suspiciously like the schema of an Azure Cost Center billing statement... in which case let me ask you: why not use CSVs you can download from the Portal instead of faffing around with JSON? – Dai Nov 17 '22 at 23:19
  • _"but I REALLY need the quantity attribute."_ - Use T-SQL's `TRY_PARSE` to identify the rows with text that cannot be converted to `decimal(18,8)` and manually patch them in Notepad, I guess – Dai Nov 17 '22 at 23:21
  • 1
    i had to change contract_no and eu_no to strings to get valid json at jsonlint.com. 000000 is not a valid integer – smoore4 Nov 17 '22 at 23:27
  • @smoore4 `000000` **is** a valid integer in SQL Server: you can run `SELECT 000000 AS i;` and it returns `i` as an `int` column and a value of `0`. – Dai Nov 17 '22 at 23:33
  • 1
    technically you are right @Dai. But you get an error if you run the OP's code. You have to put the 00000 in quotes to not get an error. In OPENJSON, it is not a valid INT – smoore4 Nov 17 '22 at 23:36
  • 2
    @smoore4 Ah, yes - this fails: `SELECT * FROM OPENJSON( N'{ "x": 00 }', '$.x');` with the erorr _"JSON text is not properly formatted. Unexpected character '0' is found at position 7."_ - I suspect the OP will need to do some _JSON surgery_ prior to using `OPENJSON`. – Dai Nov 17 '22 at 23:40
  • 1
    Bottom line is that it's not valid JSON. ECMA 404 specifically defines: _A number is a sequence of decimal digits with no superfluous leading zero._ – AlwaysLearning Nov 18 '22 at 00:06
  • @Dai it is an azure billing statement, however it’s from our CSP distributor’s API, which doesn’t offer a CSV option. – Benjamin Schneider Nov 18 '22 at 03:30
  • The 000000 is an integer in the real file… this is anonymized real quick. I’ll correct above – Benjamin Schneider Nov 18 '22 at 03:33
  • The current sample doesn't reproduce the error message. Do you have a valid JSON sample that does? – AlwaysLearning Nov 18 '22 at 05:29

1 Answers1

0

You must look at your 1000 rows. Somewhere in there is a string that won't cast to a DECIMAL(18,8) for some reason. Try casting QUANTITY to NVARCHAR(MAX) in the OPENJSON statement and then put CAST(QUANTITY AS DECIMAL(18,8)) in the SELECT part of the statement. Then use where clauses to isolate the row or rows causing this casting problem.

Ross Attrill
  • 2,594
  • 1
  • 22
  • 31