CODEWITHSUNDEEP
sqlpostgresql
DonSeba
DonSeba

Reputation: 2119

PostgreSQL: Join 2 arrays with maximum values

I have an table with 2 records ( illustrative purpose )

| id      | access (int array) |
| ---------------------------- |
| 1       | {0,1,0}            | 
| 2       | {1,0,0}            |

I'd like to select both values and merge them together
to get the highest value making the result in this case: 

{1,1,0}

The access array could grow to over 200 values and therefor i'd like to avoid to do it in the programming code, ( golang ).

Basically it is the same as this question : Merge two (or more) arrays and keep values that are highest? but in my case the seperated columns are combined into one field with an array.

UPDATES

  1. The row(access) array's always have the same amount of values

Upvotes: 3

Views: 208

Answers (2)

klin
klin

Reputation: 121634

Try this query (SqlFiddle):

select array_agg(elem)
from (
    select distinct on (row_number) row_number, elem 
    from (
        select id, elem, row_number() over (partition by id)
        from (
            select id, unnest(access) elem
            from merge_test
            where id in (1,2)
            ) sub
        ) sub
    order by 1, 2 desc
    ) sub;

Binary arrays

If the arrays contain only '0' and '1' and have the same number of values:

create table merge_binary_test (id int, access int[]);
insert into merge_binary_test values
(1, '{0,1,0,1}'),
(2, '{1,0,0,1}');

select string_to_array(bit_or(elem)::text, null)::int[]
from (
    select id, array_to_string(access, '')::varbit elem
    from merge_binary_test
    where id in (1,2)
    ) sub

 string_to_array
-----------------
 {1,1,0,1}
(1 row)

Upvotes: 1

Vivek S.
Vivek S.

Reputation: 21915

Consider following as an example:

create table arr(id int,access int[]);
insert into arr values (1,'{0,1,0}'),(2,'{0,1,0}'),(3,'{0,0,9}');

so that the rows will be:

id access  
-- ------- 
1  {0,1,0} 
2  {0,1,0} 
3  {0,0,9}

Now a function can be created to merge array rows like below:

CREATE
    OR replace FUNCTION merge_array (
    arrval INT -- the total number of values in an array in my example its 3, but 200 is for yours
    ,tbl TEXT --name of the table in which you want perform this function,
    ,col TEXT -- name of the array col
    )
RETURNS setof INTEGER [] AS $$

DECLARE qry TEXT;

BEGIN
    SELECT format('select translate(string_to_array(x.*::text,'','')::text,''()'','''')::int[] from (select %s  from (select ' || col || ' ar  from  ' || tbl || ' ) as sq) as x ', egn)
    INTO qry
    FROM (
        SELECT string_agg('max(ar[' || val || '])', ',') egn
        FROM (
            SELECT generate_series(1, arrval) val
            ) t
        ) tt;

    RETURN QUERY

    EXECUTE (qry);
END;$$

LANGUAGE plpgsql

Usage : select merge_array(3,'arr','access') Result:

merge_array 
----------- 
{0,1,9}

Upvotes: 1

Related Questions