Need to update row at Postgres table with join by array

Question

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?

Answer 1

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