У меня есть таблица SQL Server 2012 со столбцом в соответствии с:
Ответ JSON varchar (макс.)
Он содержит текстовые значения, такие как
{
"partNumber": 1,
"partTotal": 1,
"payeeLocationId": "ABC00011",
"remittanceAdviceId": "77592",
"paymentInfo": {
"accountInfo": {
"accountName": "ABC Hospital",
"xyzNumber": "",
"xyzCode": ""
},
"depositAmount": "1234",
"paymentReference": "ERA 1234"
},
"paymentRun": {
"payerName": "ABC",
"runDate": "2022-12-05"
},
"claimSummary": [
{
"benefit": "5555",
"channelCode": "ABC",
"claimId": "1234",
"lodgementDate": "2022-02-14",
"transactionId": "xpxpxpxpxxp",
"accountReferenceId": "12345678"
}
]
}
Мне было интересно, как прочитать значение remittanceAdviceId 77592 (в данном случае) из данных этого столбца JSON. remittanceAdviceId может быть разного размера по длине.. например. 1,2,3,4,5,6,7 и т. д. цифры
Я рассматривал что-то вроде:
SELECT remittanceAdviceId = CASE
WHEN E.RequestJSON IS NOT NULL AND
CHARINDEX('"remittanceAdviceId"', E.RequestJSON, 0) > 0 THEN
SUBSTRING(E.RequestJSON,
CHARINDEX('"remittanceAdviceId"', E.RequestJSON, 0) + 22,
5)
ELSE
NULL
END
но это не совсем правильно, так как значение может отличаться от 5 цифр..
Это отвечает на ваш вопрос Как парсить данные json в SQL Server 2012?
Учитывая, что SQL Server 2012 полностью «не поддерживается», вы должны быть близки к реализации или, по крайней мере, к завершению пути обновления. Возможно, необходимость анализа данных JSON поможет ускорить эту реализацию.
Предполагая, что обновление до последней версии SQL сейчас не планируется, вот простой подход с использованием SUBSTRING
и CHARINDEX
:
DECLARE @json varchar(2000) = '{
"partNumber": 1,
"partTotal": 1,
"payeeLocationId": "ABC00011",
"remittanceAdviceId": "77592",
"paymentInfo": {
"accountInfo": {
"accountName": "ABC Hospital",
"xyzNumber": "",
"xyzCode": ""
},
"depositAmount": "1234",
"paymentReference": "ERA 1234"
},
"paymentRun": {
"payerName": "ABC",
"runDate": "2022-12-05"
},
"claimSummary": [
{
"benefit": "5555",
"channelCode": "ABC",
"claimId": "1234",
"lodgementDate": "2022-02-14",
"transactionId": "xpxpxpxpxxp",
"accountReferenceId": "12345678"
}
]
}';
SELECT SUBSTRING (
@json
, CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) + 23
, CHARINDEX ( '",', @json, CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) ) - CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) - 23
) AS remittanceAdviceId;
ВОЗВРАТ
+--------------------+
| remittanceAdviceId |
+--------------------+
| 77592 |
+--------------------+
НОТЫ
ОБНОВИТЬ
Теперь, когда вы знаете, что можете использовать нативную функцию JSON в SQL, самый простой способ извлечь одно значение из JSON:
SELECT JSON_VALUE ( @json, '$.remittanceAdviceId' ) AS remittanceAdviceId;
ВОЗВРАТ
+--------------------+
| remittanceAdviceId |
+--------------------+
| 77592 |
+--------------------+
Большое спасибо ! Очень признателен ! .. Я узнал, что все наши клиенты сейчас используют SQL Server 2017 или более поздней версии .. но я узнал об этом только сегодня. Рассмотрю возможность использования этого... или использования новых функций JSON, таких как OPENJSON..
Эквивалент OpenJson, по-видимому, является выбранным значением как remittanceAdviceId из OpenJson(@json), где [key] = 'remittanceAdviceId'
Используйте JSON_VALUE
для извлечения значения отдельного поля.
@AllanF, я добавил небольшое обновление, чтобы показать использование JSON_VALUE
.
@AllanF, даже SQL Server 2017 сейчас не поддерживается. Ведь 5 лет. Тем не менее, он все еще находится в расширенной поддержке, и блокировки задержали все планы обновления.
Кроме того, просто пытаясь прочитать числовое значение из json, стоит также сделать что-то подобное (чтобы удалить пробелы, вкладки, LF, CR): RequestJSON = REPLACE(REPLACE(REPLACE(REPLACE(E.RequestJSON, CHAR(32), ''), СИМВОЛ(9),''), СИМВОЛ(10),''), СИМВОЛ(13),'')
СЛУЧАЙ, КОГДА T.RequestJSON НЕ NULL И CHARINDEX('"paymentReference":"', T.RequestJSON, 0) > 0 THEN SUBSTRING ( T.RequestJSON , CHARINDEX ( '"paymentReference":"', T.RequestJSON, 0 ) + 20 , (CHARINDEX ( '"', T.RequestJSON, CHARINDEX ( '"paymentReference":"', T.RequestJSON, 0 ) + 20 )) - (CHARINDEX ( '"paymentReference":"', T.RequestJSON, 0 ) + 20 )) ELSE NULL END
Не пишите парсер json в SQL. Вы потерпите неудачу. Объекты с экранированными символами сломают ваш код, или если ключ поиска появится в значении в другом месте объекта. Обновите сервер sql до версии 2016+ (чтобы получить встроенные функции json) или проанализируйте его перед вставкой в базу данных с помощью стандартной библиотеки по вашему выбору. (Или, возможно, напишите свою собственную функцию с помощью CLR, чтобы вы могли использовать библиотеки json в .Net)