JSON_TABLE()-Function in Oracle's SQL

I'm currently struggling to import my API-Response to my Oracle table. I'm using the JSON_TABLE() but this function is not working as expected.

I have a JSON value like this:

{
  "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
    }
  ]
}...

hint: there can be several entries per car if I increase the time range.

Currently I use the following plsql to extract the data into my db table:

INSERT INTO car_event_table (car_name, event_state, event_timestamp)
SELECT jt.car_name, jt.event_state, jt.event_timestamp
  FROM JSON_TABLE(l_response,
                  '$.*[*]'
                  COLUMNS(car_name        VARCHAR2 PATH '$',
                          event_state     VARCHAR2 PATH '$.state',
                          event_timestamp NUMBER   PATH '$.timestamp')) jt;

Unfortunately I cannot extract the car_name from the json object. Can someone help me?

Oracle does not have a built-in way to dynamically get keys from an object. However, you can create a function that can retrieve them for you if you know their position within the JSON document:

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;
/

Then you can use the query:

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;

Which, if you have the table:

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

and l_response is:

{
  "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}]
}

Then, after the INSERT, the table contains:

fiddle