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.
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.
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)