Due to new design requirements, I needed to migrate a nested jsonb field from an integer to a float . Attempting this is pretty straightforward.

Query all of rows in the table Cast the value from integer to float Update the rows in the database

The update pseudo-code looks something like this:

query =

from(

r in "my_table",

where: fragment("?->>'number' ~ '^[0-9]+$'", r.meta),

select: r

) query

|> Repo.all()

|> Enum.map(&cast_field_to_float/1)

|> Repo.update_all()

Everything works great at this point. All of the rows update, but something unexpected happens. Most, but not all of the fields were cast to float. Numbers like 5 were properly converted to 5.0 , but 5000 remained 5000 . What the eff?

Let’s jump into the REPL and figure this out. What happens if we hardcode 5000.0?

iex>

meta = put_in(meta.number, 5000.0) model

|> Ecto.Changeset.cast(%{meta: meta}, [:meta])

|> Repo.update(force: true) iex> Repo.one(from(r in "my_table", select: r.meta, where: r.id == 1))

%{"number" => 5000}

At this point, I’m thinking that we’ve found a bug in Ecto, Postgrex, or maybe even Postgres itself. It appears that only numbers divisible by 10 will not cast. Hardcoding 5001.0 does seem to work, so maybe we’ve found an edge case?

Let’s go to Postgres directly to see if we can replicate.

outline_dev=# UPDATE my_table SET meta = '{"number": 5000.0}' where id = 1; outline_dev=# select meta from cards where id = 1;

meta

--------------------

{"number": 5000.0}

Directly using SQL gives me the behavior I’m looking for, so at this point I’m inclined to think that there’s a bug somewhere in Elixir land. After chatting with Michal Muskala in the Elixir Slack for a bit, we found the problem.

In order to convert from an Elixir Map to JSON, Ecto must encode the value. Here’s what a few different values look like after they’ve been encoded to JSON.

iex(6)> Jason.encode(%{number: 5.0})

{:ok, "{\"number\":5.0}"} iex(7)> Jason.encode(%{number: 5})

{:ok, "{\"number\":5}"} iex(8)> Jason.encode(%{number: 5000.0})

{:ok, "{\"number\":5.0e3}"}

Notice how 5000.0 is being encoded? According to RFC 8259, the JSON RFC, 5.0e3 is a valid encoding for 5000.0 . The RFC states how to parse this value, but it does not specify its storage.

Postgres makes the decision to store 5.0e3 as an integer value for a jsonb field, while Elixir chooses to store it as a float . Due to this mismatch, when we try to store this value as a float , Postgres casts it back into an integer .