title: Querying dataset by conditions in nested JSONB document
body: <p>I have a fairly simple table in PostgreSQL database (v 9.6):</p>
  
  <pre><code>CREATE TABLE foobar (id serial, data jsonb);
  </code></pre>
  
  <p>Here is an example of JSONB document that is stored in data column:</p>
  
  <pre><code>[{ key: 'foo', value: 100 }, { key: 'bar', value: 5 }, { key: 'baz', value: 10 }]
  </code></pre>
  
  <p>I am trying to write a select that will return every row where conditions in JSONB document are met and select only specified nested documents; i.e. for every row where <code>foo</code> is greater than <code>X</code> return all nested documents that have <code>baz</code> as key. </p>
  
  <p>So far I've came with a query like this but it does not work — it is returning 0 records.</p>
  
  <pre><code>SELECT id
    FROM foobar
   WHERE (data-&gt;&gt;'key' = 'foo' AND (data-&gt;&gt;'value')::numeric &gt; 5)
      OR (data-&gt;&gt;'key' = 'bar' AND (data-&gt;&gt;'value')::numeric &lt; 10)
  </code></pre>
  
  <p>Also it would be great if anyone knows how to optimize the final query. Thanks!</p>