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.

Kaushik Nayak · Accepted Answer · 2019-03-23 07:17:40Z

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

answered Mar 23, 2019 at 7:17

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

THEK Over a year ago

Interestingly, when I combine tbl.val with a bind (:val), the DB resets my connection and throws a generic error. Has anyone else experienced this? It works if I use literals though.

Collectives™ on Stack Overflow

Oracle search array within JSON values?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related