Re: How to unnest nested arrays

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

út 7. 4. 2020 v 7:25 odesílatel Guyren Howe <guyren@xxxxxxxxx> napsal:

On Apr 6, 2020, at 19:44 , David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <guyren@xxxxxxxxx> wrote:

Consider this:

select (array[array[1, 2], array[3, 4]])[i:i]

from generate_subscripts(array[array[1, 2], array[3, 4]], 1) i

which produces:

{{1,2}}

{{3,4}}

I expect and want, from that source:

{1, 2}

{3, 4}

Also: could we _please_ get a version of unnest that doesn’t explode any number of dimensions into 1?

Here’s a solution in pure SQL, for reference:

CREATE OR REPLACE FUNCTION public.pairwise(

    cards card[]

)

RETURNS table(c1 card, c2 card)

LANGUAGE sql

AS $function$

with

individual_cards as (

    select

        *

    from 

        unnest(cards) with ordinality c

)

select

    c(c1.suit, c1.rank),

    c(c2.suit, c2.rank)

from 

    individual_cards c1 join

    individual_cards c2 on c1.ordinality = c2.ordinality - 1

where 

    c1.ordinality % 2 = 1

    

$function$

;

Given that Postgres often (with good cause) touts its type system, it’s a shame that this basic structured type is great in many ways, but seriously flawed in really simple ones.

This task is not hard, but it is not supported by any special syntax. There are two forces - power of syntax, and complexity of syntax.

It can be reduced little bit

create or replace function unnest_nested2(anyarray)
returns setof anyarray as $$
  select array_agg(v)

   from unnest($1) with ordinality v

  group by (ordinality - 1) / array_length($1,1)
$$ language sql;