i.e. something like this: CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))?
i guess caveat here is that slight change in json path syntax (can't think of any right now) can cause SQLite to not use this index, while in case of explicitly specified Virtual Generated Columns you're guaranteed to use the index.
Very cool article. To really drill it home, I would have loved to see how the query plan changes. It _looks_ like it should Just Work(tm) but my brain refuses to believe that it's able to use those new indexes so flawlessly
Tiny bug report: I couldn't edit text in those SQL editor widgets from my iPhone, and I couldn't scroll them to see text that extended past the width of the page either.
I thought this was common practice, generated columns for JSON performance. I've even used this (although it was in Postgres) to maintain foreign key constraints where the key is buried in a JSON column. What we were doing was slightly cursed but it worked perfectly.
I was looking for a way to index a JSON column that contains a JSON array, like a list of tags. AFAIK this method won't work for that; you'll either need to use FTS or a separate "tag" table that you index.
I wish devs would normalize their data rather than shove everything into a JSON(B) column, especially when there is a consistent schema across records.
It's much harder to setup proper indexes, enforce constraints, and adds overhead every time you actually want to use the data.
In the 2nd section you're using a CREATE TABLE plus three separate ALTER TABLE calls to add the virtual columns. In the 3rd section you're using a single CREATE TABLE with the virtual columns included from the get go.
Generated columns are pretty great, but what I would really love is a Postgres-style gin index, which dramatically speeds up json queries for unanticipated keys.
As others mention, you can create indexes directly against the json without projecting in to a computed column... though the computed column has the added benefit of making certain queries easier.
That said, this is pretty much what you have to do with MS-SQL's limited support for JSON before 2025 (v17). Glad I double checked, since I wasn't even aware they had added the JSON type to 2025.
It's a pretty big deal as without an actual JSON data type queries are really parsing against strings for every action, which is much much slower in practice.
Most of the JSON functions added in iirc MS-SQL 2016 really performed poorly and is a significant reason why denormalized JSON data was used very sparingly... with actual JSON data types (assuming a binary deserialized form of storage), then queries and operations against that underlying data structure can run significantly faster.
I've been pretty critical of it since I tried using it for a few things a few years ago... it still worked well enough for the needs of what it was doing, but I'm glad that it's doing better.
For reference, what it was being used for was to semi-normalize most stored procedures to receive 2 argumenst and return 2. All JSON... the first argument would be the claims portion of the JWT for the service, the second would be a serialized typed request object representing the request to the service and the two results are the natural results to the sproc as well as an error result if an error occurred. This allowed for a very simplified API surface (basically 4 utility methods being used for all API calls), in the project in question it was a requirement for data logic to be inside the database, of which I'm not a fan, but it did work out pretty well for what it was. Other isseus not withstanding.
I've been coding a lot of small apps recently, and going from local JSON file storage to SQLite has been a very natural path of progression, as data's order of magnitude ramps up. A fully performant database which still feels as simple as opening and reading from a plain JSON file. The trick you describe in the article is actually an unexpected performance buffer that'll come in handy when I start hitting next bottleneck :) Thank you
Dude what? This is incredible knowledge. I had been fearing this exact problem for so long, but there is an elegant out of the box solution. Thank you!!
Hilariously, I discovered this very technique a couple weeks ago when Claude Code presented it out of the blue as an option with an implemented example when I was trying to find some optimizations for something I'm working on. It turned out to be a really smart and performant choice, one I simply wasn't aware of because I hadn't really kept up with new SQLite features the last few years at all.
Lesson learned: even if you know your tools well, periodically go check out updated docs and see what's new, you might be surprised at what you find!
I love SQLite and this is in no way I'm making a point devaluing SQLite, Author's method is excellent approach to get analytical speed out of SQLite. But I am loving DuckDB for similar analytical workloads as it is built for such tasks. DuckDB also reads from single file, like SQLite and DuckDB process large data sets at extreme speeds. I work on my macbook m2 and I have been dealing with about 20 million records and it works fast, very fast.
Loading data into DuckDB is super easy, I was surprised :
SELECT
avg(sale_price),
count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';
and you can also load into a JSON type column and can use postgres type syntax
col->>'$.key'
38 comments
[ 4.2 ms ] story [ 70.0 ms ] threadi.e. something like this: CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))?
i guess caveat here is that slight change in json path syntax (can't think of any right now) can cause SQLite to not use this index, while in case of explicitly specified Virtual Generated Columns you're guaranteed to use the index.
It's much harder to setup proper indexes, enforce constraints, and adds overhead every time you actually want to use the data.
Why?
That said, this is pretty much what you have to do with MS-SQL's limited support for JSON before 2025 (v17). Glad I double checked, since I wasn't even aware they had added the JSON type to 2025.
Most of the JSON functions added in iirc MS-SQL 2016 really performed poorly and is a significant reason why denormalized JSON data was used very sparingly... with actual JSON data types (assuming a binary deserialized form of storage), then queries and operations against that underlying data structure can run significantly faster.
I've been pretty critical of it since I tried using it for a few things a few years ago... it still worked well enough for the needs of what it was doing, but I'm glad that it's doing better.
For reference, what it was being used for was to semi-normalize most stored procedures to receive 2 argumenst and return 2. All JSON... the first argument would be the claims portion of the JWT for the service, the second would be a serialized typed request object representing the request to the service and the two results are the natural results to the sproc as well as an error result if an error occurred. This allowed for a very simplified API surface (basically 4 utility methods being used for all API calls), in the project in question it was a requirement for data logic to be inside the database, of which I'm not a fan, but it did work out pretty well for what it was. Other isseus not withstanding.
What?
Lesson learned: even if you know your tools well, periodically go check out updated docs and see what's new, you might be surprised at what you find!
Loading data into DuckDB is super easy, I was surprised :
SELECT avg(sale_price), count(DISTINCT customer_id) FROM '/my-data-lake/sales/2024/*.json';
and you can also load into a JSON type column and can use postgres type syntax col->>'$.key'
You can do the same with DuckDB and Postgres too.