Ecto embedded schema migration

Questions & Help Questions
,
5

I posted this question on StackOverflow and here’s the solution that I ended up with. Thanks for pointing me in the right direction. I think I probably would have wasted another day’s worth of time before I even considered writing the SQL directly. Ecto’s really spoiled me. I used to write all my SQL queries/commands and now I rarely need to!

UPDATE menu_items
SET build_items = t.newValue
FROM (WITH temp AS (SELECT id, UNNEST(build_items) b FROM menu_items)
    SELECT
      id,
      array_agg(b - 'item_id' || jsonb_build_object('value', coalesce(b -> 'item_id', b -> 'value'))) AS newValue
    FROM temp
    GROUP BY id) AS t
WHERE menu_items.id = t.id;