Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search