Generating JSON from SQL

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.

```sql
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.

```sql
insert into users values
```

```sql
table users;
```

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

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
insert into bookmarks(user_id, name, url) values
```

Let's try a nested JSON query.

```sql
select row_to_json(t)
from (
select id, name, email,
(
select json_agg(row_to_json(bookmarks))
from bookmarks
) 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.

```sql
select json_agg(row_to_json(t))
from (
select id, name, email,
(
select json_agg(row_to_json(bookmarks))
from bookmarks
) 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.

[nicedreammmm]

Nice tips here. But we can have simpler queries using json_build_array function (available in 9.4). Instead of row_to_json + subquery (as shown in the video), we have the same result with

select json_build_object('id', id, 'name', name, 'email', email)
from users
where id = 1

which is simpler to read in my opinion.

Congratulations on the screencasts!

[Jarrod_C]

now i want to know how to import JSON to Postgres