Select rows where json array contains specific element

I have a table in Oracle DB with a JSON column. The column is array of integers:

    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?

>Solution :

select *
from   orders
where  json_exists(products, '$[*]?(@ == 8)')

Leave a Reply