Search Postgresql Archives

Re: How to unnest nested arrays

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





út 7. 4. 2020 v 4:44 odesílatel David G. Johnston <david.g.johnston@xxxxxxxxx> napsal:
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?

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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux