CODEWITHSUNDEEP
postgresqlelixirecto
AJP
AJP

Reputation: 545

Sum a field in a jsonb column with an Ecto query

Say I have a jsonb type column in a Postgres DB, called info. One of the fields is bytes, which is stored as an integer in the info field.

If I try and sum the values of the info => bytes field in an Ecto Query, as below:

total_bytes = Repo.one(from job in FilesTable,
  select: sum(fragment("info->>'bytes'")))

I get the error function sum(text) does not exist.

Is there a way to write the query above so that info => bytes can be summed, or would I have to just select that field from each row in the database, and then use Elixir to add up the values?

Upvotes: 0

Views: 868

Answers (1)

Dogbert
Dogbert

Reputation: 222398

The error message says that it can't sum a text field. You need to explicitly cast the field to an integer so that sum works.

Also, it's incorrect to hardcode a column name in a fragment. It only works in this case because you're selecting from only one table. If you had some join statements in there with other tables with the same column name, the query won't work. You can use ? in the string and then pass the column as argument.

Here's the final thing that should work:

sum(fragment("(?->>'bytes')::integer", job.info)))

Upvotes: 2

Related Questions