JSON_TABLE()-Function in Oracle's SQL

Timo
July 15, 2024
142 views
0 votes
2 Answers

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?

Answers

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

In fact you can do it without creating a function, but with version >= 21c only:

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
;

Please signup or login to give your own answer.

Click here to cancel reply.