CODEWITHSUNDEEP
sqlarraysdatabasepostgresqlsorting
cinfis
cinfis

Reputation: 345

How to sort two dimensional int array in PostgreSQL?

{{5,23}, {8,45}, {1,12}}

I want to sort this array according the first element of each sub-array elements, like;

{{1,12}, {5,23}, {8,45}}

How can I do that?

Edit:

This code works;

create or replace function arraysortingaccordingfirstindexofsubarrayelements()
returns void as $$
declare samplearraydata integer[][];
declare sortedarraydata int[][];
begin
samplearraydata:=ARRAY[[5,8], [1,6],[3,9]];
EXECUTE 'CREATE TEMP TABLE temptable (
   firstindex integer,
   secondindex integer
) on commit drop;';
WITH
    data as (select samplearraydata as arr) 
insert into temptable select   
    arr[i][1], 
    arr[i][2] FROM data, 
    generate_subscripts((SELECT arr FROM data), 1) i 
    order by 1;
sortedarraydata:=(SELECT array_agg_mult(ARRAY[ARRAY[y.firstindex, y.secondindex]])) FROM   temptable y;
raise notice '%', sortedarraydata;
end;
$$ language plpgsql;

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

CREATE TEMP TABLE arrtbl (
   firstindex integer,
   secondindex integer
) on commit drop;

Credits to Erwin:)

Upvotes: 1

Views: 2470

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656962

Postgres 9.5 or newer

ships with a variant of array_agg() that serves as drop-in replacement for the custom aggregate function array_agg_mult() below - and is considerably faster. The manual:

Function                Argument Type(s)   Return Type
array_agg(expression)   any array type     same as argument data type  

Description
input arrays concatenated into array of one higher dimension
(inputs must all have same dimensionality, and cannot be empty or NULL)

Postgres 9.4 or older

Aggregate function

The built-in aggregate function array_agg() currently (Postgres 9.4) only works for non-array input data types. Since we are going to aggregate arrays, we need a custom aggregate function like detailed in this related answer:

CREATE AGGREGATE array_agg_mult (anyarray) (
   SFUNC     = array_cat
 , STYPE     = anyarray
 , INITCOND  = '{}'
);

Accessing array

Basic understanding of array syntax / how to access arrays is required. Read this chapter of the manual if you aren't there, yet.

Query

Then, based on the same setup as this earlier answer today (you may want to read that one, too):

SELECT arrtbl_id
     , array_agg_mult(arr[i:i][lo2:up2] ORDER BY arr[i:i][lo2])
FROM  (SELECT arrtbl_id, arr
            , array_lower(arr, 2) AS lo2
            , array_upper(arr, 2) AS up2
       FROM   arrtbl) t
     , generate_subscripts(t.arr, 1) i
GROUP  BY 1;

Explanation

  1. Compute lower and upper bound for the 2nd array dimension in the base table, that's cheaper than repeating it for every array slice.

    I am not simply starting with index 1 to cover a possible corner case. Postgres allows non-standard array subscripts:

  2. LATERAL join the base table to generate_subscripts(arr,1) to get the first index for each sub-array (no matter how many dimensions).

  3. The expression arr[i:i][lo2:up2] (or simply arr[i:i][:] in pg 9.6+) returns each array slice preserving dimensions. This works for any number of elements and dimensions (greater than 1).

  4. The expression arr[i:i][lo2] returns the first slice within each array slice, which defines sort order. For strictly 2-dimnesional arrays, you can use arr[i][lo2] to return the first element of each slice as well, but the former works for any dimensionality greater than 1.

  5. Aggregate with array_agg_mult() which happily accepts values of appropriate dimension (everything falls into place nicely).

Upvotes: 2

Radren
Radren

Reputation: 305

Since Postgres 9.4 if you can break your two dimensional array to two one dimensional arrays you can use operator

unnest(anyarray, anyarray [, ...])

Example:

SELECT * FROM unnest(ARRAY[6,4,3,5], ARRAY[64,2,1,-1], ARRAY[45,2,4,6]) ORDER BY 1

Return:

4   2   2
3   1   4
5   -1  6
6   64  45

If you want to sort by third elemen, you should change

... ORDER BY 3

Upvotes: 0

Related Questions