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,...}
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.
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With