title: Add ORDINALITY to expanded JSON array in Postgres 11.7
body: <p>I'm taking two JSONB arrays, unpacking them, and combing the results. I'm trying to add <code>WITH ORDINALITY</code> to the JSON array unpacking. I've been unable to figure out how to add <code>WITH ORDINALITY</code>. For some reason, I can't find <code>WITH ORDINALITY</code> in the documentation for Postgres 11's JSON tools:</p>
  
  <p><a href="https://www.postgresql.org/docs/11/functions-json.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/11/functions-json.html</a></p>
  
  <p>I've seen examples using <code>jsonb_array_elements....WITH ORDINALITY</code>, but haven't been able to get it to work. First, a functional example based on Postgres arrays:</p>
  
  <pre><code>WITH
  first AS (
  SELECT * FROM
      UNNEST (ARRAY['Charles','Jane','George','Percy']) WITH ORDINALITY AS x(name_, index)
  ),
  
  last AS (
  SELECT * FROM
      UNNEST (ARRAY['Dickens','Austen','Eliot']) WITH ORDINALITY AS y(name_, index)
  )
  
  SELECT first.name_ AS first_name,
         last.name_  AS last_name
  
    FROM first
    JOIN last ON (last.index = first.index)
  </code></pre>
  
  <p>This gives the desired output:</p>
  
  <pre><code>first_name  last_name
  Charles     Dickens
  Jane        Austen
  George      Eliot
  </code></pre>
  
  <p>I'm using the <code>ORDINALITY</code> index to make the <code>JOIN</code>, as I'm combining two lists for pair-wise comparison. I can assume my lists are equally sized.</p>
  
  <p>However, my input is going to be a JSON array, not a Postgres array. I've got the unpacking working with <code>jsonb_to_recordset</code>, but have not got the ordinality generation working. Here's a sample that does the unpacking part correctly:</p>
  
  <pre><code>DROP FUNCTION IF EXISTS tools.try_ordinality (jsonb, jsonb);
  CREATE OR REPLACE FUNCTION tools.try_ordinality (
       base_jsonb_in         jsonb,
       comparison_jsonb_in   jsonb)
  
  RETURNS TABLE (
      base_text         citext,
      base_id           citext,
      comparison_text   citext,
      comparison_id     citext)
  
  AS $BODY$
  
  BEGIN
  
  RETURN QUERY
  
  WITH
  base_expanded AS (
  
   select *
     from jsonb_to_recordset (
            base_jsonb_in) 
        AS base_unpacked (text citext, id citext)
   ),
  
  comparison_expanded AS (
  
   select *
     from jsonb_to_recordset (
            comparison_jsonb_in)
        AS comparison_unpacked (text citext, id citext)
   ),
  
  combined_lists AS (
  select base_expanded.text       AS base_text,
         base_expanded.id         AS base_id,
         comparison_expanded.text AS comparison_text,
         comparison_expanded.id   AS comparison_id
  
    from base_expanded,
         comparison_expanded
  )
  
  select * 
  
     from combined_lists;
  
  END
  $BODY$
  LANGUAGE plpgsql;
  </code></pre>
  
  <pre><code>select *  from try_ordinality (
  '[
      {"text":"Fuzzy Green Bunny","id":"1"},
      {"text":"Small Gray Turtle","id":"2"}
      ]',
  
  '[
      {"text":"Red Large Special","id":"3"},
      {"text":"Blue Small","id":"4"},
      {"text":"Green Medium Special","id":"5"}
    ]'
  );
  </code></pre>
  
  <p>But that's a <code>CROSS JOIN</code></p>
  
  <pre><code>base_text    base_id    comparison_text    comparison_id
  Fuzzy Green Bunny    1    Red Large Special      3
  Fuzzy Green Bunny    1    Blue Small             4
  Fuzzy Green Bunny    1    Green Medium Special   5
  Small Gray Turtle    2    Red Large Special      3
  Small Gray Turtle    2    Blue Small             4
  Small Gray Turtle    2    Green Medium Special   5
  </code></pre>
  
  <p>I'm after a pair-wise result with only two rows:</p>
  
  <pre><code>Fuzzy Green Bunny    1    Red Large Special    3
  Small Gray Turtle    2    Blue Small           4
  </code></pre>
  
  <p>I've tried switching to <code>jsonb_array_elements</code>, as in this snippet:</p>
  
  <pre><code>WITH
  base_expanded AS (
  
   select *
     from jsonb_array_elements (
            base_jsonb_in) 
        AS base_unpacked (text citext, id citext)
   ),
  </code></pre>
  
  <p>I get back</p>
  
  <pre><code>ERROR:  a column definition list is only allowed for functions returning "record"
  </code></pre>
  
  <p>Is there a straightforward way to get ordinality on an unpacked JSON array? It's very easy with <code>UNNEST</code> on a Postgres array.</p>
  
  <ul>
  <li><p>I'm happy to learn I've screwed up the syntax.</p></li>
  <li><p>I can <code>CREATE TYPE</code>, if it's of any help.</p></li>
  <li><p>I can convert to a Postgres array, if that's straightforward to do.</p></li>
  </ul>
  
  <p>Thanks for any suggestions.</p>