I have a table in Oracle DB with a JSON column. The column is array of integers:
CREATE TABLE orders ( id NUMBER, products CLOB CHECK (products IS JSON) ); INSERT INTO orders VALUES (1, '[5, 8, 12]'); INSERT INTO orders VALUES (2, '[3, 7, 19]');
I want to select rows where products array contains number 8 for example. What would be the correct SQL query for that?
select * from orders where json_exists(products, '$[*]?(@ == 8)') ;