Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 691
Shamil Yakupov Avatar asked Jun 12 '15 20:06

Shamil Yakupov


2 Answers

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.

like image 98
Sami Kuhmonen Avatar answered Oct 24 '22 21:10

Sami Kuhmonen


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
like image 28
Erwin Brandstetter Avatar answered Oct 24 '22 21:10

Erwin Brandstetter