Search a JSON array in Oracle

I do not have any installation of oracle available right now but I believe that the first string in json_table should be the path to the array which we want to produce rows from. Then inside COLUMNS, the path should be relative to the array, not the root.

Try this:

select *from orders o,      json_table(o.details, '$.products[*]'          columns (              product_id integer path '$.product'         )     ) twhere t.product_id = 2;

In 12.2 you can do this with JSON_EXISTS

 SQL> WITH ORDERS as   2  (   3    select 1 as ID, '{"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "delivery_address": "My hometown"}' as DETAILS   4      from dual   5    union all   6    select 2 as ID, '{"products": [{ "product": 42, "quantity": 1}, {"product": 10, "quantity": 2}], "comment": "Your website is too slow"}' as DETAILS   7      from dual   8    union all   9    select 3 as ID, '{"products": [{ "product": 543, "quantity": 1}], "discount": "15"}' as DETAILS  10      from dual  11    union all  12    select 4 as ID, '{"products": [{ "product": 738, "quantity": 12}], "discount": "32"}' as DETAILS  13     from dual  14  )  15  select *  16    from ORDERS  17   where JSON_EXISTS(DETAILS,'$?(@.products.product == $PRODUCT)' passing 2 as "PRODUCT")  18  /         ID ---------- DETAILS --------------------------------------------------------------------------------          1 {"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "d elivery_address": "My hometown"} SQL>