How to parse JSON into relational format in SQL Se

问题:

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

回答1:

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

回答2:

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.