Search code examples
sqljsonoraclejson-table

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?


Solution

  • 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:

    CAR_NAME EVENT_STATE EVENT_TIMESTAMP
    CAR-1 waiting 2024-07-08 22:00:00.000000 UTC
    CAR-13 delivery 2024-07-08 22:00:00.000000 UTC
    CAR-15 pickup 2024-07-08 22:00:00.000000 UTC
    CAR-16 waiting 2024-07-08 22:00:00.000000 UTC
    CAR-19 waiting 2024-07-08 22:00:00.000000 UTC
    CAR-2 waiting 2024-07-08 22:00:00.000000 UTC
    CAR-2 delivery 2024-07-13 19:46:40.000000 UTC

    fiddle