Postgres - json build object aggregates
Sorry for not posting recently. I Fell out with this blog for some time now, often tinkering with mentions or just trying to get some time after work not writing.
Todays offering is a sort of "recently I learned". Perhaps I have used this in the past, but recently I at least rediscovered the wonder of combining SQL with JSON functions.
I wanted to get some aggregate data at work. But I also wanted to know about specific records affected. Further to this, I would like to get more than one field.
I am a data pedant, which also works to talk about me if you remove the word data. If someone shows me aggregates without access to source data, I find it hard to trust.
SELECT COUNT(id) AS "line_items", SUM(amount) AS "sub_total" json_agg(json_build_object('sku', sku, 'quantity', qty, 'price', amount)) AS "line_item_data" FROM order_line_items WHERE order_id = ? GROUP BY (order_id, amount)
At work I was not dealing with line items, but checking out customer order histories.
Things to note
- This might not be optimal for extracting graphs and charts, or displaying tabular data.
- json_build_object takes key, value pairs, so should always have an even number of entries.
- Without a group by clause, there would be no point in me combining with json_agg.
- You can use array_agg instead of json_agg, but each item will become a string.
- Without this technique I might need two or more queries.
Anyway, I Hope you found this useful.