Oracle 21c

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

create table sample as
  select 1 as id, to_clob('{
    "name" : "Rahul",
    "address" : [ {"street" : "100"}, {"street" : "200"}, {"street": "200"}]
    }') as val from dual
  union all
  select 2 as id, to_clob('{
    "name" : "Rahul",
     "address" : [ {"street" : "100"}, {"street" : "300"}]
  }') as val from dual
2 rows affected

select
  sample.*
from sample
where json_exists(val, '$.address[*]?(@.street == "200")')


ID
VAL


1
{
    "name" : "Rahul",
    "address" : [ {"street" : "100"}, {"street" : "200"}, {"street": "200"}]
    }


select
  sample.*
from sample,
    json_table(
      val
      , '$.address[*]'
      columns (street PATH '$.street')
    ) flt
where flt.street = '200'


ID
VAL


1
{
    "name" : "Rahul",
    "address" : [ {"street" : "100"}, {"street" : "200"}, {"street": "200"}]
    }

1
{
    "name" : "Rahul",
    "address" : [ {"street" : "100"}, {"street" : "200"}, {"street": "200"}]
    }


select
  sample.*
from sample
where exists (
    select null
    from json_table(
      val
      , '$.address[*]'
      columns (street PATH '$.street')
    )
    where street = '200'
  )


ID
VAL


1
{
    "name" : "Rahul",
    "address" : [ {"street" : "100"}, {"street" : "200"}, {"street": "200"}]
    }


select
  sample.*
from sample
  cross join lateral (
    select null
    from json_table(
      val
      , '$.address[*]'
      columns (street PATH '$.street')
    )
    where street = '200'
      and rownum = 1
  )


ID
VAL


1
{
    "name" : "Rahul",
    "address" : [ {"street" : "100"}, {"street" : "200"}, {"street": "200"}]
    }

ID	VAL
1	{ "name" : "Rahul", "address" : [ {"street" : "100"}, {"street" : "200"}, {"street": "200"}] }
1	{ "name" : "Rahul", "address" : [ {"street" : "100"}, {"street" : "200"}, {"street": "200"}] }