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

4

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.

edited Jun 12, 2015 at 20:59

answered Jun 12, 2015 at 20:38

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Piotrowski Over a year ago

The query applied to a set of rows gives an incorrect result.

Jarrod L Over a year ago

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related