JSON_TABLE() — функция в Oracle SQL

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

CREATE FUNCTION get_key(
  pos  IN PLS_INTEGER,
  json IN CLOB
) RETURN VARCHAR2 
AS
  doc_keys JSON_KEY_LIST;
BEGIN
  doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
  RETURN doc_keys( pos );
END get_key;
/

Затем вы можете использовать запрос:

INSERT INTO car_event_table (car_name, event_state, event_timestamp)
    SELECT get_key(jt.idx, l_response),
           jt.event_state,
           TIMESTAMP '1970-01-01 00:00:00 UTC'
           + jt.event_timestamp * INTERVAL '1' SECOND
    FROM JSON_TABLE(
        l_response,
        '$.*'
        COLUMNS (
          idx FOR ORDINALITY,
          NESTED PATH '$[*]' COLUMNS (
            event_state VARCHAR2 PATH '$.state',
            event_timestamp NUMBER PATH '$.timestamp'
          )
        )
    ) jt;

Что, если у вас есть таблица:

CREATE TABLE car_event_table (
  car_name        VARCHAR2(50),
  event_state     VARCHAR2(10),
  event_timestamp TIMESTAMP WITH TIME ZONE
)

и l_response это:

{
  "CAR-1":[{"state":"waiting","timestamp":1720476000}],
  "CAR-13":[{"state":"delivery","timestamp":1720476000}],
  "CAR-15":[{"state":"pickup","timestamp":1720476000}],
  "CAR-16":[{"state":"waiting","timestamp":1720476000}],
  "CAR-19":[{"state":"waiting","timestamp":1720476000}],
  "CAR-2":[{"state":"waiting","timestamp":1720476000},{"state":"delivery","timestamp":1720900000}]
}

Затем после INSERT таблица содержит:

рабочий пример

На самом деле вы можете сделать это без создания функции, но только с версией >= 21c:

with data(js) as (
    select json(q'~{
      "CAR-1": [
        {
          "state": "waiting",
          "timestamp": 1720476000
        }
      ],
      "CAR-13": [
        {
          "state": "delivery",
          "timestamp": 1720476000
        }
      ],
      "CAR-15": [
        {
          "state": "pickup",
          "timestamp": 1720476000
        }
      ],
      "CAR-16": [
        {
          "state": "waiting",
          "timestamp": 1720476000
        }
      ],
      "CAR-19": [
        {
          "state": "waiting",
          "timestamp": 1720476000
        }
      ],
      "CAR-2": [
        {
          "state": "waiting",
          "timestamp": 1720476000
        }
      ]
    }~')
)
select 
    substr(dg.path,4,length(dg.path)-4) as car_name, event_state, event_timestamp 
from 
data d,
json_table(js,
    '$.*'
    COLUMNS (
      idx FOR ORDINALITY,
      NESTED PATH '$[*]' COLUMNS (
        event_state VARCHAR2 PATH '$.state',
        event_timestamp NUMBER PATH '$.timestamp'
      )
    )
) t,
json_table(
  (select json_dataguide(js) from data)
  , '$[*]?(@."o:path" like "$.\"CAR-%\"")'
    columns(
        idx for ORDINALITY,
        path varchar2(128) path '$."o:path"'
    )
) dg
where t.idx = dg.idx
;