您可以使用json_table将JSON数据转换为表中的行和列：

select  t.*
from mytable t, json_table( myclob_column , '$.address[*]'
                COLUMNS (street PATH '$.street')
               )
where STREET = 200

Demo here

您可以解析JSON值并添加条件，而无需硬编码，如下所示，

select 
  * 
from 
mytable t1, 
JSON_TABLE(t1.clob_column,'$.address[*]' 
           COLUMNS 
           (STREET varchar PATH '$.street')
) t2 
where t2.street = '200';

你可以看到实时的sql here

你可以在数组元素上使用json_exists和 predicate ：

SQL> with sample as (
  2    select 1 as id, to_clob('{
  3      "name" : "Rahul",
  4      "address" : [ {"street" : "100"}, {"street" : "200"}]
  5      }') as val from dual
  6    union all
  7    select 2 as id, to_clob('{
  8      "name" : "Rahul",
  9       "address" : [ {"street" : "100"}, {"street" : "300"}]
 10    }') as val from dual 
 11  )
 12  select
 13    sample.*
 14  from sample
 15  where json_exists(val, '$.address[*]?(@.street == "200")')

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

UPD：如果你想使用json_table和where过滤器，那么最好使用exists predicate 或使用横向连接和一个访问表的子查询，如果有多行需要值，不要分解结果集。

假设这个样本数据（还有一个street: 200条目）：

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

交叉连接：

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

exists：

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

或cross join lateral，带子查询：

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

fiddle