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

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

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.

Solution

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