How to extract row from int[][] as int[] in postgres

Question

I'm totally confused about what sql array indexing does and what the return types of indexing [] should be.

I have a 3,2 array:

select ARRAY[
   [1,1],
   [2,2],
   [3,3]];

--> {{1,1},{2,2},{3,3}}

(BTW, pgadmin3 says this is an "array integer[]", not "array integer[][]").

Let's say I want to extract the first row (indexes start at 1 right?):

-- A
select (ARRAY[
   [1,1],
   [2,2],
   [3,3]])[1];
--> NULL

Huh? Why not {1,1} (of type int[]).

-- B
select (ARRAY[
   [1,1],
   [2,2],
   [3,3]])[1][:];

--> {{1,1}}

... seems legit. But:

-- C
select (ARRAY[
   [1,1],
   [2,2],
   [3,3]])[1][:][1];
--> {}

why is this different to A?

And how can I extract a row from an int[][] (1d array) as an int[] (1d array).

score 2 · Accepted Answer · edited May 23 '17 at 11:55

2

How to extract row from int[][] as int[] in postgres

By aggregation of unnested elements:

select array_agg(elem)
from unnest(array[[1,1],[2,2],[3,3]]) elem;

   array_agg   
---------------
 {1,1,2,2,3,3}
(1 row)

You can find more information in this post: How to get the dimensionality of an ARRAY column?

If you want to get the second subarray:

with my_data(id, arr) as (
values
(1, array[
   [1,1],
   [2,2],
   [3,3]]),
(2, array[
   [1,1,11],
   [2,2,22],
   [3,3,33]])
)

select array_agg(elem)
from my_data, 
unnest(arr[2:2]) elem
group by id;

 array_agg 
-----------
 {2,2}
 {2,2,22}
(2 rows)

Note1. The notation [2:2] means a slice from 2nd element to 2nd element, so it points to the second subarray.

Note 2. Multidimensional arrays must have array expressions with matching dimensions.

edited May 23 '17 at 11:55

Community

1
1

answered May 12 '17 at 19:07

klin

112,967
15
204
232

This gets all the rows. What if I want the second row? – user48956 May 12 '17 at 19:15
A '{{ ... }}' is a 2d array. I want a 1d array (the row). It makes a difference because arr[1] gives NULL is arr is 2d, or the element if its 1d. – user48956 May 12 '17 at 20:32
Use the first tip, the answer modified again. – klin May 12 '17 at 20:41
Its a little dangerous because it relies on the row width being fixed and known ahead of time. Arrays can vary in their dimension sizes within table's row. – user48956 May 12 '17 at 21:10
No, it doesn't rely on subarray widths. See the answer. – klin May 12 '17 at 21:19

Abelisto · Answer 2 · 2017-05-12T22:38:27.213

In addition to the klin's answer and using function provided here (with some fixes):

-- drop operator if exists -> (anyarray, int);
-- drop function if exists array_by_index(anyarray, int);
-- drop function if exists reduce_dim(anyarray);

create or replace function reduce_dim(anyarray)
returns setof anyarray as
$function$
declare
  s $1%type;
begin
  if ($1 = '{}') or ($1 is null) then
    return;
  else
    foreach s slice 1 in array $1 loop
      return next s;
    end loop;
    return;
  end if;
end;
$function$
language plpgsql immutable;

create function array_by_index(anyarray, int) returns anyarray
  language sql
  immutable
as $$
  select case when count(*) = 1 then reduce_dim($1[$2:$2]) else array_agg(x) end 
  from reduce_dim($1[$2:$2]) x
$$;

create operator -> (procedure = array_by_index, leftarg = anyarray, rightarg = int);

select 
  array[[[1,1],[2,2]],[[3,3],[4,4]]]->2,
  array[[1,2],[3,4],[5,6]]->3,
  array[[1,2],[3,4],[5,6]]->3->1;
╔═══════════════╤══════════╤══════════╗
║   ?column?    │ ?column? │ ?column? ║
╠═══════════════╪══════════╪══════════╣
║ {{3,3},{4,4}} │ {5,6}    │ {5}      ║
╚═══════════════╧══════════╧══════════╝

How to extract row from int[][] as int[] in postgres

2 Answers2