Retrieve data from Json Column

There is a data structure in a json column I am trying to retrieve a certain result from this column. Тhese dates I will have to apply it to a function each date will be a parameter

The goal is to retrieve PERIOD1 05/31/2024 as date1 , PERIOD1 06/15/2024 as date2

   create table emp_json_tbl (
     emp_json CLOB

  );


   insert into emp_json_tbl values ('{"flows":{"11/21/2023":725.76,"06/14/2024"},"PERIOD1":                   "05/31/2024":115.09,"06/15/2024":14.10},"PERIOD3":{"11/21/2023":0,"06/15/2024":18.72},"PERIOD2":   {"03/31/2024":140,"06/15/2024":13}}') 


 select * from emp_json_tbl

`

Expected result; 05/31/2024 as date1 06/15/2024 as date2

Adapting this answer, Oracle does not have a built-in way to dynamically get key-value pairs so you can create a get_key function:

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, if you have valid JSON:

create table emp_json_tbl (
  emp_json CLOB CHECK (emp_json IS JSON)
);

insert into emp_json_tbl
values ('{
  "flows":{"11/21/2023":725.76,"06/14/2024":0},
  "PERIOD1":{"05/31/2024":115.09,"06/15/2024":14.10},
  "PERIOD3":{"11/21/2023":0,"06/15/2024":18.72},
  "PERIOD2":{"03/31/2024":140,"06/15/2024":13}
}')

Then you can use the query:

SELECT TO_DATE(get_key(idx, e.emp_json.PERIOD1), 'MM/DD/YYYY') AS key,
       value
FROM   emp_json_tbl e
       CROSS JOIN JSON_TABLE(
         e.emp_json,
         '$.PERIOD1.*'
         COLUMNS(
           idx   FOR ORDINALITY,
           value NUMBER PATH '$'
         )
       )

Which outputs:

If you want the values as columns then PIVOT:

SELECT "1_DATE" AS date1,
       "1_VALUE" AS value1,
       "2_DATE" AS date2,
       "2_VALUE" AS value2
FROM   (
  SELECT idx,
         TO_DATE(get_key(idx, e.emp_json.PERIOD1), 'MM/DD/YYYY') AS key,
         value
  FROM   emp_json_tbl e
         CROSS JOIN JSON_TABLE(
           e.emp_json,
           '$.PERIOD1.*'
           COLUMNS(
             idx   FOR ORDINALITY,
             value NUMBER PATH '$'
           )
        )
)
PIVOT (
  MAX(key) AS "DATE",
  MAX(value) AS value
  FOR idx IN (1, 2)
)

Which outputs:

fiddle