Java解析SQL记录为JSON,如何在SQL Server 2016中将JSON解析为关系格式？

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

解决方案

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