Get sum of a specific attribute in jsonb array in PostgreSQL

Question

I have a table named 'receipts' with those four values inside:

id              // serial
payments        // jsonb e.g. '[{'method':'card', 'amount':100.00},{'method':'cash', 'amount':150.00}]'
total_price     // decimal
status          // enum ['confirmed', 'pending']

I want a query which can update payments and compare the sum of payments with total_price. If they are equal, update status to 'confirmed', otherwise, update status to 'pending'. However, I cannot figure out a way to get the sum of payments' amount and compare to total_price.

I think the code will looks like that:

UPDATE
  receipts
SET
  payments = receipts.payments::jsonb || '[{"method":"cash","amount": 50.00}]'::jsonb,
  status = (
    CASE WHEN
      //here to get the sum of exist payments' amount + 50.00 = receipts.total
    THEN
      'confirmed'
    ELSE
      'pending'
    END
  )
WHERE
  id = 1
RETURNING
  id,
  payments,
  total_price,
  status

Vao Tsun · Accepted Answer · 2018-04-06 13:34:59Z

I created somewhat close to your structure:

t=# select * from so14;
 i |                                      j                                       | st
---+------------------------------------------------------------------------------+----
 1 | [{"amount": 30.00, "method": "card"}, {"amount": 11.00, "method": "cash"}]   |
 2 | [{"amount": 100.00, "method": "card"}, {"amount": 150.00, "method": "cash"}] |
(2 rows)

and here's aggregation example with somewhat close statement:

t=# with a as (select i,st,(jsonb_array_elements(j)->>'amount')::float f from so14)
, ag as (select i, st, sum(f) from a group by i, st)
update so14 set st = sum
from ag where ag.i = so14.i
returning so14.*;
 i |                                      j                                       | st
---+------------------------------------------------------------------------------+-----
 1 | [{"amount": 30.00, "method": "card"}, {"amount": 11.00, "method": "cash"}]   | 41
 2 | [{"amount": 100.00, "method": "card"}, {"amount": 150.00, "method": "cash"}] | 250
(2 rows)

UPDATE 2

Collectives™ on Stack Overflow

Get sum of a specific attribute in jsonb array in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related