Re: How to unnest nested arrays

út 7. 4. 2020 v 4:44 odesílatel David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> napsal:

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

is possible to write own function in PLpgSQL

CREATE OR REPLACE FUNCTION unnest_nested(a anyarray, OUT r anyarray)
RETURNS SETOF anyarray AS $$
BEGIN
FOREACH r SLICE 1 IN ARRAY a
LOOP
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;

postgres=# select unnest_nested(array[array[1, 2], array[3, 4]]);
┌───────────────┐
│ unnest_nested │
╞═══════════════╡
│ {1,2} │
│ {3,4} │
└───────────────┘
(2 rows)

Regards

Pavel

>
> David J.
>
>