How to speed up a bulk Repo.update? Is there a way at all?

I have a table: User:

_id: "123"
file: "345"
data_info: %{
   data_file: "567"
}

My goal is to transfer/migrate file field’s value into the nested field data_file which exists in data_info map.

I am running a kind of idiomatic code below and it works fine:

  users =
      User
      |> Repo.all()

    users
    |> Enum.each(fn user ->
      changeset =
        Banner.changeset(user, %{
          data_info: %{data_file: user.file}
        })

      Repo.update(changeset)
    end)

the problem is that it takes more than 15 mins to go through all existing records in the table.

And my question basically: is there any other way I can speed up that process somehow?

Thank you all so much for any possible help! I really appreciate any help here!

You could use update_all.
I’m not sure about the syntax (and the field names are not right) but as a starting point you could try this
from u in User |> Repo.update_all(set: [data_info: u.file])

Yeah, I was thinking the same way, but it will not update file field inside data_info map. It will update it this way;

"data_info" : "87ed24b0-201b-4a1d-8c45-f140553c95da"

I just couldn’t figure out how to specify a properly nested map to update in set… ?!

Thanks for your help!

Does set allow you to pass a map to it? [data_info: %{ data_file: u.file}]

No, it doesn’t! And that is where I am scratching my head to figure it out…

If you are on postgres, you might be able to use one of json functions like jsonb_set in a fragment.

User
|> update([u], set: [data_info: fragment("jsonb_set(?, '{data_file}', ?)", u.data_info, u.file)])
|> Repo.update_all([])

or (not sure about this one)

User
|> update([u], set: [data_info: fragment("? || ?::jsonb", u.data_info, %{data_file: u.file})])
|> Repo.update_all([])
2 Likes

Thanks a lot! I really appreciate it!
Yes, I am on Postgres.
I’ve tried that approach, and got this:

10:28:09.217 [debug] QUERY ERROR source="users" db=0.0ms queue=121.8ms idle=773.7ms
UPDATE "users" AS b0 SET "data_info" = jsonb_set(b0."data_info", '{data_file}', b0."file") []
** (Postgrex.Error) ERROR 42883 (undefined_function) function jsonb_set(json, unknown, character varying) does not exist

    query: UPDATE "users" AS b0 SET "data_info" = jsonb_set(b0."data_file", '{file}', b0."file")

    hint: No function matches the given name and argument types. You might need to add explicit type casts.
    (ecto_sql) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql) lib/ecto/adapters/sql.ex:526: Ecto.Adapters.SQL.execute/5

I am sorry, but I am not very familiar with SQL commands. Appreciate any help here!

The docs for jsonb_set list the third argument’s type as jsonb but the error says it’s receiving character varying, try:

jsonb_set(b0."data_info", '{data_file}', to_jsonb(b0."file"))