CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Shh
Shh

Reputation: 1006

postgres array of arrays to columns

Sample of my table

Table_A
id integer
arr varchar(20) []

Sample data

id.         arr
1.    {{'a', 'b', 'c'}, {'d', 'test1', 'sample1'}}
2.    {{'sample2', sample3, sample4'}}
3.     null
4.    {{'sample6', 'sample7', 'test done'}}

I want to select to have the data as:

id.     col1.       col2          col3  
1       'a'         'b'.          'c'
1.      'd'.       'test1'.      'sample1'
2.      'sample2'  'sample3'.    'sample4'
3.      null        null          null
4.     'sample6'.  'sample7'.    'test done'

the data is guaranteed to have 5 elements in array if not null. Sample data above is with 3 elements for convenience. It's a fairly large table which will grow over time. Right now I am doing it using unnest and then row_number() over () and then joining them with id. Is there any better way of doing it.

Upvotes: 2

Views: 4798

Answers (2)

S-Man
S-Man

Reputation: 23766

unnest() for a regular multidimensional array has the problem, that it expands all elements of all dimensions, not only the first one. So, there is no way for these types to get this done.

A possible workaround is casting the array into a JSON array. For JSON arrays this is not a problem:

demo:db<>fiddle

SELECT
    id,
    elems ->> 0 AS col1,
    elems ->> 1 AS col2,
    elems ->> 2 AS col3
FROM
    table_a,
    json_array_elements(array_to_json(arr)) elems

json_array_elements() expands the first dimension of the array, so the subarrays are moved into their one row. Afterwards simple get their elemens by their indexes.

The query above eliminates the NULL rows because of its comma notation (which, in fact, is the shortcut for INNER JOIN LATERAL in this case). This can be avoided using an LEFT OUTER JOIN LATERAL instead:

demo:db<>fiddle 

SELECT
    id,
    elems ->> 0 AS col1,
    elems ->> 1 AS col2,
    elems ->> 2 AS col3
FROM
    table_a
LEFT JOIN LATERAL
    json_array_elements(array_to_json(arr)) elems ON TRUE

Upvotes: 3

Pavel Stehule
Pavel Stehule

Reputation: 45940

There is only one alternative - write procedural code in some PL language like Perl, Python or PLpgSQL. The code in Perl or Python will be more dynamic (you don't need special type), the code in PLpgSQL is easy to write. Probably procedural code should be faster for large arrays.

CREATE OR REPLACE FUNCTION unnest_to_columns3(a text[])
RETURNS TABLE(col1 text, col2 text, col3 text)
AS $$
DECLARE r text[];
BEGIN
  FOREACH r SLICE 1 IN ARRAY a 
  LOOP
    col1 := r[1]; col2 := r[2]; col3 := r[3];
    RETURN NEXT;
  END LOOP;
END
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM unnest_to_columns3('{{a,b,c},{Ahoj,Nazdar,Servus}}');
+------+--------+--------+
| col1 |  col2  |  col3  |
+------+--------+--------+
| a    | b      | c      |
| Ahoj | Nazdar | Servus |
+------+--------+--------+
(2 rows)

Note: PostgreSQL has not a array of arrays - it has only multidimensional arrays.

It is much more practical work with arrays of records than multidimensional arrays. There is simple conversion between relations and arrays of this type.

create table foo(v1 text, v2 text, v3 text);
insert into foo values('ahoj','nazdar','servus');
insert into foo values('hi','greating',null);

postgres=# select array_agg(foo) from foo;
+-------------------------------------------+
|                 array_agg                 |
+-------------------------------------------+
| {"(ahoj,nazdar,servus)","(hi,greating,)"} |
+-------------------------------------------+
(1 row)

postgres=# select * from unnest('{"(ahoj,nazdar,servus)","(hi,greating,)"}'::foo[]);
+------+----------+--------+
|  v1  |    v2    |   v3   |
+------+----------+--------+
| ahoj | nazdar   | servus |
| hi   | greating | ∅      |
+------+----------+--------+
(2 rows)

Important note - relation (table) is not a array, and a arrays is not a relation (table).

Upvotes: 2

Related Questions