Oracle 18c

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;
/
SELECT t.id,
       jt.idx,
       k.COLUMN_VALUE AS Key,
       get_value( jt.json_obj, k.COLUMN_VALUE ) AS value
FROM   TABLE_NAME t
       CROSS APPLY JSON_TABLE(
         t.json_value,
         '$[*]'
         COLUMNS (
           idx FOR ORDINALITY,
           json_obj VARCHAR2(4000) FORMAT JSON PATH '$'
         )
       ) jt
       CROSS APPLY get_keys( jt.json_obj ) k


ID
IDX
KEY
VALUE


1
1
column1
value1

1
1
column2
value2

1
1
column3
value3

1
2
column1
value4

1
2
column2
value5

1
2
column3
value6

1
3
column3
value9

1
3
column1
value7

1
3
column2
value8

ID	IDX	KEY	VALUE
1	1	column1	value1
1	1	column2	value2
1	1	column3	value3
1	2	column1	value4
1	2	column2	value5
1	2	column3	value6
1	3	column3	value9
1	3	column1	value7
1	3	column2	value8