CODEWITHSUNDEEP
postgresqlsumjsonb
jamesamuir
jamesamuir

Reputation: 1457

Postgresql JSONB inline sum of array values

I am trying to figure out what the query syntax is for an inline sum for a specific field in an object array. My data is structured like this

CREATE TABLE "orders" (
  order_id int8,
  tax_lines jsonb
);
INSERT INTO "orders"(order_id, tax_lines) VALUES (4521745668, '[
  {
    "rate": 0.029,
    "price": "0.43",
    "title": "CO State Tax"
  },
  {
    "rate": 0.00985,
    "price": "0.15",
    "title": "Boulder County Tax"
  },
  {
    "rate": 0.0496,
    "price": "0.74",
    "title": "Boulder Municipal Tax"
  }
]');

The result i am trying to achieve is 

order_id         cumulative_tax_rate
4521745668       .08845

This is as far as i have gotten

SELECT
  o.order_id,
  SUM((jsonb_array_elements(o.tax_lines) ->> 'rate')::numeric) AS cumulative_tax_rate
 FROM orders o WHERE o.order_id = '4521745668'

but it keeps asking for a GROUP BY clause which I would like to avoid. I was wondering if it is possible to perform this aggregation at the row level without a group by clause and, if so, what that syntax might look like?

Thank you in advance.

Upvotes: 1

Views: 3231

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23381

Even though you said you want to avoid the group by I will provide a solution with it because it is what you indeed need.

select order_id, 
       sum(tax) tax
  from (SELECT o.order_id, 
               (jsonb_array_elements(o.tax_lines)->>'rate')::numeric tax
          FROM orders o) a
 where order_id = 4521745668 -- this you add if you want a specific order id
  group by order_id;         -- without it you will have all orders tax sum

This will give you the result you want:

  order_id      tax
 4521745668   0.08845

And if you need just add the where clause for that order_id in the outer query. where order_id = 4521745668 no need for quotes since it is a int8 value.

Upvotes: 5

Vao Tsun
Vao Tsun

Reputation: 51579

I aggregate with window, not group by - is it what you are looking for?..

t=# with a as (
SELECT
  o.order_id,
 (jsonb_array_elements(o.tax_lines ) ->> 'rate')::float AS cumulative_tax_rate
  FROM orders o WHERE order_id = 4521745668
)
select
distinct order_id, sum(cumulative_tax_rate) over (partition by order_id)
from a;
  order_id  |   sum
------------+---------
 4521745668 | 0.08845
(1 row)

Upvotes: 1

Related Questions