Modeling One-to-Many in SQLite Using the JSON1 Extension
The real additional complexity lies in building a query that servers our typical query pattern: fetching an entire record by its UUID. In the past it was sufficient to select all columns (
SELECT *), now we need to join with theissue_assigneetable to get a full representation of an issue into memory. The relational approach reduces data locality (not all information about an issue is located in one place anymore) which adds complexity to our application.[…]
As mentioned in the intro of the article, SQlite has built-in support to query columns that contain JSON documents (support was added in SQlite 3.9) through the JSON1 extension.
This means we can model the assignees of an issue as an array of JSON objects, instead of using a join table[…]
[…]
Thanks to the JSON1 extension we can also build queries for fetching all issues assigned to specific user, without fetching all issues into memory. To fetch all issues assigned to the user with the UUID “7” we can use the following query:
SELECT Issues.* from Issues, json_each(Issues.assignees) WHERE json_extract(value, '$.uuid') = "7"
