I am trying to unnest an array from one table using ORDINALITY to preserve order, then perform an INNER JOIN on another table to find the corresponding value from a specific column and then use ARRAY_AGG to package this back up and UPDATE the original table. I have something working for a single query, but I want to do an UPDATE on each row in a table but can’t seem to get it to work. I feel like I’m close but I’m spending too long on this so any help would be appreciated.
Code is below to generate the tables, alongside the answer I am looking for and my attempts.
create table table_1( table_1_id int, table_2_id_list int[], table_2_geom text[] ); insert into table_1 values (1, ARRAY[1,3,5], null) ,(2, ARRAY[2,4,6], null); create table table_2(table_2_id int, geom text); insert into table_2 values (1, 'geom1'), (2, 'geom2'), (3, 'geom3'), (4, 'geom4'), (5, 'geom5'), (6, 'geom6');
I want to end up with this:
table_1_id | table_2_id_list | table_2_geom ------------------------------------------------------------------ 1 | (1, 3, 5) | (geom1, geom3, geom5) 2 | (2, 4, 6) | (geom2, geom4, geom6)
I can get it working for the single case using the following:
SELECT TABLE_1_ID, array_agg(TABLE_2.geom ORDER BY ORDINALITY) FROM TABLE_1, unnest(table_2_id_list) WITH ORDINALITY a INNER JOIN TABLE_2 ON a = TABLE_2.TABLE_2_ID GROUP BY TABLE_1_ID LIMIT 1;
But when I try to do something similar to UPDATE each row in the table, I do something wrong. I’ve tried the following but it doesn’t work:
UPDATE TABLE_1 SET table_2_geom = ( SELECT array_agg(TABLE_2.geom ORDER BY ORDINALITY) FROM TABLE_1, unnest(table_2_id_list) WITH ORDINALITY a INNER JOIN TABLE_2 ON a = TABLE_2.TABLE_2_ID );
If anyone can point me in the correct direction I would be very grateful.
Thanks
Advertisement
Answer
You could turn your existing query to a CTE and join it back with the original table for update:
with cte as ( select t1.table_1_id, array_agg(t2.geom order by ordinality) table_2_geom from table_1 t1 cross join lateral unnest(t1.table_2_id_list) with ordinality i(table_2_id) inner join table_2 t2 on t2.table_2_id = i.table_2_id group by t1.table_1_id ) update table_1 t1 set table_2_geom = c.table_2_geom from cte c where c.table_1_id = t1.table_1_id
Demo on DB Fiddle – table content after the update
:
table_1_id | table_2_id_list | table_2_geom ---------: | :-------------- | :------------------ 1 | {1,3,5} | {geom1,geom3,geom5} 2 | {2,4,6} | {geom2,geom4,geom6}
But a correlated subquery might be simpler:
update table_1 t1 set table_2_geom = ( select array_agg(t2.geom order by ordinality) from unnest(t1.table_2_id_list) with ordinality i(table_2_id) inner join table_2 t2 on t2.table_2_id = i.table_2_id )