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. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

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