Oracle 21c

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