How to fetch nested data using JSON_VALUE when it contains an array of objects

Question

I have a table MYTABLE which has 3 columns as mentioned below.

|id | myclob_column | column3|

It has one column (myclob_column) which is clob data (json). Sample clob data is below.

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

I want to get all the rows from the table which has street value '200'.

Below select query is working when we are hard-coding the position (in our case [1]) :

select * from MYTABLE where JSON_VALUE(`myclob_column`, $.address[1].street) = '200';

But I can't hard-code the position of address as the value (200) can be in any position. So I need to have a generic query to match the street as 200 in any position. I tried many things but not able to do it.

Answer 1

You can do it using json_table to convert the JSON data into rows and columns in a table :

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

Demo here

Answer 2

you can parse JSON value and add condition without hardcoding as below,

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

you can see the live sql here

Answer 3

You may use json_exists with predicate on array elements:

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: If you want to use json_table with where filter, then it would be better to use exists predicate or use lateral join with a subquery accessing the table not to explode the result set if there are multiple rows with required value.

Assuming this sample data (with one more street: 200 entry):

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

Cross join:

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"}]     }

exists:

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

or cross join lateral with subquery:

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"}]     }

fiddle