Postgresql JSONB inline sum of array values

Question

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.

indeed it was, thank you for pointing that out. Edited and fixed. — jamesamuir
– jamesamuir, Commented Apr 29, 2017 at 15:06
shopify_order_id and jsondata aren't in your orders table — Oto Shavadze
– Oto Shavadze, Commented Apr 29, 2017 at 15:06
you need aggregation, not for that one row, but for three array elements in json. check my answer — Vao Tsun
– Vao Tsun, Commented Apr 29, 2017 at 15:14

Jorge Campos · Accepted Answer · 2017-04-29 15:17:54Z

5

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.

answered Apr 29, 2017 at 15:17

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

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

2 Comments

foureyedraven Over a year ago

How would you generate all rows' tax and order_id without summing them all up?

Jorge Campos Over a year ago

@foureyedraven well, just use the subquery instead.

Vao Tsun · Accepted Answer · 2017-04-29 15:12:03Z

1

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)

answered Apr 29, 2017 at 15:12

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgresql JSONB inline sum of array values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related