Ecto embedded schema migration

I have a schema that contains an embedded schema, which I would like to rename a field on. For example:

schema "menu_items" do
    field(:plu, :string)
    # ...
    embeds_many :build_items, BuildItem, on_replace: :delete do
        field(:item_id, :string)
        # ...
    end
end

I want to rename the build_items item_id field to value. Is there a way to do this with an Ecto migration or will I need to create the new field, copy the data over, then delete the old field?

Any pointers are very much appreciated.

Thanks,
Ben

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

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. :slight_smile:

Thanks!

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 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;
1 Like