CODEWITHSUNDEEP
arrayspostgresqlplpgsql
Shamil Yakupov
Shamil Yakupov

Reputation: 5469

Select every first element of array of integer arrays to array

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,...}

Upvotes: 7

Views: 11076

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656814

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 [:]?

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:

Upvotes: 3

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

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.

Upvotes: 4

Related Questions