JSON functions are defined for Database Oracle12c+ version. APEX_JSON package with release 5.0+ should be installed for the previous releases. Whenever installation complete, then the following code might be used as a workaround in order to extract the desired values :
DECLARE
v_json VARCHAR2(32767);
v_mon1 OWA.VC_ARR;
v_mon2 OWA.VC_ARR;
BEGIN
SELECT *
INTO v_json
FROM j; -- your JSON value is inserted into this table
-- there's no WHERE clause assuming only one row is to be inserted
APEX_JSON.PARSE(v_json);
FOR i IN 1..2
LOOP
v_mon1(i) := TO_CHAR(TO_DATE(i, 'j'),'jspth');
v_mon2(i) := TO_CHAR(TO_DATE(i, 'mm'),'MON');
DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_VARCHAR2(v_mon1(i)||'.'||v_mon2(i)||'.ID')||' '||
APEX_JSON.GET_VARCHAR2(v_mon1(i)||'.'||v_mon2(i)||'.Name'));
END LOOP;
END;
/
If you need to create a view, then you convert the existing data to XMLTYPE, and then XMLTABLE function the extract the columns as
CREATE OR REPLACE VIEW v_people AS
WITH t AS
(
SELECT APEX_JSON.TO_XMLTYPE(jsdata) AS xml_data
FROM j
), t2 AS
(
SELECT xt1.*, xt2.*
FROM t
CROSS JOIN
XMLTABLE('/json/first'
PASSING xml_data
COLUMNS
ID1 INT PATH 'JAN/ID',
Name1 VARCHAR2(100) PATH 'JAN/Name'
) xt1
CROSS JOIN
XMLTABLE('/json/second'
PASSING xml_data
COLUMNS
ID2 INT PATH 'FEB/ID',
Name2 VARCHAR2(100) PATH 'FEB/Name'
) xt2
)
SELECT ID1 AS ID, Name1 AS Name FROM t2
UNION ALL
SELECT ID2, Name2 FROM t2
