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 *
FROM   (
  SELECT t.id,
         jt.idx,
         k.COLUMN_VALUE AS Key,
         get_value( jt.json_obj, k.COLUMN_VALUE ) AS value,
         ROW_NUMBER() OVER (PARTITION BY t.id, jt.idx ORDER BY k.COLUMN_VALUE)
           AS key_index
  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
)
PIVOT (
  MAX(key) AS key,
  MAX(value) AS value
  FOR key_index IN (1, 2, 3)
)


ID
IDX
1_KEY
1_VALUE
2_KEY
2_VALUE
3_KEY
3_VALUE


1
1
column1
value1
column2
value2
column3
value3

1
2
column1
value4
column2
value5
column3
value6

1
3
column1
value7
column2
value8
column3
value9

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