Seonixx Seonixx - 7 days ago 6
JSON Question

Postgres PLpgSQL JSON SUM

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: 'test@test.com',
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.

Answer

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": "test@test.com",
      "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)