How to use a fragment as part of update changeset attributes?

Question

I'm trying to update an existing model with new attributes and a database call interacting with the existing column. Something like UPDATE companies SET a = 1, b = jsonb_set(b, '{key}', CURRENT_TIMESTAMP(), true) in SQL.

How would I achieve this using Ecto and fragments?

This is my attempt, but it fails since fragment is a macro:

enhanced =
  %{ attrs | b: fragment(
    "jsonb_set(b, ?, CURRENT_TIMESTAMP(), true)",
    "{#{Atom.to_string(key)}}"
  )}
result =
  company
  |> Company.changeset(enhanced)
  |> Repo.update()

Answer 1

Using the fragment macro via Ecto.Query.update is the way to go. To do that, we will need to make the update part of the query expression.

{count, results} =
  Company
  |> where([c], <some_condition>)
  |> select([e], e) ## Optional: this will return our changes
  |> update(set: [
        string_field: "value",
        map_field: fragment(~S<jsonb_set("map_field"::jsonb, '{key_name_here}', 'value_here')>)
      ]
  |> Repo.update_all([])

The ~S<…> is used to avoid escaping the quotation marks " in our fragment.

Add variables to your fragment by replacing them in your string with a ? and adding them as a pinned ^ param.

fragment(~S<jsonb_set("map_field"::jsonb, '{?}', 'value_here')>, ^key)