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
– MatBailie, Commented Dec 6, 2022 at 11:20
Does this answer your question How to parse json data in SQL Server 2012? — Dordi
– Dordi, Commented Dec 6, 2022 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
– Thom A ♦, Commented Dec 6, 2022 at 14:01

critical_error · Accepted Answer · 2022-12-07 17:11:59Z

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 7, 2022 at 17:11

answered Dec 6, 2022 at 21:58

critical_error

6,8263 gold badges16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Allan F Over a year ago

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 Over a year ago

The OpenJson equivalent seems to be select value as remittanceAdviceId from OpenJson(@json) where [key] = 'remittanceAdviceId'

critical_error Over a year ago

Use JSON_VALUE to extract a singular field's value.

critical_error Over a year ago

@AllanF, I added a minor update to show the use of JSON_VALUE.

Panagiotis Kanavos Over a year ago

@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

|

Collectives™ on Stack Overflow

reading JSON values .. in SQL 2012

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related