Поддержка JSON появилась в Oracle с версии 12. В этой статье рассматривается пример SQL-запроса для обработки JSON-ответа API, содержащего статусы и ошибки. Запрос позволяет извлечь данные из вложенных структур, сохранить порядок статусов и агрегировать данные вложенных структур.
Это пример обработки реального сообщения от API Транспортной компании, содержащей результат запроса заявки на вызов курьера.
Задача: получить и сохранить актуальный статус обработки заявки и сообщение об ошибке (если она была).

Пример JSON структуры

{
  "entity": {
    "uuid": "7dc5aa3f-e441-4c37-95c0-6927304e42e9",
    "cdek_number": "101954699434",
    "from_location": {
        "code": 15326,
        "city_uuid": "331dd291-d809-4eff-83ff-417ac1e59def",
        "city": "Инской"
    },
    "to_location": {
        "code": 450,
        "city_uuid": "27f58584-4034-4a6c-810b-daa741a2d075",
        "city": "Петрозаводск"
    },
    "need_call": true,
    "statuses": [
        {
            "code": "READY_FOR_APPOINTMENT",
            "name": "Готова к назначению",
            "date_time": "2025-12-05T13:23:15+0000"
        },
        {
            "code": "INVALID",
            "name": "Некорректная заявка",
            "date_time": "2025-12-05T13:23:15+0000"
        },
        {
            "code": "ACCEPTED",
            "name": "Принят",
            "date_time": "2025-12-05T13:23:15+0000"
        }
    ],
    "requests": [
        {
            "type": "CREATE",
            "date_time": "2025-12-05T13:23:15+0000",
            "state": "INVALID",
            "errors": [
                {
                    "code": "v2_internal_error",
                    "message": "Internal Error"
                }
            ]
        }
    ]
  }
}

SQL-запрос для обработки JSON

WITH all_parsed_data AS (
  SELECT 
    jt.*
  FROM JSON_TABLE(
    '{
      "entity": {
        "uuid": "7dc5aa3f-e441-4c37-95c0-6927304e42e9",
        "cdek_number": "101954699434",
        "from_location": {
            "code": 15326,
            "city_uuid": "331dd291-d809-4eff-83ff-417ac1e59def",
            "city": "Инской"
        },
        "to_location": {
            "code": 450,
            "city_uuid": "27f58584-4034-4a6c-810b-daa741a2d075",
            "city": "Петрозаводск"
        },
        "need_call": true,
        "statuses": [
            {
                "code": "READY_FOR_APPOINTMENT",
                "name": "Готова к назначению",
                "date_time": "2025-12-05T13:23:15+0000"
            },
            {
                "code": "INVALID",
                "name": "Некорректная заявка",
                "date_time": "2025-12-05T13:23:15+0000"
            },
            {
                "code": "ACCEPTED",
                "name": "Принят",
                "date_time": "2025-12-05T13:23:15+0000"
            }
        ],
        "requests": [
            {
                "type": "CREATE",
                "date_time": "2025-12-05T13:23:15+0000",
                "state": "INVALID",
                "errors": [
                    {
                        "code": "v2_internal_error",
                        "message": "Internal Error"
                    }
                ]
            }
        ]
      }
    }', '$'
    COLUMNS (
      entity_uuid VARCHAR2(100) PATH '$.entity.uuid',
      NESTED PATH '$.entity.statuses[*]' COLUMNS (
        status_order     FOR ORDINALITY,
        status_code      VARCHAR2(100) PATH '$.code',
        status_name      VARCHAR2(400) PATH '$.name',
        status_date_time VARCHAR2(100) PATH '$.date_time'
      ),
      NESTED PATH '$.entity.requests[*]' COLUMNS (
        request_type VARCHAR2(100) PATH '$.type',
        req_state    VARCHAR2(100) PATH '$.state',
        NESTED PATH '$.errors[*]' COLUMNS (
          err_code VARCHAR2(200) PATH '$.code',
          err_msg  VARCHAR2(4000) PATH '$.message'
        )
      )
    )
  ) jt
)
SELECT 
  s.entity_uuid,
  s.status_code,
  s.status_name,
  TO_TIMESTAMP_TZ(
    REPLACE(REGEXP_REPLACE(s.status_date_time,'([+-]\d{2})(\d{2})$','\1:\2'),'T',' '),
    'YYYY-MM-DD HH24:MI:SS TZH:TZM'
  ) - NUMTODSINTERVAL(s.status_order, 'SECOND') as status_date_time,
  e.errors_text
FROM (
  -- Уникальные статусы
  SELECT DISTINCT 
    entity_uuid,
    status_order,
    status_code,
    status_name,
    status_date_time
  FROM all_parsed_data
  WHERE status_code IS NOT NULL
) s
LEFT JOIN (
  -- Агрегированные ошибки
  SELECT 
    req_state,
    LISTAGG(err_code || ': ' || err_msg, '; ') WITHIN GROUP (ORDER BY err_code) as errors_text
  FROM all_parsed_data
  WHERE err_code IS NOT NULL
  GROUP BY req_state
) e ON s.status_code = e.req_state
ORDER BY s.status_order;

Описание запроса

Этот SQL-запрос выполняет следующие задачи:

  1. Извлечение данных из JSON: Используется функция JSON_TABLE для преобразования вложенной JSON структуры в табличный формат.
  2. Сохранение порядка статусов: Поле status_order сохраняет порядок элементов из массива statuses, так как все date_time одинаковы.
  3. Агрегация ошибок: Ошибки из массива errors объединяются в одну строку с помощью LISTAGG.
  4. Обработка временных меток: Поле status_date_time преобразуется в формат TIMESTAMP WITH TIME ZONE с учетом часового пояса.

Преимущества подхода

  • Гибкость: Позволяет работать с вложенными структурами JSON и извлекать данные на разных уровнях вложенности.
  • Сохранение порядка: Использование FOR ORDINALITY позволяет сохранить порядок элементов в массиве.
  • Агрегация данных: Возможность объединять данные из разных частей JSON (например, статусы и ошибки).
  • Универсальность: Подходит для обработки сложных JSON структур с вложенными массивами и объектами.

Дополнительные замечания

  • Оптимизация: Для больших JSON структур рекомендуется использовать индексы на колонках, участвующих в фильтрации.
  • Обработка ошибок: Убедитесь, что JSON данные корректны, чтобы избежать ошибок при парсинге.