with mytable
as (
select
'{
"name" : "Rahul",
"address" : [ {"street" : "300"}, {"street" : "200"}]
}' clob_column
from dual
)
select * from mytable t1, JSON_TABLE(t1.clob_column,'$.address[*]' COLUMNS (STREET varchar PATH '$.street')) t2 where t2.street = '100'no data found
with mytable
as (
select
'{
"name" : "Rahul",
"address" : [ {"street" : "100"}, {"street" : "200"}]
}' clob_column
from dual
)
select * from mytable t1, JSON_TABLE(t1.clob_column,'$.address[*]' COLUMNS (STREET varchar PATH '$.street')) t2 where t2.street = '100'| CLOB_COLUMN | STREET | { "name" : "Rahul", "address" : [ {"street" : "100"}, {"street" : "200"}] } | 100 |
|---|
with mytable
as (
select
'{
"name" : "Rahul",
"address" : [ {"street" : "100"}, {"street" : "200"}]
}' clob_column
from dual
)
select * from mytable t1, JSON_TABLE(t1.clob_column,'$.address[*]' COLUMNS (STREET varchar PATH '$.street')) t2 where t2.street = '200'| CLOB_COLUMN | STREET | { "name" : "Rahul", "address" : [ {"street" : "100"}, {"street" : "200"}] } | 200 |
|---|