Better explanation of unnest with ordinality

[-]

Hi guys!
I’ve recently come across a query of the kind:
select * from unnest(array[1,2,3,4]) with ordinality t;
and was asked whether ordinality value is guaranteed to be the same as the 
array index of the array element in the same tuple. The only relevant thing 
about ordinality column I’ve found is here 
https://www.postgresql.org/docs/13/queries-table-expressions.html 
 but it’s 
not clear what is the order of function result set. According to ISO SQL:2011 
standard draft (the only one I’ve found in internet) (7.6 ) 
unnest is not a function but a special syntax to make a table from collection. 
It states that in case of array argument and "with ordinality" this statement 
returns ordinality column that matches array indexes. Is it possible to clear 
it out in docs or maybe it’s an intentional deviation from standard so we can’t 
rely on  == ?

Re: Better explanation of unnest with ordinality

[Tom Lane]

-  writes:
> I’ve recently come across a query of the kind:
> select * from unnest(array[1,2,3,4]) with ordinality t;
> and was asked whether ordinality value is guaranteed to be the same as the 
> array index of the array element in the same tuple. The only relevant thing 
> about ordinality column I’ve found is here 
> https://www.postgresql.org/docs/13/queries-table-expressions.html 
>  but it’s 
> not clear what is the order of function result set. According to ISO SQL:2011 
> standard draft (the only one I’ve found in internet) (7.6 ) 
> unnest is not a function but a special syntax to make a table from 
> collection. It states that in case of array argument and "with ordinality" 
> this statement returns ordinality column that matches array indexes. Is it 
> possible to clear it out in docs or maybe it’s an intentional deviation from 
> standard so we can’t rely on  == ?

Hmm.  I think 7.2.1.4 is clear enough:

If the WITH ORDINALITY clause is specified, an additional column of
type bigint will be added to the function result columns. This column
numbers the rows of the function result set, starting from 1.

(The SELECT reference page is vaguer, though, which ought to be improved.)

What is less clear is the definition of UNNEST, for which that text refers
you to 9.19, which says only:

Expands an array to a set of rows.

unnest(ARRAY[1,2]) →
 1
 2

I'm inclined to expand that, now that we have room for
more-than-three-words-of-explanation, to

Expands an array to a set of rows. Multi-dimensional arrays are read
out in storage order.

unnest(ARRAY[1,2]) →
 1
 2
unnest(ARRAY[['foo','bar'],['baz','quux']]) →
 foo
 bar
 baz
 quux

I haven't checked your claim that the spec says that ordinality matches
the array indexes.  But it seems pretty meaningless for Postgres; what
would you do for multidimensional arrays?  Another thing that we have
that's not in the SQL spec is arrays with first index different from 1.
WITH ORDINALITY still counts from 1 in that case:

=# select * from unnest('[-1:2]={1,2,3,4}'::int[]) with ordinality t;
 t | ordinality 
---+
 1 |  1
 2 |  2
 3 |  3
 4 |  4
(4 rows)

Maybe that's a spec violation, or maybe it isn't, but we're not
going to change it.  WITH ORDINALITY is implemented independently
of the particular SRF being expanded, so it couldn't take account
of the array subscripts even if we wanted it to.

regards, tom lane

Re: Better explanation of unnest with ordinality

[m7o...@gmail.com]

On Wed, Jan 27, 2021 at 7:47 PM Tom Lane  wrote:
>
> What is less clear is the definition of UNNEST, for which that text refers
> you to 9.19

What about some json functions that return setof, e.g.
jsonb_to_recordset
(https://www.postgresql.org/docs/13/functions-json.html)?
Should we keep the same semantics with json array ordering as with
ordinary arrays?