LATERAL WITH ORDINALITY - numbering sets   

Basic WITH ORDINALITY

Here is the simple example of its use:

SELECT *
FROM unnest('{my,dog, eats, dog food}'::text[] ) 
    WITH ordinality;

  unnest  | ordinality
----------+------------
 my       |          1
 dog      |          2
 eats     |          3
 dog food |          4

If you aren't happy with the default column names, you can change them by revising your query like this

 
SELECT f.*
FROM unnest('{my,dog, eats, dog food}'::text[] ) 
    WITH ordinality As f(phrase, sort_order);

Which would output this:

  phrase  | sort_order
----------+------------
 my       |          1
 dog      |          2
 eats     |          3
 dog food |          4

LATERAL WITH ORDINALITY

The greatest value of WITH ORDINALITY comes when you apply it to rows of data. How do you do that when you need to use WITH ORDINALITY. This is where one of our favorite constructs, the LATERAL construct comes to the rescue.

First let's construct our table with a text array column for demonstration. Note that the fish has no tags.

CREATE TABLE pets(pet varchar(100) PRIMARY KEY, tags text[]);
INSERT INTO pets(pet, tags)
    VALUES ('dog', '{big, furry, friendly, eats steak}'::text[]),
        ('cat', '{small, snob, eats greenbeans, plays with mouse}'::text[]),
        ('mouse', '{very small, fits in pocket, eat peanuts, watches cat}'::text[]),
        ('fish', NULL);

If you do a cross join, you'll leave out fish because he's got no tags

SELECT pet, sort_order, tag
FROM pets, unnest(tags) 
    WITH ORDINALITY As f(tag, sort_order) ;

 pet  | sort_order |       tag
------+------------+------------------
dog   |          1 | big
dog   |          2 | furry
dog   |          3 | friendly
dog   |          4 | eats steak
cat   |          1 | small
cat   |          2 | snob
cat   |          3 | eats greenbeans
cat   |          4 | plays with mouse
mouse |          1 | very small
mouse |          2 | fits in pocket
mouse |          3 | eat peanuts
mouse |          4 | watches cat

In order to include pets that have no tags, you need to do a LEFT JOIN like so

SELECT pet, sort_order, tag
FROM pets LEFT JOIN 
    LATERAL unnest(tags) 
        WITH ORDINALITY As f(tag, sort_order) ON true;

And voila, all our pets are accounted for:

 pet  | sort_order |       tag
------+------------+------------------
dog   |          1 | big
dog   |          2 | furry
dog   |          3 | friendly
dog   |          4 | eats steak
cat   |          1 | small
cat   |          2 | snob
cat   |          3 | eats greenbeans
cat   |          4 | plays with mouse
mouse |          1 | very small
mouse |          2 | fits in pocket
mouse |          3 | eat peanuts
mouse |          4 | watches cat
fish  |            |