Re: How to unnest nested arrays

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.

David J.