Ecto embedded schema migration

Old topic, but still useful. The custom query can be simplified a little:

    UPDATE menu_items t1 SET build_items = (
      SELECT json_agg(el::jsonb - 'item_id' || jsonb_build_object('value', el -> 'item_id')) 
      FROM menu_items t2, jsonb_array_elements(t2.build_items) AS el
      WHERE t1.id = t2.id
    );  

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;

Actually, the stackowerflow answer from above won’t work since you seem to have an array of jsons, not just one json object … So I think I’ll keep my handle for now.

But yeah, I would still do it “manually” with an SQL statement, albeit with a different one.

I didn’t think about writing the SQL manually, that’s a great idea! Maybe you should relinquish your handle and give it to me.

Thanks!

You could probably add an sql statement to execute which would atomically move your json fields around during an up migration. Don’t forget to include a down migration which would try and reverse that.

Adapted from https://stackoverflow.com/questions/42308764/postgresql-rename-attribute-in-jsonb-field

def SomeMigration do
  use Ecto.Migration

  def up do
    execute("""
    update menu_items
      set build_item = build_item - 'item_id' || jsonb_build_object('value', js->'item_id');
    """)
  end
  
  def down do
      # ... do the opposite
  end
end