How to parse JSON into relational format in SQL Server 2016?

Question

I have some Json stored in SQL Server 2016 table as under (partitial)

{
  "AFP": [
    {
      "AGREEMENTID": "29040400001330",
      "LoanAccounts": {
        "Product": "OD003",
        "BUCKET": 0,
        "ZONE": "MUMBAI ZONE",
        "Region": "MUMBAI METRO-CENTRAL REGION",
        "STATE": "GOA",
        "Year": 2017,
        "Month": 10,
        "Day": 13
      },
      "FeedbackInfo": {
        "FeedbackDate": "2017-10-13T12:07:44.2317198",
        "DispositionDate": "2017-10-13T12:07:44.2317198",
        "DispositionCode": "PR"
      },
      "PaymentInfo": {
        "ReceiptNo": "2000000170",
        "ReceiptDate": "2017-10-13T12:07:42.1218299",
        "PaymentMode": "Cheque",
        "Amount": 200,
        "PaymentStatus": "CollectionBatchCreated"
      }
    }
  ]
}

table schema as under

create table tblHistoricalDataDemo(
AGREEMENTID nvarchar(40)
,Year_Json nvarchar(4000)
)

I would like to fetch the records from JSON into relational format as

AgreementID Product Bucket .... PaymentStatus

I tried with below but something wrong i am doing for which I am not able to get the result

SELECT AGREEMENTID, 
  JSON_VALUE(Year_Json, '$.LoanAccounts') AS records
FROM tblHistoricalDataDemo

I think you need OPENJSON(), not JSON_VALUE(). See the docs. — Aaron Bertrand
– Aaron Bertrand, Commented May 1, 2018 at 17:25

Zohar Peled · Accepted Answer · 2018-05-01 17:41:03Z

2

Use the OPENJSON built in table value function:

SELECT *
FROM tblHistoricalDataDemo
CROSS APPLY 
    OPENJSON(Year_Json, '$.AFP') WITH
    (
    -- You don't have to specify the json path
    -- if the column name is the same as the json name
        AGREEMENTID bigint 
    )
 As afp
 CROSS APPLY 
    OPENJSON(Year_Json, '$.AFP') WITH
    (
        Product varchar(10) '$.LoanAccounts.Product', 
        bucket int '$.LoanAccounts.BUCKET'
    )
 As LoanAccounts

edited May 1, 2018 at 17:41

answered May 1, 2018 at 17:32

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

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

3 Comments

priyanka.sarkar Over a year ago

But i am getting the below error Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'WITH'. Msg 319, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.tracking context clause, the previous statement must be terminated with a semicolon.

Aaron Bertrand Over a year ago

@priyanka.sarkar Are you sure you are using SQL Server 2016 and that the compatibility level is 130?

Zohar Peled Over a year ago

Sorry, I've had a small mistake since I've written the code directly here. I've now edited it and tested on my SQL Server and it seems to be producing the correct result.

Rm558 · Accepted Answer · 2018-05-02 00:24:14Z

0

In case the array in JSON has a fixed number of element, use

$.P1[x]

If AFP has only 1 element,

SELECT t.AGREEMENTID, 
JSON_Value(Year_Json, '$.AFP[0].LoanAccounts.Product') Product,
JSON_Value(Year_Json, '$.AFP[0].LoanAccounts.BUCKET') Bucket, 
JSON_Value(Year_Json, '$.AFP[0].PaymentInfo.PaymentStatus') PaymentStatus
FROM tblHistoricalDataDemo t

Run it in SQLFiddle, thx Jacob H.

answered May 2, 2018 at 0:24

Rm558

5,0604 gold badges42 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to parse JSON into relational format in SQL Server 2016?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related