Select rows where json array contains specific element-CodePudding

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?

CodePudding user response：

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

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/condition-JSON_EXISTS.html#GUID-D60A7E52-8819-4D33-AEDB-223AB7BDE60A