Transcript

Generating JSON in PostgreSQL can be several times faster than copying the relational data to the application and then generating the JSON. This can be especially useful for APIs that return JSON.

Our example domain will be a simple bookmarking platform. We will start by creating a users table.

create table users(
  id integer primary key,
  email varchar not null,
  name varchar not null,
  password_digest varchar not null
);

Now let's add a few users.

insert into users values
  (1, 'john@example.com', 'John', '0123456789abcdef'),
  (2, 'jane@example.com', 'Jane', 'abcdef0123456789');

table users;

To get a user as JSON we use the row_to_json function.

select row_to_json(users)
from users
where id=1;

This works, but it returns all columns in the table. We would never want to expose a user's password digest.

We could try using a row constructor.

select row_to_json(row(id, name, email))
from users
where id=1;

This almost works, but the generic row type loses the names. If we use a subquery we can keep the names.

select row_to_json(t)
from (
  select id, name, email
  from users
  where id=1
) t;

For what it's worth, another solution is to create a composite type and cast the row to that type. However, the subquery approach has worked better for me.

Now let's look at create JSON documents with nested values. We'll start by creating a bookmarks table.

create table bookmarks(
  id serial primary key,
  user_id integer not null references users,
  name varchar not null,
  url varchar not null
);

Now we'll insert some sample bookmarks.

insert into bookmarks(user_id, name, url) values
  (1, 'Hashrocket', 'https://www.hashrocket.com'),
  (1, 'PostgreSQL Docs', 'http://www.postgresql.org/docs/current/static/index.html'),
  (2, 'Google', 'https://www.google.com'),
  (2, 'Stack Overflow', 'http://stackoverflow.com/'),
  (2, 'YouTube', 'https://www.youtube.com');

Let's try a nested JSON query.

select row_to_json(t)
from (
  select id, name, email,
  (
    select json_agg(row_to_json(bookmarks))
    from bookmarks
    where user_id=users.id
  ) as bookmarks
  from users
  where id=1
) t;

The only change is to use a subquery with the json_agg function to aggregate all the bookmarks for the user. json_agg aggregates json objects into a json array.

If we want to get all users and all their bookmarks we would simply remove the where clause and add json_agg to the outermost query.

select json_agg(row_to_json(t))
from (
  select id, name, email,
  (
    select json_agg(row_to_json(bookmarks))
    from bookmarks
    where user_id=users.id
  ) as bookmarks
  from users
) t;

It can be a little awkward to work with, especially at first, but generating JSON in PostgreSQL is a helpful feature when you need more performance.

Thanks for watching.