Чтение значений JSON.. в SQL 2012

У меня есть таблица 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. Вы потерпите неудачу. Объекты с экранированными символами сломают ваш код, или если ключ поиска появится в значении в другом месте объекта. Обновите сервер sql до версии 2016+ (чтобы получить встроенные функции json) или проанализируйте его перед вставкой в ​​базу данных с помощью стандартной библиотеки по вашему выбору. (Или, возможно, напишите свою собственную функцию с помощью CLR, чтобы вы могли использовать библиотеки json в .Net)

MatBailie 06.12.2022 12:20

Это отвечает на ваш вопрос Как парсить данные json в SQL Server 2012?

Dordi 06.12.2022 13:55

Учитывая, что SQL Server 2012 полностью «не поддерживается», вы должны быть близки к реализации или, по крайней мере, к завершению пути обновления. Возможно, необходимость анализа данных JSON поможет ускорить эту реализацию.

Larnu 06.12.2022 15:01
Как сделать HTTP-запрос в Javascript?
Как сделать HTTP-запрос в Javascript?
В JavaScript вы можете сделать HTTP-запрос, используя объект XMLHttpRequest или более новый API fetch. Вот пример для обоих методов:
0
3
59
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

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

Предполагая, что обновление до последней версии 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 со значениями в кавычках.
  • Нет необходимости указывать длину идентификатора денежного перевода. Это будет соответственно проанализирован.

ОБНОВИТЬ

Теперь, когда вы знаете, что можете использовать нативную функцию JSON в SQL, самый простой способ извлечь одно значение из JSON:

SELECT JSON_VALUE ( @json, '$.remittanceAdviceId' ) AS remittanceAdviceId;

ВОЗВРАТ

+--------------------+
| remittanceAdviceId |
+--------------------+
|              77592 |
+--------------------+

Большое спасибо ! Очень признателен ! .. Я узнал, что все наши клиенты сейчас используют SQL Server 2017 или более поздней версии .. но я узнал об этом только сегодня. Рассмотрю возможность использования этого... или использования новых функций JSON, таких как OPENJSON..

Allan F 07.12.2022 02:07

Эквивалент OpenJson, по-видимому, является выбранным значением как remittanceAdviceId из OpenJson(@json), где [key] = 'remittanceAdviceId'

Allan F 07.12.2022 02:18

Используйте JSON_VALUE для извлечения значения отдельного поля.

critical_error 07.12.2022 17:31

@AllanF, я добавил небольшое обновление, чтобы показать использование JSON_VALUE.

critical_error 07.12.2022 18:12

@AllanF, даже SQL Server 2017 сейчас не поддерживается. Ведь 5 лет. Тем не менее, он все еще находится в расширенной поддержке, и блокировки задержали все планы обновления.

Panagiotis Kanavos 07.12.2022 18:13

Кроме того, просто пытаясь прочитать числовое значение из json, стоит также сделать что-то подобное (чтобы удалить пробелы, вкладки, LF, CR): RequestJSON = REPLACE(REPLACE(REPLACE(REPLACE(E.RequestJSON, CHAR(32), ''), СИМВОЛ(9),''), СИМВОЛ(10),''), СИМВОЛ(13),'')

Allan F 13.12.2022 05:51

СЛУЧАЙ, КОГДА 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

Allan F 13.12.2022 05:52

Другие вопросы по теме