Как преобразовать JSON в реляционный формат в SQL Server 2016?

У меня есть Json, хранящийся в таблице SQL Server 2016, как в разделе (частично)

{
  "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 в реляционный формат как

Сегмент продукта AgreementID .... PaymentStatus

Я пробовал с помощью ниже, но я делаю что-то не так, для чего не могу получить результат

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

Думаю, вам нужен OPENJSON(), а не JSON_VALUE(). См. Документацию.

— 01.05.2018 19:25

json sql-server tsql

01.05.2018 19:19

Как сделать HTTP-запрос в Javascript?

В JavaScript вы можете сделать HTTP-запрос, используя объект XMLHttpRequest или более новый API fetch. Вот пример для обоих методов:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Используйте встроенную функцию табличного значения OPENJSON:

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

Но я получаю сообщение об ошибке ниже Msg 156, Level 15, State 1, Line 7 Неправильный синтаксис рядом с ключевым словом «WITH». Сообщение 319, уровень 15, состояние 1, строка 7 Неправильный синтаксис рядом с ключевым словом with. Если этот оператор является общим табличным выражением, предложением xmlnamespaces или предложением контекста отслеживания изменений, предыдущий оператор должен заканчиваться точкой с запятой. Tracking context clause, предыдущий оператор должен заканчиваться точкой с запятой.

— 01.05.2018 19:34

@ priyanka.sarkar Вы уверены, что используете SQL Server 2016 и что уровень совместимости равен 130?

— 01.05.2018 19:40

Извините, у меня была небольшая ошибка, так как я написал код прямо здесь. Теперь я отредактировал его и протестировал на своем SQL Server, и, похоже, он дает правильный результат.

— 01.05.2018 19:41

01.05.2018 19:32

Если массив в JSON имеет фиксированное количество элементов, используйте

$.P1[x]

Если AFP имеет только 1 элемент,

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

Запустите его в SQLFiddle, спасибо Джейкобу Х.

02.05.2018 02:24