Returning related rows in a single SQL query using JSON

When building database-backed applications you’ll often find yourself wanting to return a row from the database along with its related rows.

A few examples:

Retrieving a list of congressional legislators and their terms, following a foreign key relationship
Return blog entries and their tags in one go, via a many-to-many table

You can do this in SQLite using the json_group_array() aggregation function. A couple of examples.

Legislators and their terms, via a foreign key#

Simplified schema for this database:

1
CREATE TABLE [legislators] (
2
   [id] TEXT PRIMARY KEY,
3
   [name] TEXT,
4
   [bio_birthday] TEXT
5
);
6
CREATE TABLE [legislator_terms] (
7
   [legislator_id] TEXT REFERENCES [legislators]([id]),
8
   [type] TEXT,
9
   [state] TEXT,
10
   [start] TEXT,
11
   [end] TEXT,
12
   [party] TEXT
13
);

Here’s a query that returns each legislator along with a JSON array of their terms:

1
select
2
  legislators.id,
3
  legislators.name,
4
  json_group_array(json_object(
5
    'type', legislator_terms.type,
6
    'state', legislator_terms.state,
7
    'start', legislator_terms.start,
8
    'end', legislator_terms.end,
9
    'party', legislator_terms.party
10
   )) as terms,
11
   count(*) as num_terms
12
from
13
  legislators join legislator_terms on legislator_terms.legislator_id = legislators.id
14
  group by legislators.id
15
order by
16
  id
17
limit
18
  10

And the result:

Screenshot of a query result. There is a terms column containing a JSON list of terms.

Note that this query does group by legislators.id which is allowed in SQLite but may not work in other databases, which might require group by legislators.id, legislators.name instead.

Tags on blog entries, via a many-to-many table#

Simplified schema:

1
CREATE TABLE [blog_entry] (
2
   [id] INTEGER PRIMARY KEY,
3
   [title] TEXT
4
);
5

6
CREATE TABLE [blog_tag] (
7
   [id] INTEGER PRIMARY KEY,
8
   [tag] TEXT
9
);
10

11
CREATE TABLE [blog_entry_tags] (
12
   [id] INTEGER PRIMARY KEY,
13
   [entry_id] INTEGER,
14
   [tag_id] INTEGER,
15
   FOREIGN KEY([entry_id]) REFERENCES [blog_entry]([id]),
16
   FOREIGN KEY([tag_id]) REFERENCES [blog_tag]([id])
17
);

Query to retrieve entries with their tags:

1
select
2
  blog_entry.id,
3
  blog_entry.title,
4
  json_group_array(json_object('tag', blog_tag.tag)) as tags
5
from
6
  blog_entry
7
  join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
8
  join blog_tag on blog_tag.id = blog_entry_tags.tag_id
9
group by
10
  blog_entry.id
11
order by
12
  blog_entry.id desc

Result:

id	title	tags
8191	I don’t know how to solve prompt injection	[{“tag”:“ai”},{“tag”:“security”},{“tag”:“openai”}]
8190	Weeknotes: Datasette Lite, s3-credentials, shot-scraper, datasette-edit-templates and more	[{“tag”:“shotscraper”},{“tag”:“datasette”},{“tag”:“plugins”},{“tag”:“datasettelite”},{“tag”:“projects”},{“tag”:“s3credentials”},{“tag”:“weeknotes”}]
8189	Prompt injection attacks against GPT-3	[{“tag”:“ai”},{“tag”:“gpt3”},{“tag”:“security”},{“tag”:“openai”}]

There’s a subtle bug in the above: if an entry has no tags at all it will be excluded from the query results entirely.

You can fix that using left joins like this:

1
select
2
  blog_entry.id,
3
  blog_entry.title,
4
  json_group_array(json_object('tag', blog_tag.tag)) as tags
5
from
6
  blog_entry
7
  left join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
8
  left join blog_tag on blog_tag.id = blog_entry_tags.tag_id
9
where blog_entry.id < 4
10
group by
11
  blog_entry.id
12
order by
13
  blog_entry.id desc

This almost works, but it outputs the following returning {"tag": null} for entries with no tags:

id	title	tags
3	Todo list	[{“tag”}]
2	Blogging aint easy	[{“tag”}]
1	WaSP Phase II	[{“tag”}]

David Fetter showed me the solution:

1
select
2
  blog_entry.id,
3
  blog_entry.title,
4
  json_group_array(
5
    json_object('tag', blog_tag.tag)
6
  ) filter (
7
    where
8
      blog_tag.tag is not null
9
  ) as tags
10
from
11
  blog_entry
12
  left join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
13
  left join blog_tag on blog_tag.id = blog_entry_tags.tag_id
14
group by
15
  blog_entry.id
16
order by
17
  blog_entry.id

That extra filter on the aggregation does the trick!

Other databases#

Other databases are capable of the same thing, but using different functions. PostgreSQL has json_agg() for example, which is also available in Django as JSONBAgg.

Here’s an equivalent query in PostgreSQL syntax:

1
select
2
  blog_entry.id,
3
  title,
4
  slug,
5
  created,
6
  coalesce(json_agg(json_build_object(blog_tag.id, blog_tag.tag)) filter (
7
    where
8
      blog_tag.tag is not null
9
  ), json_build_array()) as tags
10
from
11
  blog_entry
12
  left join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
13
  left join blog_tag on blog_entry_tags.tag_id = blog_tag.id
14
group by
15
  blog_entry.id
16
order by
17
  blog_entry.id

See that running here in django-sql-dashboard.

Returning related rows in a single SQL query using JSON#

Legislators and their terms, via a foreign key#

Tags on blog entries, via a many-to-many table#

Other databases#