By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (
id NUMBER
GENERATED ALWAYS AS IDENTITY
PRIMARY KEY,
json_value CLOB
CHECK( json_value IS JSON )
);
INSERT INTO table_name ( json_value ) VALUES (
'[{"column1":"value1","column2":"value2","column3":"value3"},
{"column1":"value4","column2":"value5","column3":"value6"},
{"column3":"value9","column1":"value7","column2":"value8"}]'
)
1 rows affected
CREATE FUNCTION get_keys(
value IN CLOB
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
js JSON_OBJECT_T := JSON_OBJECT_T( value );
keys JSON_KEY_LIST;
BEGIN
keys := js.get_keys();
FOR i in 1 .. keys.COUNT LOOP
PIPE ROW ( keys(i) );
END LOOP;
END;
/
CREATE FUNCTION get_value(
value IN CLOB,
path IN VARCHAR2
) RETURN VARCHAR2
IS
js JSON_OBJECT_T := JSON_OBJECT_T( value );
BEGIN
RETURN js.get_string( path );
END;
/
DECLARE
rc sys_refcursor;
j_keys varchar2(2000);
query_s varchar2(20000);
BEGIN
SELECT listagg(
k.COLUMN_VALUE || ' varchar(256) PATH ''$.' || k.COLUMN_VALUE || '''',
','
)
INTO j_keys
FROM ( SELECT JSON_QUERY( json_value, '$[1]' RETURNING CLOB) AS json_obj
FROM table_name
)t
CROSS APPLY get_keys( t.json_obj ) k;
query_s := 'SELECT jt.*
FROM table_name t
CROSS APPLY JSON_TABLE(
t.json_value,
''$[*]''
COLUMNS
' || j_keys || ') jt';
open rc for query_s;
DECLARE
col1 VARCHAR2(50);
col2 VARCHAR2(50);
col3 VARCHAR2(50);
BEGIN
LOOP
FETCH rc INTO col1, col2, col3;
EXIT WHEN rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( col1 || ', ' || col2 || ', ' || col3 );
END LOOP;
END;
1 rows affected
dbms_output:
value1, value2, value3
value4, value5, value6
value7, value8, value9