reading JSON values .. in SQL 2012

Question

I have a SQL Server 2012 table with a column as per:

ResponseJSON varchar(max)

It contains text values like

{
  "partNumber": 1,
  "partTotal": 1,
  "payeeLocationId": "ABC00011",
  "remittanceAdviceId": "77592",
  "paymentInfo": {
    "accountInfo": {
      "accountName": "ABC Hospital",
      "xyzNumber": "",
      "xyzCode": ""
    },
    "depositAmount": "1234",
    "paymentReference": "ERA 1234"
  },
  "paymentRun": {
    "payerName": "ABC",
    "runDate": "2022-12-05"
  },
  "claimSummary": [
    {
      "benefit": "5555",
      "channelCode": "ABC",
      "claimId": "1234",
      "lodgementDate": "2022-02-14",
      "transactionId": "xpxpxpxpxxp",
      "accountReferenceId": "12345678"
    }
  ]
}

I wondered how to read the remittanceAdviceId value of 77592 (in this case) out of this JSON column data .. The remittanceAdviceId may be varying size in length .. e.g. 1,2,3,4,5,6,7 etc digits

I considered something like :

SELECT remittanceAdviceId = CASE
         WHEN E.RequestJSON IS NOT NULL AND
              CHARINDEX('"remittanceAdviceId"', E.RequestJSON, 0) > 0 THEN
          SUBSTRING(E.RequestJSON,
                    CHARINDEX('"remittanceAdviceId"', E.RequestJSON, 0) + 22,
                    5)
         ELSE
          NULL
       END

but this isn't quite right as value may be other than 5 digits ..

Don't write a json parser in SQL. You will fail. Objects with escaped characters will break your code, or if the search key appears in a value elsewhere in the object. Upgrade the sql server to 2016+ (to get native json functions), or parse it before you insert it into the database, with a standard library of your choice. (Or perhaps write your own function using the CLR, so you can use the json libraries in .Net) — MatBailie, Dec 06 '22 at 11:20
Does this answer your question [How to parse json data in SQL Server 2012?](https://stackoverflow.com/questions/41094989/how-to-parse-json-data-in-sql-server-2012) — Dordi, Dec 06 '22 at 12:55
Considering that SQL Server 2012 is completely*unsupported" you should be close to implementing, or at least finalising, your upgrade path. Perhaps the need to parse JSON data will help accelerate that implementation. — Thom A, Dec 06 '22 at 14:01

critical_error · Accepted Answer · 2022-12-07T17:11:59.250

1

Assuming upgrading to the latest version of SQL isn't in the cards right now, here's a simple approach using SUBSTRING and CHARINDEX:

DECLARE @json varchar(2000) = '{
  "partNumber": 1,
  "partTotal": 1,
  "payeeLocationId": "ABC00011",
  "remittanceAdviceId": "77592",
  "paymentInfo": {
    "accountInfo": {
      "accountName": "ABC Hospital",
      "xyzNumber": "",
      "xyzCode": ""
    },
    "depositAmount": "1234",
    "paymentReference": "ERA 1234"
  },
  "paymentRun": {
    "payerName": "ABC",
    "runDate": "2022-12-05"
  },
  "claimSummary": [
    {
      "benefit": "5555",
      "channelCode": "ABC",
      "claimId": "1234",
      "lodgementDate": "2022-02-14",
      "transactionId": "xpxpxpxpxxp",
      "accountReferenceId": "12345678"
    }
  ]
}';

SELECT SUBSTRING (
    @json
    , CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) + 23
    , CHARINDEX ( '",', @json, CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) ) - CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) - 23
) AS remittanceAdviceId;

RETURNS

+--------------------+
| remittanceAdviceId |
+--------------------+
|              77592 |
+--------------------+

NOTES

Assumes valid JSON with quoted values.
There is no need to specify a length for the remittance id. It will get parsed accordingly.

UPDATE

Now that you know you can use the native JSON feature in SQL, the simplest way to extract a single value from JSON is:

SELECT JSON_VALUE ( @json, '$.remittanceAdviceId' ) AS remittanceAdviceId;

RETURNS

+--------------------+
| remittanceAdviceId |
+--------------------+
|              77592 |
+--------------------+

edited Dec 07 '22 at 17:11

answered Dec 06 '22 at 21:58

critical_error

6,306
3
14
16

Many thanks ! Much appreciated ! .. I have learnt that all our customers are on SQL Server 2017 or higher now .. but I only learnt this today. Will consider using this ... or use of new JSON functions like OPENJSON .. – Allan F Dec 07 '22 at 01:07
The OpenJson equivalent seems to be select value as remittanceAdviceId from OpenJson(@json) where [key] = 'remittanceAdviceId' – Allan F Dec 07 '22 at 01:18
1

Use `JSON_VALUE` to extract a singular field's value. – critical_error Dec 07 '22 at 16:31
1

@AllanF, I added a minor update to show the use of `JSON_VALUE`. – critical_error Dec 07 '22 at 17:12
1

@AllanF even SQL Server 2017 is out of mainstream support right now. It's 5 years old after all. It's still in extended support though, and lockdowns delayed everyone's upgrade plans – Panagiotis Kanavos Dec 07 '22 at 17:13
Also, as just attempting to read a number value from json, worth doing something like this also (to remove Spaces, Tabs, LF, CR ) : RequestJSON = REPLACE(REPLACE(REPLACE(REPLACE(E.RequestJSON, CHAR(32), ''), CHAR(9),''), CHAR(10),''), CHAR(13),'') – Allan F Dec 13 '22 at 04:51
CASE WHEN T.RequestJSON IS NOT NULL AND CHARINDEX('"paymentReference":"', T.RequestJSON, 0) > 0 THEN SUBSTRING ( T.RequestJSON , CHARINDEX ( '"paymentReference":"', T.RequestJSON, 0 ) + 20 , ( CHARINDEX ( '"', T.RequestJSON, CHARINDEX ( '"paymentReference":"', T.RequestJSON, 0 ) + 20 ) ) - (CHARINDEX ( '"paymentReference":"', T.RequestJSON, 0 ) + 20 ) ) ELSE NULL END – Allan F Dec 13 '22 at 04:52

reading JSON values .. in SQL 2012

1 Answers1