Postgresql update record column based on it's JSONB field values sum

Question

I have a table in Postgresql 9.6.5 with some fields like:

CREATE TABLE test (
  id SERIAL,
  data JSONB,
  amount DOUBLE PRECESION,
  PRIMARY KEY(id)
);

In data column there are json objects like this:

{
"Type": 1,
  "CheckClose":
    {"Payments":
      [
        {"Type": 4, "Amount": 2068.07},
        {"Type": 1, "Amount": 1421.07}
      ]
    }
}

What i need to do is tu put into amount field of each row the SUM of Amount values of Payments field od this data object. For example, for this particular object there should be 2068.07 + 1421.07 = 3489.14. I've read some stuff about Postgres json and jsonb functions, so here where i am now:

UPDATE test SET amount=sum((jsonb_array_elements(data::jsonb->'CheckClose'->'Payments')->>'Amount')::FLOAT)

That's not working - i get an error about not using agregate functions in UPDATE.

I tried to do this something like this:

UPDATE test SET amount=temp.sum
  FROM (
     SELECT sum((jsonb_array_elements(data::jsonb->'CheckClose'->'Payments')->>'Amount')::FLOAT) AS "sum"
    FROM test WHERE id=test.id
  ) as "temp"

Now i'm getting an error set-valued function called in context that cannot accept a set

How should i do this? I just need to calculate sum and put it into another row, is that such a hard task? Please, anyone, help me to figure this out. Thanks.

Answer 1

the set returning fn() aggregation try:

t=# with c(j) as (values('{"Payments":
      [
        {"Type": 4, "Amount": 2068.07},
        {"Type": 1, "Amount": 1421.07}
      ]
    }'::jsonb))
select sum((jsonb_array_elements(j->'Payments')->>'Amount')::float) from c;

error:

ERROR:  aggregate function calls cannot contain set-returning function calls
LINE 7: select sum((jsonb_array_elements(j->'Payments')->>'Amount'):...
                    ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

can easily be overcame by another cte:

t=# with c(j) as (values('{"Payments":
      [
        {"Type": 4, "Amount": 2068.07},
        {"Type": 1, "Amount": 1421.07}
      ]
    }'::jsonb))
, a as (select (jsonb_array_elements(j->'Payments')->>'Amount')::float am from c)
select sum(am) from a;
   sum
---------
 3489.14
(1 row)

so now just update from CTE:

with s as (SELECT ((jsonb_array_elements(data::jsonb->'CheckClose'->'Payments')->>'Amount')::FLOAT) AS "sm", id
    FROM test
)
, a as (select sum(sm), id from s group by id)
UPDATE test SET amount = sum
FROM a
WHERE id=test.id