Skip to content

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.

The problem

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.

The solution


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.
  • This can be particularly useful for destructuring the data in JavaScript.
  • Without this technique I might need two or more queries.

Anyway, I Hope you found this useful.

By