Multiple distinct arrays with null values among them

Question

I have a table with three one dimensional arrays (and also a primary key and several other columns, but they do not matter). All of these arrays could be NULL. The arrays do overlap quite often in each column: Many values are in several arrays. I now want a query which returns one row with three arrays being the distinct values of the whole array column.

Creating the test table like so

DROP TABLE IF EXISTS my_array_test;
CREATE TABLE IF NOT EXISTS my_array_test(id integer, my_txt text[], my_int1 integer[], my_int2 integer[]);
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (1,'{text1,text2}','{1,2}','{21,22}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (2,null,'{7,8}','{21,22}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (3,'{text2,text4}',null,null);
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (3,null,null,'{17,18}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (4,'{text1,text2}','{1,2,3}','{21,22}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (5,'{text1,text5}','{1,5}','{21,25}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (6,null,null,null);

The result being something like

select * from my_array_test ;
 id |    my_txt     | my_int1 | my_int2
----+---------------+---------+---------
  1 | {text1,text2} | {1,2}   | {21,22}
  2 |               | {7,8}   | {21,22}
  3 | {text2,text4} |         |
  3 |               |         | {17,18}
  4 | {text1,text2} | {1,2,3} | {21,22}
  5 | {text1,text5} | {1,5}   | {21,25}
  6 |               |         |
(7 rows)

The expected result would be {text1,text2,text4,text5},{1,2,7,8,2,5},{21,22,17,18,25} (order within the array is not important.)

What I tried was a multiple lateral query like so:

SELECT 
    array_agg(DISTINCT t) AS text_array_result,
    array_agg(DISTINCT i1) AS integer_array1_result,
    array_agg(DISTINCT i2) AS integer_array2_result 
FROM 
    my_array_test,
    unnest(my_txt) AS t,
    unnest(my_int1) AS i1,
    unnest(my_int2) AS i2

However, that kills all values which are only in rows with NULL arrays among them.

I also tried unnest(COALESCE(my_txt,'{}')) AS t, and so on, but to no avail.

Vao Tsun · Answer 1 · 2017-11-30T10:41:54.457

If I understand you right, you want all distinct values, but null? then you can just remove NULLs. definetely does not look neat, but:

t=# with u as (select unnest(my_txt) a,unnest(my_int1) b,unnest(my_int2) v from my_array_test)
select array_remove(array_agg(distinct a),NULL),array_remove(array_agg(distinct b),NULL),array_remove(array_agg(distinct v),NULL) from u;
       array_remove        | array_remove  |   array_remove
---------------------------+---------------+------------------
 {text1,text2,text4,text5} | {1,2,3,5,7,8} | {17,18,21,22,25}
(1 row)

for pre10 version:

t=# SELECT
    array_remove(array_agg(DISTINCT t),NULL) AS text_array_result,
    array_remove(array_agg(DISTINCT i1),NULL) AS integer_array1_result,
    array_remove(array_agg(DISTINCT i2),NULL) AS integer_array2_result
FROM
    my_array_test
    left outer join unnest(my_txt) AS t on true
    left outer join unnest(my_int1) AS i1 on true
    left outer join unnest(my_int2) AS i2 on true
;
     text_array_result     | integer_array1_result | integer_array2_result
---------------------------+-----------------------+-----------------------
 {text1,text2,text4,text5} | {1,2,3,5,7,8}         | {17,18,21,22,25}
(1 row)

Does that really work for you as you describe? For me, it doesn't. See fiddle: http://sqlfiddle.com/#!17/875ea/1 I get: "{text1,text2,text5}";"{1,2,3,5}";"{21,22,25}" - which is the same problem. Values within rows which contain NULL INSTEAD of an array are ignored. NULL values WITHIN an array would not matter. I think you tried to salve the latter phenomenon. — cis, Nov 30 '17 at 10:32
@cis frankly saying I'm suprised that inner join worked in my first example :) meanwhile http://sqlfiddle.com/#!17/875ea/4 works — Vao Tsun, Nov 30 '17 at 10:42

score 1 · Accepted Answer · answered Nov 30 '17 at 10:43

You can use my custom aggregate described in this post.

select
    array_merge_agg(my_txt) AS text_array_result,
    array_merge_agg(my_int1) AS integer_array1_result,
    array_merge_agg(my_int2) AS integer_array2_result 
from 
    my_array_test;

     text_array_result     | integer_array1_result | integer_array2_result 
---------------------------+-----------------------+-----------------------
 {text1,text2,text4,text5} | {1,2,3,5,7,8}         | {17,18,21,22,25}
(1 row)

Multiple distinct arrays with null values among them

2 Answers2