如何在SQL Server 2016中将JSON解析为关系格式? [英] How to parse JSON into relational format in SQL Server 2016?

问题描述

我在(部分)下在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"
      }
    }
  ]
}

表架构如下

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

我想将JSON中的记录从

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

AgreementID产品存储区.... 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

这篇关于如何在SQL Server 2016中将JSON解析为关系格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！