How to Get the Element Number When Using PostgreSQL's unnest() Function?

PostgreSQL unnest() Functions and element numbers

Question

When you encounter a column containing delimited values, the unnest() function provides a way to extract these values:

<code class="language-sql">myTable
id | elements
---+------------
1  |ab,cd,efg,hi
2  |jk,lm,no,pq
3  |rstuv,wxyz

select id, unnest(string_to_array(elements, ',')) AS elem
from myTable

id | elem
---+-----
1  | ab
1  | cd
1  | efg
1  | hi
2  | jk
...</code>

However, you may wish to include the element number as well, in the following format:

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

The ultimate goal is to get the original position of each element in the source string without using window functions like row_number() or rank() as these functions always return 1, probably because all elements are in the same row of the source table.

Solution

PostgreSQL 14 or higher

For comma separated strings, use string_to_table() instead of unnest(string_to_array()):

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t
LEFT   JOIN LATERAL string_to_table(t.elements, ',') WITH ORDINALITY AS a(elem, nr) ON true</code>

Demo

PostgreSQL 9.4 or higher

For functions that return a collection, use WITH ORDINALITY:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl AS t
LEFT   JOIN LATERAL unnest(string_to_array(t.elements, ',')) WITH ORDINALITY AS a(elem, nr) ON true</code>

LEFT JOIN ... ON true Ensures that all rows from the left table are retained regardless of whether the right table expression returns any rows.

Alternatively, since LEFT JOIN ... ON true retains all rows, a more concise version of the query can be used:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr)</code>

For actual arrays (arr are array columns), a more concise form can be used:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr)</code>

For simplicity, you can use the default column names:

<code class="language-sql">SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a</code>

Can be further simplified:

<code class="language-sql">SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a</code>

This final form returns all columns of tbl. Of course, explicit specification of column aliases and table-qualified columns can improve clarity.

a is used as both a table alias and a column alias (for the first column), and the default name of the appended ordinal column is ordinality.

PostgreSQL 8.4 - 9.3

Use row_number() OVER (PARTITION BY id ORDER BY elem) to get numbers based on sort order (rather than ordinal position):

<code class="language-sql">SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM  (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t</code>

While this generally works, and no failures have been observed in simple queries, PostgreSQL does not guarantee the ordering of rows without ORDER BY. Current behavior is the result of implementation details.

To ensure that the space-separated serial number of the elements in the string :

<code class="language-sql">SELECT id, arr[nr] AS elem, nr
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS nr
   FROM  (SELECT id, string_to_array(elements, ' ') AS arr FROM tbl) t
   ) sub</code>

For actual arrays, a simpler version can be used:

<code class="language-sql">SELECT id, arr[nr] AS elem, nr
FROM  (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t</code>

PostgreSQL 8.1 - 8.4

Since PostgreSQL versions 8.1 to 8.4 are missing some features, such as RETURNS TABLE, generate_subscripts(), unnest(), and array_length(), a custom SQL function named f_unnest_ord can be used:

<code class="language-sql">CREATE FUNCTION f_unnest_ord(anyarray, OUT val anyelement, OUT ordinality integer)
  RETURNS SETOF record
  LANGUAGE sql IMMUTABLE AS
'SELECT [i], i - array_lower(,1) + 1
 FROM   generate_series(array_lower(,1), array_upper(,1)) i'</code>

The modified function is as follows:

<code class="language-sql">myTable
id | elements
---+------------
1  |ab,cd,efg,hi
2  |jk,lm,no,pq
3  |rstuv,wxyz

select id, unnest(string_to_array(elements, ',')) AS elem
from myTable

id | elem
---+-----
1  | ab
1  | cd
1  | efg
1  | hi
2  | jk
...</code>

This extension function f_unnest_ord_idx returns additional idx columns. Compare:

<code class="language-sql">id | elem | nr
---+------+---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...</code>

Output

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM   tbl t
LEFT   JOIN LATERAL string_to_table(t.elements, ',') WITH ORDINALITY AS a(elem, nr) ON true</code>

The above is the detailed content of How to Get the Element Number When Using PostgreSQL's unnest() Function?. For more information, please follow other related articles on the PHP Chinese website!