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_itemsitem_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?
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.
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
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;