title: Generate multiple cross joins with Postgresql
body: <p>I have a JSON array containing objects with two members named <code>first</code> and <code>second</code>. Each one has a value array. I would like to generate the rows with all possible combinations between the values in <code>first</code> with the values in <code>second</code> for one given object and then do the same with the next one, and so on. There can be any number of objects. Some of the values in the arrays could be identical.</p>
  <p>Example 1:</p>
  <pre class="lang-json prettyprint-override"><code>[{&quot;first&quot;:[&quot;1 - one&quot;, &quot;2 - two&quot;, &quot;3 - three&quot;], &quot;second&quot;: [1, 2]}]
  </code></pre>
  <p>should give me</p>
  <pre><code>   first   | second
  -----------+--------
   1 - one   |      1
   1 - one   |      2
   2 - two   |      1
   2 - two   |      2
   3 - three |      1
   3 - three |      2
  (6 rows)
  </code></pre>
  <p>Example 2:</p>
  <pre class="lang-json prettyprint-override"><code>[
   {&quot;first&quot;:[&quot;1 - one&quot;, &quot;2 - two&quot;, &quot;3 - three&quot;], &quot;second&quot;: [1, 2]},
   {&quot;first&quot;:[&quot;4 - four&quot;, &quot;5 - five&quot;], &quot;second&quot;: [10]}
  ]
  </code></pre>
  <p>should give me</p>
  <pre><code>   first   | second
  -----------+--------
   1 - one   |      1
   1 - one   |      2
   2 - two   |      1
   2 - two   |      2
   3 - three |      1
   3 - three |      2
   4 - four  |     10
   5 - five  |     10
  (8 rows)
  </code></pre>
  <p>When there is only one object, the following query is working well.</p>
  <pre class="lang-sql prettyprint-override"><code>with t1(j) as (
    select jsonb_array_elements('[{&quot;first&quot;:[&quot;1 - one&quot;, &quot;2 - two&quot;, &quot;3 - three&quot;], &quot;second&quot;: [1, 2]}]'::jsonb)
  ),
  t2(first, second) as (
    select * from
      (select jsonb_array_elements_text(j-&gt;'first') from t1) as first
      cross join
      (select jsonb_array_elements_text(j-&gt;'second')::smallint from t1) as second
  )
  select * from t2 order by first, second;
  </code></pre>
  <p>Of course the same query with multiple objects doesn't work since it is doing a <code>cross join</code> between all the values of <code>first</code> and <code>second</code> instead of respecting the pairs.</p>
  <pre class="lang-sql prettyprint-override"><code>with t1(j) as (
    select jsonb_array_elements('[{&quot;first&quot;:[&quot;1 - one&quot;, &quot;2 - two&quot;, &quot;3 - three&quot;], &quot;second&quot;: [1, 2]},{&quot;first&quot;:[&quot;4 - four&quot;, &quot;5 - five&quot;], &quot;second&quot;: [10]}]'::jsonb)
  ),
  t2(first, second) as (
    select * from
      (select jsonb_array_elements_text(j-&gt;'first') from t1) as first
      cross join
      (select jsonb_array_elements_text(j-&gt;'second')::smallint from t1) as second
  )
  select * from t2 order by first, second;
  </code></pre>
  <pre><code>   first   | second
  -----------+--------
   1 - one   |      1
   1 - one   |      2
   1 - one   |     10
   2 - two   |      1
   2 - two   |      2
   2 - two   |     10
   3 - three |      1
   3 - three |      2
   3 - three |     10
   4 - four  |      1
   4 - four  |      2
   4 - four  |     10
   5 - five  |      1
   5 - five  |      2
   5 - five  |     10
  (15 rows)
  </code></pre>
  <p>How should I alter the query to make it work ? I thought about a recursive CTE but couldn't figure out how to write it.</p>