Блог

Главная - Вопросы по программированию - Выберите вложенный JSON в Oracle, сохраненный как строка

Выберите вложенный JSON в Oracle, сохраненный как строка

#sql #json #oracle

#sql #json #Oracle

Вопрос:

Как извлечь код и причину из приведенного ниже JSON с помощью sql-запроса oracle. Validation_Errors — это строка.

 {
    "validation_errors": "[{"Code": "505", "Reason": "Applicant Potentially Deceased"}, {"Code": "507", "Reason": "Applicant Tax ID Discrepancy"}, {"Code": "508", "Reason": "Applicant Name Discrepancy"}]",
}

Комментарии:

1. Я попробовал вложенный путь, но запрос не возвращает никаких результатов

2. Вы сами сказали , что validation_errors (все в нижнем регистре, а не так, как вы написали) — это строка . Это действительно так — оно заключено в одинарные кавычки. На этом этапе тот факт, что сама строка выглядит как массив JSON, не имеет значения. В вашем JSON нет «вложенного пути». Похоже, вы не хотите, чтобы значение «validation_errors» было строкой; вы хотите, чтобы это был массив JSON (который НЕ должен быть заключен в двойные кавычки!) Итак, пожалуйста, уточните.

3. И, на самом деле, это еще хуже. Значение validation_errors представляет собой строку, заключенную в двойные кавычки. Сама строка также содержит двойные кавычки, которые не экранируются . Итак, ваша входная строка является недопустимым JSON (а не просто допустимым JSON со структурой, отличной от той, о которой вы думали). У вас также есть ошибочная запятая в конце, прямо перед закрытием } — я считаю, что это также фатальная ошибка.

4. Внутренние кавычки экранированы; неформатированный код просто не показал этого. Однако это небольшая часть проблемы… Было бы полезно, если бы вы отредактировали вопрос, чтобы показать попытку запроса, ожидаемый результат и контекст — является ли это значение значением в столбце или, например, введенным пользователем, и типом данных. Также было бы полезно узнать вашу версию Oracle, поскольку поддержка JSON появилась относительно недавно.

Ответ №1:

Вот что вы могли бы сделать (протестировано на Oracle 12.2.0.1). Обратите внимание, что сначала я извлекаю VALIDATION_ERRORS строку с помощью одного приложения JSON_VALUE ; затем я обрабатываю полученную строку как JSON сам по себе (это устраняет те оскорбительные двойные кавычки), чтобы извлечь то, что вам нужно.

Обратите внимание, что JSON чувствителен к регистру; вы должны обратить на это внимание. С другой стороны, оказывается, что дополнительная запятая после вашего «массива, представленного в виде одной строки», молча игнорируется — это не приводит к сбою запроса.

 with
  inputs (json_str) as (
    select
'{
    "validation_errors": "[{"Code": "505", "Reason": "Applicant Potentially Deceased"}, {"Code": "507", "Reason": "Applicant Tax ID Discrepancy"}, {"Code": "508", "Reason": "Applicant Name Discrepancy"}]",
}'
    from dual
  )
, prep  (error_codes) as (
    select json_value(to_clob(json_str), '$.validation_errors')
    from   inputs
  )
select code, reason
from   prep, json_table(error_codes, '$[*]'
                              columns code   number        path '$.Code',
                                      reason varchar2(100) path '$.Reason')
;

CODE REASON                        
---- ------------------------------
 505 Applicant Potentially Deceased
 507 Applicant Tax ID Discrepancy  
 508 Applicant Name Discrepancy

Комментарий на стороне редактирования о необходимости to_clob в prep подзапросе в моем решении.

Я использовал подзапрос в with предложении для имитации входной строки. Oracle рассматривает этот строковый литерал как CHAR тип данных (в частности, нет VARCHAR2 !) С другой стороны, JSON_VALUE ожидается текстовый литерал или столбец (как в нашем случае) типа VARCHAR2 CLOB или BLOB . Вместо того, чтобы создавать сохраненную таблицу со столбцом типа VARCHAR2 or CLOB , я воспользовался коротким путем использования TO_CLOB в своем решении. В реальной жизни это, вероятно, излишне.