title: Search a JSON array in Oracle
body: <p>I'm trying to use the new JSON features introduced in Oracle 12.1.0.2</p>
  
  <p>However I can't seem to find a way to look for a specific value in an array inside my JSON document. </p>
  
  <p>Consider the following table and data:</p>
  
  <pre><code>create table orders
  (
     id      integer not null primary key,
     details clob not null check (details is json (strict))
  );
  
  insert into orders (id, details) values 
  (1, '{"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "delivery_address": "My hometown"}');
  
  insert into orders (id, details) values 
  (2, '{"products": [{ "product": 42, "quantity": 1}, {"product": 10, "quantity": 2}], "comment": "Your website is too slow"}');
  
  insert into orders (id, details) values 
  (3, '{"products": [{ "product": 543, "quantity": 1}], "discount": "15"}');
  
  insert into orders (id, details) values 
  (4, '{"products": [{ "product": 738, "quantity": 12}], "discount": "32"}');
  </code></pre>
  
  <p>Now I'm trying to write a SQL query that returns all orders, where product #2 was ordered. </p>
  
  <p>I can't use <a href="https://docs.oracle.com/database/121/SQLRF/conditions010.htm#SQLRF56664"><code>json_exists</code></a> because it doesn't allow array expressions (and I wouldn't know how to specify the value anyway).</p>
  
  <p><a href="https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56668"><code>json_value</code></a> only returns a single value, so I can't "iterate" over the array values. </p>
  
  <p>I tried: </p>
  
  <pre><code>select *
  from orders o
  where json_value(details, '$.products[*].product') = '2';
  </code></pre>
  
  <p>but that didn't return anything. </p>
  
  <p>I also tried <a href="https://docs.oracle.com/database/121/SQLRF/functions091.htm#SQLRF56973"><code>json_table</code></a>, but that also seems to only take the first element from the array:</p>
  
  <pre><code>select *
  from orders o, 
       json_table(o.details, '$' columns (product_id integer path '$.products[*].product')) t
  where t.product_id = 2;
  </code></pre>
  
  <p>But that didn't show anything. Apparently the "star expansion" in the "<a href="https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56668">array_step</a>" doesn't expand the values in the <code>json_table</code></p>
  
  <p>So my question is: </p>
  
  <p>how can I (based on the above sample data) retrieve all orders where the product with the number 2 has been ordered?</p>
  
  <p>I am essentially looking for the  equivalent to this Postgres query:</p>
  
  <pre><code>select *
  from orders
  where details @&gt; '{"products": [{"product": 2}] }';
  </code></pre>