By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
id int,
myclob_column clob,
column3 int
);
insert into mytable
select 1, '{
"name" : "Rahul",
"address" : [ {"street" : "100"}, {"street" : "200"}]
}', 1 from dual;
1 rows affected
insert into mytable
select 2, '{
"name" : "Rahul",
"address" : [ {"street" : "100"}, {"street" : "300"}]
}', 2 from dual;
1 rows affected
select t.*
from mytable t, json_table( myclob_column , '$.address[*]'
COLUMNS (street PATH '$.street')
)
where STREET = 200
| ID | MYCLOB_COLUMN | COLUMN3 |
|---|---|---|
| 1 | { "name" : "Rahul", "address" : [ {"street" : "100"}, {"street" : "200"}] } |
1 |