Postgres PLpgSQL JSON SUM

Question

I'm trying to calculate the sum of some JSON values in PLpgSQL (Postgres v9.5.5) but am stuck on the logic.

For this data set:

{
  clientOrderId: 'OR836374647',
  status: 'PENDING',
  clientId: '583b52ede4b1a3668ba0dfff',
  sharerId: '583b249417329b5b737ad3ee',
  buyerId: 'abcd12345678',
  buyerEmail: '[email protected]',
  lineItems: [{
    name: faker.commerce.productName(),
    description: faker.commerce.department(),
    category: 'test',
    sku: faker.random.alphaNumeric(),
    quantity: 3
    price: 40
    status: 'PENDING'
  }, {
    name: faker.commerce.productName(),
    description: faker.commerce.department(),
    category: 'test',
    sku: faker.random.alphaNumeric(),
    quantity: 2,
    price: 30,
    status: 'PENDING'
  }

I am trying to get the subtotal of all the lineItems for each row (i.e. quantity * price for each line item, then the sum of these values for the row). So for the above example, the returned value should be 180.

I got this far, but this is returning the totals for all lineItems in the table, not grouped by row.

WITH line_items AS (SELECT jsonb_array_elements(line_items) as line_items FROM public.order),
line_item_totals AS (SELECT line_items->>'quantity' AS quantity, line_items->>'price' AS price FROM line_items)

SELECT (quantity::int * price::numeric) AS sub_total FROM line_item_totals;

I'm sure the fix is simple but I'm not sure how to do this with JSON fields.

This is the JS object that I'm using to populate the table. The lineItems field is JSONB in the database. All other key/values are just columns in the table. — Seonixx
– Seonixx, Commented Nov 29, 2016 at 10:47

Dzmitry Savinkou · Accepted Answer · 2016-11-29 19:11:46Z

0

Please always include Postgres version you are using. It also looks like your JSON is incorrect. Below is an example of how you can accomplish this with json type and valid json document.

with t(v) as ( VALUES
  ('{
      "clientOrderId": "OR836374647",
      "status": "PENDING",
      "clientId": "583b52ede4b1a3668ba0dfff",
      "sharerId": "583b249417329b5b737ad3ee",
      "buyerId": "abcd12345678",
      "buyerEmail": "[email protected]",
      "lineItems": [{
          "name": "name1",
          "description": "desc1",
          "category": "test",
          "sku": "sku1",
          "quantity": 3,
          "price": 40,
          "status": "PENDING"
        },
        {
          "name": "name2",
          "description": "desc2",
          "category": "test",
          "sku": "sku2",
          "quantity": 2,
          "price": 30,
          "status": "PENDING"
        }]
      }'::JSON)
)
SELECT
  v->>'clientOrderId' cId,
  sum((item->>'price')::INTEGER * (item->>'quantity')::INTEGER) subtotal
FROM
  t,
  json_array_elements(v->'lineItems') item
GROUP BY cId;

Result:

     cid     | subtotal 
-------------+----------
 OR836374647 |      180
(1 row)

answered Nov 29, 2016 at 19:11

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Seonixx Over a year ago

Thanks. Sorry - have updated with version number. Currently on v9.5.5.

Seonixx Over a year ago

Also, I just put the JS object there as some sample data, not actual JSON from the DB.

Collectives™ on Stack Overflow

Postgres PLpgSQL JSON SUM

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related