Storing Foreign Keys in a SQLite JSON Array

I recently ran into an interesting database constraint problem — uniqueness when the only distinguishing factor is the relations. In this model rows A and B are identical if they share the same relations. This actually turned out to be an extremely difficult constraint to represent because unique indices tend to limited to a single row.

I’ve considered approaches designed around maintaining a hash of relations and making that hash unique. The simplest approach would be to calculate this hash at the service level, but that leaves an important database constraint left up to the user. Alternatively, we could write a trigger function to compute the hash every time a junction table is modified — computing the hash once for each relation. This would work in the sense that it’s correct but scales quadratically with the number of relationships.

Arrays, however, have an interesting property in that the relation is stored directly in the row. It’s basically trivial to express a unique constraint when the data is in this form — all you need to do is write a unique index on the field. Unfortunately, direct arrays are a somewhat niche SQL feature and support is uncommon. PostgreSQL isn’t the only database which supports arrays, but it is the only driver supported by my ORM which also supports arrays.

In my deployment I’ll be using PostgreSQL so that’s not inherently a problem, but I would really prefer to avoid a database schema which doesn’t work on SQLite for ease of testing.

SQLite has no native array type, or even properly a JSON type, but it does have a fairly complete set of functions for manipulating columns of text JSON fields — users can easily extract, filter, or modify JSON columns. For this specific use case we use the table-valued json_each function which returns a table of rows representing the object.

1
select value AS INTEGER from json_each('[1,2,3]');

1
2
3

Let’s apply that to a more complex example — can we store foreign keys in a JSON array and then join on it?

1
CREATE TABLE chapter (
2
  id INTEGER PRIMARY KEY,
3
  title TEXT NOT NULL
4
;
5
CREATE TABLE spine (
6
  id INTEGER PRIMARY KEY,
7
  chapter_ids JSON NOT NULL
8
;
9

10
INSERT INTO chapter (id, title) VALUES (1, 'Lorem Ipsum');
11
INSERT INTO spine (id, chapter_ids) VALUES (1, json('[1]'));
12

13
SELECT
14
 b.title
15
 FROM spine a
16
 JOIN json_each(a.chapter_ids) je ON TRUE
17
 JOIN chapter b ON b.id = CAST(je.value AS INTEGER);

Lorem Ipsum

When is this approach useful? I think that:

• JSON foreign keys are the least complicated approach I’ve considered which solves this constraint problem purely in the database.
• Junction tables are simpler to represent and likely more performant in most cases.
• If you’re writing a performance sensitive application you should not trust anything I say and benchmark approaches you’re considering.