arrayspostgresqljoin

Need to update row at Postgres table with join by array


my table schema looks this way:

create table table_with_arrays
(
    dim_col_code_array integer[] -- contains foreign keys to dict_table.array_code
    dim_col_val_array  varchar[] -- needs to be populated with dict_table.array_value
);

create table dict_table(
   array_code integer,
   array_value varchar
)

there is the field dim_col_code_array with values like [10,300,400]. these integers are foreign keys to dict_table.array_code.

For each row of table_with_arrays I need to join each value of dim_col_code_array with table dict_table by each element of dim_col_code_array = integer.array_code And then store joined array_value in the same order to table_with_arrays.dim_col_val_array

Example:

table_with_arrays contains record:
[1,2,3], [/** fill me with values*/]

dict_table contains records
1 FIRST
2 SECOND
3 THIRD

query should update record of table_with_arrays to
[1,2,3], [FIRST, SECOND, THIRD]

is there any "smart" ideas / insights that I could try?


Solution

  • The join can be done using the operator ANY :

    SELECT dim_col_code_array, array_agg(array_value ORDER BY array_code) as dim_col_val_array  
    FROM table_with_arrays t
    JOIN dict_table d ON d.array_code = ANY(t.dim_col_code_array)
    GROUP BY dim_col_code_array
    

    The update can be :

    UPDATE table_with_arrays t
    SET dim_col_val_array = sub_q.dim_col_val_array 
    FROM 
        (
        SELECT dim_col_code_array, array_agg(array_value ORDER BY array_code) as dim_col_val_array  
        FROM table_with_arrays t
        JOIN dict_table d ON d.array_code = ANY(t.dim_col_code_array)
        GROUP BY dim_col_code_array
        ) AS sub_q
    WHERE sub_q.dim_col_code_array = t.dim_col_code_array;
    

    Demo here