SQLite has had JSON support for a while.
However recently it added a killer feature:generated
columns. (This was added in 3.31.0,
released 2020-01-22.)
This makes it possible to insert JSON straight into SQLite and then have it
extract data and index them, i.e. you can treat SQLite as a document database.
This has been possible with PostgreSQL and obviously is what something like
Elastic provides but having it available in an embedded database is very nice
for lightweight stuff.
$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Connected to a transient in-memory database.
sqlite> CREATE TABLE t (
body TEXT,
d INT GENERATED ALWAYS AS (json_extract(body, ‘$.d’)) VIRTUAL);
sqlite> insert into t values(json(‘{“d”:”42″}’));
sqlite> select * from t WHERE d = 42;
{“d”:”42″}|42
It’s that simple, thedcolumn is extracted from the provided JSON.
(Aside: The hard bit may be getting a new enough SQLite, at the time of writing
Homebrew on macOS has it, else you likely need to use an unstable source like
nixpkgs-unstable.)
There’s some nice properties of this. Normally it’s encouraged to minifiy and
validate JSON when inserting (via thejson()function) as because SQLite
doesn’t have a JSON type it will allow anything. However nothing enforces that,
you could add a constraint but will probably forget… HavingGENERATED ALWAYSusingjson_extractmeans invalid JSON will get aError: malformed JSONat
INSERT time.
sqlite> CREATE TABLE x (
body TEXT,
id TEXT GENERATED ALWAYS AS (json_extract(body, ‘$.id’)) VIRTUAL NOT NULL);
sqlite> insert into x values(”);
Error: malformed JSON
sqlite> insert into x values(‘{}’);
Error: NOT NULL constraint failed: x.id
We can enforce items are present in the inserted JSON, here by addingNOT
NULL, but we could also use constraints and other SQLite features!
You’ll notice I’ve usedVIRTUALwith the generated column in these examples.
There’s also the option of usingSTOREDto essentially cache the values,
although a downside is you can’t add those columns viaALTER TABLE.
However you can always create an index on a column, even if it’s defined a
virtual one:
Then check that’s going to work as expected:EXPLAIN QUERY PLAN SELECT * FROM x WHERE id=’foo’;
QUERY PLAN
`–SEARCH TABLE x USING INDEX xid (id=?)