Postgres json aggregate functions are pretty cool

In my current project at work, we have a TypeScript backend that is backed by a Postgrses database. Our usual approach would be to load the data from Postgres and then transform it into the desired shape for our api with Typescript. We did some experimentation and realised that we could actually use Postgres for many of the transformations itself which I thought was quite cool so I will demonstrate how this can be done in this post.

Imagine we have a set of users (with an additional age column) that can be in different groups (n:m). Our data could look like this:

user_name	age	group_name
User 2	32	Group 2
User 1	21	Group 2
User 2	32	Group 1
User 3	43	Group 1
User 1	21	Group 1

Our goal is to transform this for our api to return this data keyed by the group_name, with each group having a collection of user objects. For the table above, it would look like this:

{
  "Group 2": [
    {
      "age": 32,
      "name": "User 2"
    },
    {
      "age": 21,
      "name": "User 1"
    }
  ],
  "Group 1": [
    {
      "age": 32,
      "name": "User 2"
    },
    {
      "age": 21,
      "name": "User 1"
    },
    {
      "age": 43,
      "name": "User 3"
    }
  ]
}

We can create the data that is used for this example like this with a fresh Postgres:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS user_groups; 
CREATE TEMP TABLE user_groups (id uuid default uuid_generate_v4(), name text);

DROP TABLE IF EXISTS users; 
CREATE TEMP TABLE users (id uuid default uuid_generate_v4(), name text, age INT);

DROP TABLE IF EXISTS user_group_map; 
CREATE TEMP TABLE user_group_map (user_id uuid, group_id uuid);

delete from users;
insert into users (name, age) values 
('User 1', 21), 
('User 2', 32), 
('User 3', 43), 
('User 4', 54), 
('User 5', 65);

DELETE FROM user_groups;
INSERT into user_groups (NAME) values ('Group 1'), ('Group 2');

DELETE from user_group_map;
INSERT INTO user_group_map (user_id, group_id) values 
((select id from users limit 1 OFFSET 0), (select id from user_groups limit 1 OFFSET 0)),
((select id from users limit 1 OFFSET 1), (select id from user_groups limit 1 OFFSET 0)),
((select id from users limit 1 OFFSET 2), (select id from user_groups limit 1 OFFSET 0)),
((select id from users limit 1 OFFSET 0), (select id from user_groups limit 1 OFFSET 1)),
((select id from users limit 1 OFFSET 1), (select id from user_groups limit 1 OFFSET 1));

Let’s see how we can build the query that is required for our expected result step by step.

To start, the table that I showed above was created like this:

select users.name, users.age, user_groups.name from users 
join user_group_map on user_group_map.user_id = users.id
join user_groups on user_group_map.group_id = user_groups.id

This gives us:

user_name	age	group_name
User 2	32	Group 2
User 1	21	Group 2
User 2	32	Group 1
User 3	43	Group 1
User 1	21	Group 1

We can start by combining the user related data into one column like this:

select user_groups.name as group_name, to_jsonb(users.*) as user_data from users 
join user_group_map on user_group_map.user_id = users.id
join user_groups on user_group_map.group_id = user_groups.id

This gives us:

group_name	user_data
Group 2	{"id": "8f60793b-1849-4ba7-809f-b4ee3df5402b", "age": 32, "name": "User 2"}
Group 2	{"id": "c5b5d707-8556-4563-816f-e51e8e1eb5cf", "age": 21, "name": "User 1"}
Group 1	{"id": "8f60793b-1849-4ba7-809f-b4ee3df5402b", "age": 32, "name": "User 2"}
Group 1	{"id": "c5b5d707-8556-4563-816f-e51e8e1eb5cf", "age": 21, "name": "User 1"}
Group 1	{"id": "ccf85355-27e8-4002-8562-72a3b285bcec", "age": 43, "name": "User 3"}

For our api we aren’t actually interested in the id column though so we can drop it like this (using the fact that this is now a jsonb column):

select user_groups.name as group_name, to_jsonb(users.*) - 'id' as user_data from users 
join user_group_map on user_group_map.user_id = users.id
join user_groups on user_group_map.group_id = user_groups.id;

This gives us:

group_name	user_data
Group 2	{"age": 32, "name": "User 2"}
Group 2	{"age": 21, "name": "User 1"}
Group 1	{"age": 32, "name": "User 2"}
Group 1	{"age": 21, "name": "User 1"}
Group 1	{"age": 43, "name": "User 3"}

Now, all of this data is still split into multiple lines, lets group it by group_name using the json_agg function:

select user_groups.name as group_name, json_agg(to_jsonb(users.*) - 'id') as user_data from users 
join user_group_map on user_group_map.user_id = users.id
join user_groups on user_group_map.group_id = user_groups.id
group by group_name;

This gives us the following table:

group_name	user_data
Group 2	[{"age": 32, "name": "User 2"}, {"age": 21, "name": "User 1"}]
Group 1	[{"age": 32, "name": "User 2"}, {"age": 21, "name": "User 1"}, {"age": 43, "name": "User 3"}]

As a final step, we can use Postgres’ json_object_agg function to now merge the data into our expected output. To do so, we first wrap our old query in a with clause and then select from there:

with groups_with_users as (
select user_groups.name as group_name, json_agg(to_jsonb(users.*) - 'id') as user_data from users 
join user_group_map on user_group_map.user_id = users.id
join user_groups on user_group_map.group_id = user_groups.id
group by group_name)

select json_object_agg(group_name, groups_with_users.user_data) from groups_with_users;

{
  "Group 2": [
    {
      "age": 32,
      "name": "User 2"
    },
    {
      "age": 21,
      "name": "User 1"
    }
  ],
  "Group 1": [
    {
      "age": 32,
      "name": "User 2"
    },
    {
      "age": 21,
      "name": "User 1"
    },
    {
      "age": 43,
      "name": "User 3"
    }
  ]
}

The Postgres documentation contains much more information about all of the different JSON aggregate functions so make sure to check that out to learn more.

I think that this is pretty cool as it allows us to get the data from the database exactly as we want it instead of mixing the select/transform between Postgres and TypeScript. In our project, we actually decided to not make use of this too much though as this is probably quite difficult to understand for people that have not worked much with Postgres before and we did not want to scare away new developers. I hope that by spreading this knowledge we can use this more in production eventually though.

If you want to follow along, I also created a Franchise notebook that you can use to play around with the examples here.