Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_with_clob_column (
  id NUMBER,
  clob_with_json_file CLOB CHECK (clob_with_json_file IS JSON)
);
INSERT INTO table_with_clob_column (id, clob_with_json_file)
VALUES (1, '{ "c1": "c1", "c2": "c2", "c3": { "a": "a" }, "c4": { "c5": { "c6": "c6" } } }');
1 rows affected

select tabj.*
from   table_with_clob_column t
       CROSS APPLY JSON_TABLE(
         t.clob_with_json_file,
         '$'
         columns(
           c1,
           c2,
           a PATH '$.c3.a',
           c6 PATH '$.c4.c5.c6'
        )
      ) tabj
where t.id = 1


C1
C2
A
C6


c1
c2
a
c6