Group rows into a JSON array

Sql

Returns one row per author with their posts nested, instead of a join that repeats the author on every row.

SELECT
  a.name,
  json_agg(json_build_object('id', p.id, 'title', p.title)
           ORDER BY p.published_at DESC) AS posts
FROM authors a
JOIN posts p ON p.author_id = a.id
GROUP BY a.id, a.name;

Download

Databases postgres json aggregation chris

More in Databases

Table sizes, largest first Databases Where the disk actually went, including indexes and TOAST. Delete duplicate rows, keeping the oldest Databases Uses a window function to number the duplicates and removes everything but the first of each group. Reset a Postgres sequence after a bulk import Databases Copying rows with explicit ids leaves the identity sequence behind, so the next insert collides with an existing row. Find slow queries in Postgres Databases Ranks statements by total time spent. Needs the pg_stat_statements extension enabled. Which indexes is nobody using? Databases Lists indexes that have never been scanned, which are pure write cost. Check against a long uptime before dropping any. Add a column without locking the table Databases On Postgres 11 and later a nullable column with a default is a metadata-only change. Adding NOT NULL afterwards is the part that needs care. Upsert a row in SQLite Databases Inserts, or updates the existing row when the unique column already holds that value.

Random picks

Tail a log and colour only what matters Shell Follows a growing file and highlights errors and warnings, leaving the rest readable but quiet. Parameterised tests in Python Testing One test body, many cases, and a failure that names the case that broke. Deep clone a value JavaScript structuredClone handles Dates, Maps, Sets and cycles, which JSON round-tripping quietly destroys. Table-driven tests in Go Testing The idiomatic Go pattern: a slice of cases and a subtest per entry, each named so failures are readable. Records and pattern matching in Java Systems A record plus a switch pattern removes most of the ceremony this used to take. Exhaustiveness check on a switch TypeScript Assigning to never makes the compiler fail the build when a new union member is added and left unhandled. Remove every stopped container and dangling image Containers Reclaims disk after a week of building. The volume flag is separate because it is the one that loses data. Merge dictionaries Python The | operator returns a new dict; |= updates in place. Later keys win in both.