title: Querying nested JSON arrays in PostgreSQL
body: <p>I am currently experimenting with the JSON functionality PostgreSQL. While queries of nested key-value pair objects are very easy to query, I am having trouble querying JSON arrays. To be more specific, I have a table <code>mytable</code> with two columns: An integer primary key <code>id</code> and a JSONB column <code>info</code>. The JSONB data have the following structure:</p>
  
  <pre><code>{
    "modes": [
      {
        "params": [
          {"name": "x", "value": 10},
          {"name": "y", "value": 15}
        ]
      },
      {
        "params": [
          {"name": "x", "value": 20},
          {"name": "y", "value": 25}
        ]
      }
    ]
  }
  </code></pre>
  
  <p>I want to select all table rows that have at least one <code>params</code> element whose <code>name</code> is <code>x</code> and has a value between <code>15</code> and <code>25</code>. A pseudo-SQL query would be:</p>
  
  <pre><code>SELECT 
    * 
  FROM 
    mytable 
  WHERE 
    (info-&gt;'modes[*]'-&gt;'params[*]'-&gt;&gt;'name')::TEXT = 'x'
    AND
    (info-&gt;'modes[*]'-&gt;'params[*]'-&gt;&gt;'value')::FLOAT BETWEEN 15 AND 25;
  </code></pre>
  
  <p>I am using PostgreSQL 9.6.2</p>