Обсуждение: How to unnest nested arrays

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?

On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <guyren@gmail.com> 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?

The inability to reduce the number of dimensions of an existing array and the behavior of unnest are both consequences of the implementation of arrays in PostgreSQL.  If PostgreSQL could do what your main question is asking - using arrays only - then a function to unwrap an array one dimension at a time would likely already exist.

The phrase "explode any number of dimensions into 1 [dimension]" is not accurate, unnest does away with the array entirely and gives you back its contents, one cell per row.  It is in fact removing all nesting embedded within the array.  There is no dimension because there is no longer an array and the scalar types do not have dimensions - they are non-dimensioned.

The best you can probably do in SQL is take a text representation of the data and munge it.  You'll probably find array_to_string(array, delim) useful in that regard.  There may be an extension in the wild that does this...?

You might also investigate whether a function written in pl/perl or pl/python gets enough information, or has better tooling available, to handle this more gracefully.

JSON maybe...though at that point you may want to just consider changing the model.

David J.

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

On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <guyren@gmail.com> 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.