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:
KEY | VALUE |
---|---|
2024-05-31 00:00:00 | 115.09 |
2024-06-15 00:00:00 | 14.1 |
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:
DATE1 | VALUE1 | DATE2 | VALUE2 |
---|---|---|---|
2024-05-31 00:00:00 | 115.09 | 2024-06-15 00:00:00 | 14.1 |