title: Speed up range test for key values nested in jsonb array of objects
body: <p>Suppose I have the following <code>parents</code> table: </p>
  
  <pre class="lang-sql prettyprint-override"><code>create table parents (
    id       integer not null constraint parents_pkey primary key,
    name     text    not null,
    children jsonb   not null
  );
  </code></pre>
  
  <p>Where <code>children</code> is a <strong>json array</strong> of the following structure:</p>
  
  <pre class="lang-json prettyprint-override"><code>[
      {
          "name": "child1",
          "age": 10
      }, 
      {
          "name": "child2",
          "age": 12
      } 
  ]
  </code></pre>
  
  <p>And I need, for example, to get all parents that have children with age between 10 and 12.</p>
  
  <p>I create the following query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select distinct
    p.*
  from
    parents p, jsonb_array_elements(p.children) c
  where
    (c-&gt;&gt;'age')::int between 10 and 12;
  </code></pre>
  
  <p>It works well but very slowly when the table <code>parents</code> is big (for example 1M records). I tried to use 'gin' index on <code>children</code> field but this did not help.</p>
  
  <p>So is there a way to speed up such queries? Or maybe there is another solution to make queries/indexes against <strong>fields</strong> in the <strong>nested json arrays</strong>?</p>
  
  <p><strong>Query plan</strong>:</p>
  
  <pre><code>Unique  (cost=1793091.18..1803091.18 rows=1000000 width=306) (actual time=4070.866..5106.998 rows=399947 loops=1)
    -&gt;  Sort  (cost=1793091.18..1795591.18 rows=1000000 width=306) (actual time=4070.864..4836.241 rows=497313 loops=1)
          Sort Key: p.id, p.children, p.name
          Sort Method: external merge  Disk: 186040kB
          -&gt;  Gather  (cost=1000.00..1406321.34 rows=1000000 width=306) (actual time=0.892..1354.147 rows=497313 loops=1)
                Workers Planned: 2
                Workers Launched: 2
                -&gt;  Nested Loop  (cost=0.00..1305321.34 rows=416667 width=306) (actual time=0.162..1794.134 rows=165771 loops=3)
                      -&gt;  Parallel Seq Scan on parents p  (cost=0.00..51153.67 rows=416667 width=306) (actual time=0.075..239.786 rows=333333 loops=3)
                      -&gt;  Function Scan on jsonb_array_elements c  (cost=0.00..3.00 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1000000)
                            Filter: ((((value -&gt;&gt; 'age'::text))::integer &gt;= 10) AND (((value -&gt;&gt; 'age'::text))::integer &lt;= 12))
                            Rows Removed by Filter: 3
  Planning time: 0.218 ms
  Execution time: 5140.277 ms
  </code></pre>