Postgres GROUPBY on data coming from JSON-CodePudding

Given the table orders containing two columns:

| order_id | json |
| ---------| ---- |
| order_1 | {...} |
| order_2 | {...} |
| order_3 | {...} |

I want to end up with a table having one row for each order and the associated total number of items bought, which can be computed using the json.

Json structure (example for order_1):

{
"id": order_1, 
"line_items": 
    [
    {"id": product_1, "quantity": 1}, 
    {"id": product_2, "quantity": 2}
    ]
}

Desired output:

| id | quantity |
| -- | -------- |
| order_1 | 3 |
| order_2 | 1 |
| order_3 | 2 |

This is the code written so far:

SELECT 
order_id,
json_array_elements_text(raw_data::json#>'{line_items}')::json->>'quantity' as quantity_lines
FROM orders

which outputs the following table:

| order_id | quantity_lines |
| -------- | -------------- |
| order_1 | 1 |
| order_1 | 2 |
| order_2 | 1 |
| order_3 | 1 |
| order_3 | 1 |

I'm missing the last step of aggregating on the order_id. I've tried a traditional GROUP BY:

SELECT 
order_id,
sum((json_array_elements_text(raw_data::json#>'{line_items}')::json->>'quantity')::int) as quantity_lines
FROM orders
GROUP BY order_id

but it yelds the following error:

postgresql error: aggregate function calls cannot contain set-returning function calls

CodePudding user response：

almost close. Let's wrap it with CTE.

with a as (SELECT
order_id,
json_array_elements_text(raw_data::json#>'{line_items}')::json->>'quantity' as quantity_lines
FROM orders)
select a.orderid, sum(quantity_lines) from a group by 1 order by 2;