How to properly unnest two arrays

Question

I have two arrays: foo_array text[], bar_array text[] inside a function. Each of them contain strings that will be split into array elements using 'string_to_array' function and type casted to bigint.

I want to return those arrays in a table(out1 bigint, out2 bigint).

For example, foo_array and bar_array each contain 10 elements and I expect the function to return 10 rows with those elements. I am only able to produce output of 20 elements and don't really understand it.

CREATE OR REPLACE FUNCTION ___two_unnests()
RETURNS TABLE(out1 bigint, out2 bigint) AS $$
DECLARE
    foo_array text[];
    bar_array text[];

    foo1 text := array_to_string(ARRAY[1, 2, 3, 4, 5], ',');
    foo2 text := array_to_string(ARRAY[11, 22, 33, 44, 55], ',');

    bar1 text := array_to_string(ARRAY[6, 7, 8, 9, 10], ',');
    bar2 text := array_to_string(ARRAY[66, 77, 88, 99, 1010], ',');
BEGIN
    foo_array := (SELECT foo_array || foo1 || foo2);
    bar_array := (SELECT bar_array || bar1 || bar2);

    RAISE NOTICE 'foo_array: %', foo_array;
    RAISE NOTICE 'bar_array: %', bar_array;

    RETURN QUERY 
    SELECT 
      unnest(string_to_array(foo, ',')::bigint[]),
      unnest(string_to_array(bar, ',')::bigint[])
    FROM 
      unnest(foo_array) as foo,
      unnest(bar_array) as bar;
END;
$$ LANGUAGE plpgsql;


SELECT * FROM ___two_unnests();

Actual output of the function.

out1 |  out2
-----+-----
1    |  6
2    |  7
3    |  8
4    |  9
5    |  10
1    |  11
2    |  22
3    |  33
4    |  44
5    |  55
11   |  6
22   |  7
33   |  8
44   |  9
55   |  10
11   |  11
22   |  22
33   |  33
44   |  44
55   |  55

Output I want:

out1 |  out2
-----+-----
1    |  6
2    |  7
3    |  8
4    |  9
5    |  10
11   |  66
22   |  77
33   |  88
44   |  99
55   |  1010

SOLUTION Using sticky-bit's suggestion

CREATE OR REPLACE FUNCTION ___two_unnests() RETURNS TABLE(out1 bigint, out2 bigint) AS $$
DECLARE
    foo_array text[];
    bar_array text[];

    foo_slice text;
    foo_text text := '';
    foo_firstiter boolean := true;

    bar_slice text;
    bar_text text := '';
    bar_firstiter boolean := true;

    out1_array bigint[];
    out2_array bigint[];

    foo1 text := array_to_string(ARRAY[1, 2, 3, 4, 5], ',');
    foo2 text := array_to_string(ARRAY[11, 22, 33, 44, 55], ',');

    bar1 text := array_to_string(ARRAY[6, 7, 8, 9, 10], ',');
    bar2 text := array_to_string(ARRAY[66, 77, 88, 99, 1010], ',');
BEGIN
    foo_array := (SELECT foo_array || foo1 || foo2);
    bar_array := (SELECT bar_array || bar1 || bar2);

    RAISE NOTICE 'foo_array: %', foo_array;
    RAISE NOTICE 'bar_array: %', bar_array;

    FOREACH foo_slice IN ARRAY foo_array LOOP
        IF foo_firstiter = true THEN
            foo_text := foo_text || foo_slice;
            foo_firstiter := false;
        ELSE
            foo_text := foo_text || ',' || foo_slice;
        END IF;
    END LOOP;

    FOREACH bar_slice IN ARRAY bar_array LOOP
        IF bar_firstiter = true THEN
            bar_text := bar_text || bar_slice;
            bar_firstiter := false;
        ELSE
            bar_text := bar_text || ',' || bar_slice;
        END IF;
    END LOOP;


    out1_array := (SELECT string_to_array(foo_text, ',')::bigint[]);
    out2_array := (SELECT string_to_array(bar_text, ',')::bigint[]);

    RAISE NOTICE 'out1_array: %', out1_array;
    RAISE NOTICE 'out2_array: %', out2_array;


    RETURN QUERY SELECT un1.val::bigint,
            un2.val::bigint
       FROM unnest(out1_array) WITH ORDINALITY un1 (val, ord)
            FULL JOIN unnest(out2_array) WITH ORDINALITY un2 (val, ord)
                      ON un2.ord = un1.ord;

END;
$$ LANGUAGE plpgsql;


SELECT * FROM ___two_unnests();

score 6 · Answer 1 · answered May 29 '19 at 13:38

If you have a modern PostgreSQL, you can use multicolumn unnest function

SELECT * FROM unnest(ARRAY[1, 2, 3, 4, 5] || ARRAY[11, 22, 33, 44, 55],
                     ARRAY[6, 7, 8, 9, 10] || ARRAY[66, 77, 88, 99, 1010]);

┌────────┬────────┐
│ unnest │ unnest │
╞════════╪════════╡
│      1 │      6 │
│      2 │      7 │
│      3 │      8 │
│      4 │      9 │
│      5 │     10 │
│     11 │     66 │
│     22 │     77 │
│     33 │     88 │
│     44 │     99 │
│     55 │   1010 │
└────────┴────────┘

Don't use subselects instead a expressions

It is bad (the code is less readable and slower)

var := (SELECT a || b || c); -- don't do this!

instead

var := a || b || c;

score 1 · Accepted Answer · answered May 29 '19 at 10:47

1

You can unnest() WITH ORDINALITY, so that every element gets an index, and full join the results on that.

SELECT un1.val::bigint,
       un2.val::bigint
       FROM unnest(ARRAY[1, 2, 3, 4, 5, 11, 22, 33, 44, 55]) WITH ORDINALITY un1 (val, ord)
            FULL JOIN unnest(ARRAY[6, 7, 8, 9, 10, 66, 77, 88, 99, 1010]) WITH ORDINALITY un2 (val, ord)
                      ON un2.ord = un1.ord;

db<>fiddle

answered May 29 '19 at 10:47

sticky bit

36,626
12
31
42

This is more complicated and expensive than it needs to be. Consider Pavel's answer. – Erwin Brandstetter May 30 '19 at 22:54

How to properly unnest two arrays

2 Answers2