How to add values for jsonb in postgres?

Questions & Help Questions
1

hi,

I have a table that has a jsonb column and I need to add all the values inside the jsonb. So suppose if something like this is there

{"a": 1, "b": 2} I need to add those values.

I’m not able to find any postgres function which can do that. What should I do?

2

You can use embeded_schema to define your jsonb column, and then use embeds_one do link it to the column. Then you can work with it like that. Ecto will take care of translating your embeded schema into JSON for you.

3

I need to do this when I’m querying the data. I don’t think it can help me.

Suppose if I’m doing something like this

from t in my_table, select: sum(t.totals)

I need to sum all those values

4

So, when you say add values you mean a sum of the values?
If thats the case you could do something ugly like this, but there is probably a cleaner way to do this.

with json_data as ( select '{"a": 1, "b": 2}'::jsonb) 
select sum(value::float) from json_data d cross join lateral jsonb_each_text(d.jsonb)

EDIT: a bit simpler query, looks like the lateral join is not needed

with json_data as ( select '{"a": 1, "b": 2}'::jsonb) 
select sum(value::float) from json_data d cross join jsonb_each_text(d.jsonb);
5

Is there a way to write this in more elixir way? Can we use “with” in Ecto.Query

6

Hi Maxtonx! I didn’t have enough time to check if this will work, but it should be close to what you want or at least give you an idea how to do it in Ecto:

MyTable
|> with_cte("json_rows", as: fragment("SELECT id, j.key, j.value FROM my_table, jsonb_each(my_table) j"))
|> join(:inner, [t], "json_rows", on: [id: t.id], as: :kv)
|> group_by(:id)
|> select([t, kv: kv], %{id: t.id, value_sum: sum(kv.value)})
7

Its giving me this ** (Postgrex.Error) ERROR 42883 (undefined_function) function sum(jsonb) does not exist

8

Ah right, we need to cast to :decimal but unless all the values are guaranteed to be integers/floats we have to filter them out with a where-clause (or else Postgrex will throw an exception).

MyTable
|> with_cte("json_rows", as: fragment("SELECT id, j.key, j.value FROM my_table, jsonb_each(my_table.json_field) j"))
|> join(:inner, [t], "json_rows", on: [id: t.id], as: :kv)
|> group_by(:id)
|> where([kv: kv], fragment("json_typeof(?)", kv.value) == "number")
|> select([t, kv: kv], %{id: t.id, value_sum: sum(kv.value |> type(:decimal))})

If you’re sure that the values are always integers or null you can simply cast the value column in the with-statement:
fragment("SELECT id, j.key, j.value::decimal FROM my_table, jsonb_each(my_table.json_field) j")

Still not sure if this is going to work, but let’s continue trying. :+1:

9

Yeah. This also not working


** (Postgrex.Error) ERROR 42846 (cannot_coerce) cannot cast type jsonb to numeric

although I have found something similar here which I’m trying to figure out

10

I think older versions of Postgres don’t support casting directly from jsonb to an SQL type. Which version do you have? The link to SO is interesting. Looks like it has a good solution.

11

Yeah, I have an older version.

12

I think equivalent of this query can solve my issue

select id, json_object_agg(key, val)
    from (
        select id, key, sum(value::numeric) val
        from mytable t, jsonb_each_text(star_pu)
        group by id, key
        ) s
    group by id;
13

The following could be the solution, but one thing that bugs me is the implicit cross join in the from-clause: from mytable t, jsonb_each_text(star_pu). I think it combines each json key-value row with every mytable row, and not just its “own” row, doesn’t it? I may be wrong so the Ecto query below should be tested properly. A cross-join is needed in the subquery because Ecto doesn’t allow multiple tables in the from-clause.

sum_query =
  MyTable
  |> join(:cross, [], fragment("jsonb_each_text(json_field)"), as: :kv)
  |> group_by([:id, "key"])
  |> select([t, kv: kv], %{id: t.id, key: kv.key, value: sum(kv.value |> type(:decimal))})

from(subquery(sum_query))
|> group_by(:id)
|> select([sum], %{id: sum.id, json: fragment("json_object_agg(?, ?)", sum.key, sum.value)})

PS: See Join Types in Postgres.

-- Old SQL join syntax...:
SELECT * FROM T1, T2
-- ...is equivalent to:
SELECT * FROM T1 CROSS JOIN T2
SELECT * FROM T1 JOIN T2 ON TRUE
SELECT * FROM T1 INNER JOIN T2 ON TRUE