Oracle search array within JSON values?

Question

I have the following stored in an Oracle database as JSON:

{
   value: [1,2,3]
}

The value can be of any type (strings, integers or arrays). How would I query if the type is array and if it contains a certain value?

In pseudocode:

SELECT * FROM TABLE WHERE COLUMN_NAME.value CONTAINS 2

I can see how to query strings using Oracle functions such as json_query but cannot see how to run this specific type of query without selecting all data and searching on the client.

Answer 1

You may use JSON_TABLE in the FROM, defining the columns and then use it in where clause to filter rows.

--Test data
with t (id,j)
as
( select 1, TO_CLOB(
  '{
     value : [1,2,3]
   }') FROM DUAL
 ) 
--Test data ends--
select t.id,tbl.val FROM t cross join 
    json_table(j,'$.value[*]' columns (val varchar2(100) path '$') ) as tbl
where tbl.val = 2


    ID      VAL 
  ------  -------
     1      2