By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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}
}')
1 rows affected
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;
/
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 '$'
)
)
| KEY | VALUE |
|---|---|
| 2024-05-31 00:00:00 | 115.09 |
| 2024-06-15 00:00:00 | 14.1 |
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)
)
| DATE1 | VALUE1 | DATE2 | VALUE2 |
|---|---|---|---|
| 2024-05-31 00:00:00 | 115.09 | 2024-06-15 00:00:00 | 14.1 |