SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins

Photo by Thomas Habr on Unsplash

I was working on a simple API last week when I needed to code a SQL query to make a simple 1-to-many join and I discovered the jsonb_agg function of PostgreSQL. And let me tell you, I use it all the time now!

Let’s take an example: a TODO list app. Each user has multiple todo lists and each todo list contains multiple todo items.

I want to create a GET /todo_lists API endpoint to fetch all the todo lists of a specific user and their associated todos. It should output something like this:

To do so, I can use a classic join like this:

And it will result in:

And then I need to write some code to group the todos in each of their list.

Using JSONB_AGG

You can also use the JSONB_AGG function to let PostgreSQL do the grouping for you!

And it will output:

I then return that to my API client, no other changes needed!

I also used the — 'todo_list_id' here to remove a field from the JSON output.

What other small SQL tips are you using currently?

Comments on HackerNews