Как эффективно анализировать json в Oracle 18c?

Я пытаюсь разобрать большой json, используя API JSON_OBJECT_T, JSON_ARRAY_T, и он работает нормально, но мне нужны советы экспертов, эффективен он или нет?

Я добавляю свой файл json и код синтаксического анализа, как показано ниже.

SampleJson

Код

SET SERVEROUTPUT ON;
DECLARE
   l_clob clob;
   l_time timestamp;

   l_json json_object_t;
   l_stops_array json_array_t;
   l_stops_arr json_array_t;

   routeInfoObj json_object_t;
   routeStopArr json_array_t;
BEGIN
   SELECT LOG_CLOB INTO l_clob FROM ITV_DEV_LOGS WHERE LOG_ID = 1435334;
   l_time := systimestamp;

   l_json := json_object_t.parse( l_clob );

   dbms_output.put_line( 'Parsing Time: ' || extract(second from( systimestamp - l_time ) ) );
   l_stops_array := l_json.get_array('data');
   DBMS_OUTPUT.PUT_LINE('Data array: '||l_stops_array.get_size);
   FOR i in 0..l_stops_array.get_size-1 loop
      l_stops_arr := TREAT(l_stops_array.get(i) AS JSON_OBJECT_T).get_array('routedStops');
      DBMS_OUTPUT.PUT_LINE('stops array: '||l_stops_arr.get_size);
      FOR j in 0..l_stops_arr.get_size - 1 loop
         routeInfoObj := TREAT(l_stops_arr.get(j) AS JSON_OBJECT_T).get_object('routingInfo');
         DBMS_OUTPUT.PUT_LINE('Stop : ' || routeInfoObj.get_number('stop'));
         routeStopArr := TREAT(l_stops_arr.get(j) AS JSON_OBJECT_T).get_array('routedJobs');
         FOR k in 0..routeStopArr.get_size - 1 loop
            DBMS_OUTPUT.PUT_LINE('JobRef : ' || TREAT(routeStopArr.get(k) AS JSON_OBJECT_T).get_string('jobRef'));
         // update query to update stop value to respective jobRef
        end loop;
      end loop;
  end loop;
END;

Он работает нормально, но есть ли способ улучшить эту реализацию, поскольку это всего лишь образец json, и количество объектов внутри может возрасти до 2000, и вместо обновления записей одну за другой, есть ли способ обновить все записи в одном выражении?


json oracle plsql oracle12c oracle18c
person Kinjan Bhavsar    schedule 05.04.2019    source источник
comment
Что именно вы пытаетесь сделать? Просто распечатать JSON? Или вы пытаетесь вставить данные в нормализованную модель данных? Если вы пытаетесь обновить таблицу, как выглядит эта таблица? И какая часть JSON должна войти в эту таблицу?   -  person a_horse_with_no_name    schedule 05.04.2019
comment
Пожалуйста, предоставьте простой json, который мы можем использовать, чтобы упростить эту проблему. Кроме того, сообщите нам, что вы хотите видеть в качестве вывода, а не мы пытаемся реконструировать его из вашего кода.   -  person Kaushik Nayak    schedule 05.04.2019
comment
@a_horse_with_no_name Я хочу обновить значение «stop» в столбце последовательности, а jobRef в json — это мой первичный ключ, который поможет мне найти запись.   -  person Kinjan Bhavsar    schedule 05.04.2019
comment
@KaushikNayak прикрепленный json - это именно тот, который я получу, и если я добавлю простой json, то я дам вам неверную информацию, и я хочу обновить столбец последовательности со значением «стоп» из json, используя значение jobRef, которое является первичным ключом в мой стол.   -  person Kinjan Bhavsar    schedule 05.04.2019
comment
Итак, вы хотите изменить фактический JSON, а не какую-то таблицу?   -  person a_horse_with_no_name    schedule 05.04.2019
comment
@a_horse_with_no_name Нет, я хочу обновить столбец в моей таблице с помощью стоп-значения из json, и для этого поможет jobRef   -  person Kinjan Bhavsar    schedule 05.04.2019

Ответы (1)


arrow_upward
3
arrow_downward

Вы можете использовать json_table(), чтобы преобразовать значение JSON в реляционное представление. Это, в свою очередь, можно использовать в операторе MERGE.

Например. следующий запрос:

select j.*
from itv_dev_logs
  cross join json_table(log_clob, '$.data.routedStops[*]'
                        columns stop_id integer path '$.stopId',
                                zone_ltf integer path '$.zoneLTF', 
                                info_stop_nr integer path '$.routingInfo.stop',
                                info_route_ref varchar(20) path '$.routingInfo.routeRef',
                                info_eta varchar(20) path '$.routingInfo.eta',
                                info_eta_dt timestamp path '$.routingInfo.etaDateTime',
                                info_stop_time number path '$.routingInfo.stopTime'
                        ) j
where log_id = 1435334;

Возвращает что-то вроде этого:

STOP_ID       | ZONE_LTF | INFO_STOP_NR | INFO_ROUTE_REF | INFO_ETA | INFO_ETA_DT             | INFO_STOP_TIME | INFO_DIST_PREV_STOP | INFO_BREAK_TIME | INFO_BREAK_DURATION
--------------+----------+--------------+----------------+----------+-------------------------+----------------+---------------------+-----------------+--------------------
1554383571432 |        1 |            1 | R119           | 11:01    | 2019-04-16 11:01:00.000 |           0.08 |                0.27 | 00:00           | 00:00              
1554383571515 |        1 |            2 | R119           | 11:07    | 2019-04-16 11:07:00.000 |           0.08 |                0.34 | 00:00           | 00:00              
1554383571601 |        1 |            3 | R119           | 11:13    | 2019-04-16 11:13:00.000 |           0.08 |                   0 | 00:00           | 00:00              
1554383571671 |        1 |            4 | R119           | 11:19    | 2019-04-16 11:19:00.000 |           0.08 |                   0 | 00:00           | 00:00              
1554383571739 |        1 |            5 | R119           | 11:25    | 2019-04-16 11:25:00.000 |           0.08 |                   0 | 00:00           | 00:00

Это можно использовать в качестве источника оператора MERGE для обновления вашей целевой таблицы:

merge into your_target_table tg
using (
    select j.*
    from itv_dev_logs
      cross join json_table(log_clob, '$.data.routedStops[*]'
                            columns stop_id integer path '$.stopId',
                                    zone_ltf integer path '$.zoneLTF', 
                                    info_stop_nr integer path '$.routingInfo.stop',
                                    info_route_ref varchar(20) path '$.routingInfo.routeRef',
                                    info_eta varchar(20) path '$.routingInfo.eta',
                                    info_eta_dt timestamp path '$.routingInfo.etaDateTime',
                                    info_stop_time number path '$.routingInfo.stopTime'
                            ) j
    where log_id = 1435334
) t on (t.stop_id = tg.stop_id and ... more join conditions ...)
when matched then update
    set stop_nr = t.info_stop_nr, 
        eta_timestamp = t.eta_dt,

Поскольку вы не предоставили ни структуру цели, ни информацию о том, какие ключи JSON должны сопоставляться с какими столбцами таблицы, все имена столбцов являются просто предположениями, и вам необходимо заменить их правильными именами.

person a_horse_with_no_name    schedule 05.04.2019
comment
в таблице есть 2 столбца, sequence и job_id. Последовательность должна сопоставляться с routingInfo.stop, а job_id является первичным ключом моей таблицы и должен сопоставляться с routedJobs.jobRef. Также улучшит ли JSON_TABLE производительность? - person Kinjan Bhavsar; 05.04.2019
comment
Можете ли вы предположить, улучшит ли JSON_TABLE() производительность, поскольку в некоторых случаях мне может потребоваться обновить 2000 записей? - person Kinjan Bhavsar; 06.04.2019
comment
@KinjanBhavsar: один оператор MERGE, скорее всего, будет быстрее, чем 2000 операторов обновления. Но нужно проверить на себе - person a_horse_with_no_name; 06.04.2019