Select every first element of array of integer arrays to array

Question

How to select every first element of array of integer arrays to array?
{{1,2,3},{2,15,32},{5,16,14},...} -> {1,2,5,...}

Some more information about your context would be appropriate. Postgres version? Table definition? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 15, 2015 at 13:17

Sami Kuhmonen · Accepted Answer · 2015-06-12 20:59:00Z

Since PostgreSQL will allow asking for a slice outside of the array size, and assuming there will never be more than 999 subarrays, we can use this monstrosity

WITH data AS ( SELECT array[array[1,2,3], array[2,15,32], array[5,16,14]] as arr) SELECT array_agg(arr) FROM (SELECT unnest(arr[1:999][1]) as arr from data) data2;

You can of course make the constant 999 larger if needed, it is just a random large number I threw in there.

The reason why this is so complicated is that if you would use just arr[1:999][1] you would still get a two-dimensional array, but with only the first elements. In this case {{1}, {2}, {5}}. If we use unnest() we can make it into a set, which can then be fed into array_agg() via subselect.

It would be nice to use array_agg(unnest(arr[1:999][1])) but the aggregation function doesn't like sets and I don't know if there is a way to convert it on the fly.

You can also use the actual array length, but it might cause unnecessary computation

SELECT unnest(arr[1:array_length(arr, 1)][1]) as arr from data

Note

If the arrays could be unnested by one level, you could just index the arrays and then use array_agg() to convert it back into an array with a lot simpler syntax

WITH data AS (SELECT array[1,2,3] as arr UNION ALL SELECT array[2,15,32] as arr UNION ALL SELECT array[5,16,14] as arr) SELECT array_agg(arr[1]) from data;

The CTE is there just for input data, the actual meat is the array_agg(arr[1]). This will of course work for any number of input arrays.

The query applied to a set of rows gives an incorrect result.
The same query without the use of unnest returned an array like I needed.

Erwin Brandstetter · Accepted Answer · 2021-10-23 13:25:12Z

Given this table and values:

CREATE TABLE arrtbl ( arrtbl_id serial PRIMARY KEY , arr int[] ); INSERT INTO arrtbl (arr) VALUES ('{{1,2,3},{2,15,32},{5,16,14}}') , ('{{17,22},{1,15},{16,14}}') -- dimensions can vary across rows! , ('{}') , (null);

This would do the job for all rows:

SELECT arrtbl_id, array_agg(a) AS a1 FROM arrtbl t , unnest(t.arr[:][1]) a GROUP BY 1;

Why [:]?

Normalize array subscripts for 1-dimensional array so they start with 1

So only if there can be non-standard array-subscripts.

Result:

arrtbl_id | a1 ----------+----------- 1 | '{1,2,5}' 2 | '{17,1,16}'

Rows with empty / NULL array in arr are dropped from the result.
Also, while the above usually works, rather use this safe syntax:

SELECT arrtbl_id, array_agg(a.a ORDER BY a.ordinality) FROM arrtbl t LEFT JOIN LATERAL unnest(t.arr[:][1]) WITH ORDINALITY a ON true GROUP BY 1;

The same, more explicit, and a single sort in a subquery is typically faster:

SELECT arrtbl_id, array_agg(elem) FROM ( SELECT t.arrtbl_id, a.elem FROM arrtbl t LEFT JOIN LATERAL unnest(t.arr[:][1]) WITH ORDINALITY a(elem, ord) ON true ORDER BY t.arrtbl_id, a.ord ) sub GROUP BY 1 ORDER BY 1;

Result:

arrtbl_id | a1 ----------+----------- 1 | '{1,2,5}' 2 | '{17,1,16}' 3 | null 4 | null

db<>fiddle here

Detailed explanation:

PostgreSQL unnest() with element number

Collectives™ on Stack Overflow

Select every first element of array of integer arrays to array

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related