7 ms·
SQLite JSON at full index speed using generated columns
- focusgroup0 10mo agoWould this be a good fit for migrating from mongo --> sqlite? A task I am dreading
- zffr 10mo agoJust curious, why do you want to migrate from mongo (document database server) to sqlite (relational database library)? That migration would be making two changes: document-based -> relational, and server -> library. Have you considered migrating to Postgres instead? By using another DB server you won't need to change your application as much.
- focusgroup0 10mo agoThanks for the feedback. The document model in mongo was slopped together by a junior engineer, so perhaps an unorthodox approach. It is basically flat, and already used in a pseudo-relational manner via in-app join to the existing sqlite store. This blog post inspired me to think, what if we just chucked all the json from mongo into sqlite and used the generated indices? Then we can gradually "strangler fig" endpoint by endpoint
- rglynn 10mo agoThis sounds roughly on-track, but I agree with GP; Postgres would probably be better (also has great JSON(B) support).
- upmostly 10mo agoI was inspired to write this blog post after reading bambax's comment on a HN post back in 2023: https://news.ycombinator.com/item?id=37082941 https://news.ycombinator.com/item?id=37082941
- Lex-2008 10mo agointeresting, but can't you use "Index On Expression" <https://sqlite.org/expridx.html https://sqlite.org/expridx.html>? 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.
- pkhuong 10mo agoYeah, you can use index on expression and views to ensure the expression matches, like https://github.com/fsaintjacques/recordlite https://github.com/fsaintjacques/recordlite . The view + index approach decouples the convenience of having a column for a given expression and the need to materialise the column for performance.
- paulddraper 10mo agoYes, that’s the simpler and faster solution. You need to ensure your queries match your index, but when isn’t that true :)
- 0x457 10mo ago> but when isn’t that true When you write another query against that index a few weeks later and forget about the caveat, that slight change in where clause will ignore that index.
- WilcoKruijer 10mo agoFrom the linked page: > The ability to index expressions was added to SQLite with version 3.9.0 (2015-10-14). So this is a relatively new addition to SQLite.
- deleted 10mo ago[deleted]
- debugnik 10mo ago
- mcluck 10mo agoVery 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
- mring33621 10mo agoIIRC, Vertica had/has a similar feature.
- xp84 10mo agoNow there’s a name I haven’t heard in 10 years. (I’m only tenuously connected to the kinds of teams that use/would have used that, so it doesn’t mean much.)
- kwillets 10mo agoIt's been around for quite while, but DB people hate to explain where they got an idea. For all I know Vertica got it from somewhere else; I think postgres got jsonb around the same time.
- simonw 10mo agoTiny 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.
- hamburglar 10mo agoThe examples also needed a “drop table if exists” so they could be run more than once without errors.
- upmostly 10mo agoGreat catch, I'll add that now!
- upmostly 10mo agoThanks Simon! Looking into that now. Big fan. Hope you enjoyed the post. Edit: This should now be fixed for you.
- jelder 10mo agoI 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.
- sigwinch 10mo agoIt is. I’d wondered if STORED is necessary and this example uses VIRTUAL.
- ramon156 10mo agoIt works until you realize some of these usages would've been better as individual key/value rows. For example, if you want to store settings as JSON, you first have to parse it through e.g. Zod, hope that it isn't failing due to schema changes (or write migrations and hope that succeeds). When a simple key/value row just works fine, and you can even do partial fetches / updates
- mickeyp 10mo agoEAV data models are kinda cursed in their own right, too, though.
- jelder 10mo agoThe necessity of using a JSON column was outside of my control, but Zod etc. are absolutely required, I think, in most projects. I wrote more about that here: https://www.jacobelder.com/2025/01/31/where-shift-left-fails-type-theater.html https://www.jacobelder.com/2025/01/31/where-shift-left-fails...
- deleted 10mo ago[deleted]
- jasonthorsness 10mo agoThis is the typical practice for most index types in SingleStore as well except with the Multi-Value Hash Index which is defined over a JSON or BSON path
- bushbaba 10mo agoFor smaller datasets (100s of thousands of rows) I don’t see why you wouldn’t just use json columns with generated column/index where needed
- bilekas 10mo agoRegardless of the number of rows, it doesn't really matter, there are useful cases for where you might be consuming json directly, so instead of parsing it out into a schema for your database, why not just keep it raw and utilize the tools of the database. It's a feature, not a replacement.
- baq 10mo agoMy understanding is Snowflake works kinda like that behind the scenes right?
- AlexErrant 10mo agoI 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.
- deleted 10mo ago[deleted]
- garaetjjte 10mo agoI would want that too. It's possible in MySQL: https://dev.mysql.com/doc/refman/8.4/en/create-index.html#create-index-multi-valued https://dev.mysql.com/doc/refman/8.4/en/create-index.html#cr...
- rini17 10mo agoYou can use triggers to keep the tag table synchronized automatically.
- MyOutfitIsVague 10mo agoYeah, SQLite doesn't have any true array datatype. I think you could probably do it with a virtual table, but that would be adding a native extension, and it would have to pack its own index.
- ellisv 10mo agoI 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.
- konart 10mo agoNormalisation brings its own overhead though.
- deleted 10mo ago[deleted]
- crazygringo 10mo agoFor very simple JSON data whose schema never changes, I agree. But the more complex it is, the more complex the relational representation becomes. JSON responses from some API's could easily require 8 new tables to store the data in, with lots of arbitrary new primary keys and lots of foreign key constraints, your queries will be full of JOIN's that need proper indexing set up... Oftentimes it's just not worth it, especially if your queries are relatively simple, but you still need to store the full JSON in case you need the data in the future. Obviously storing JSON in a relational database feels a bit like a Frankenstein monster. But at the end of the day, it's really just about what's simplest to maintain and provides the necessary performance. And the whole point of the article is how easy it is to set up indexes on JSON.
- nh2 10mo agoJSON columns shine when * The data does not map well to database tables, e.g. when it's tree structures (of course that could be represented as many table rows too, but it's complicated and may be slower when you always need to operate on the whole tree anyway) * your programming language has better types and programming facilities than SQL offers; for example in our Haskell+TypeScript code base, we can conveniently serialise large nested data structures with 100s of types into JSON, without having to think about how to represent those trees as tables.
- 10mo ago
- meindnoch 10mo agoIn 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. Why?
- hamburglar 10mo agoI think the intent is to separate the virtual column creation out when it’s introduced in order to highlight that it’s a very lightweight operation. When moving onto the 3rd example, the existence of the virtual columns is just a given.
- hiccuphippo 10mo agoIn 2 they show how to add virtual columns to an existing table, in 3 how to add indexes to existing virtual columns so they are pre-cooked. Like a cooking show.
- upmostly 10mo agoLiterally exactly as I meant it. I watch a lot of cooking shows, too, so this analogy holds up.
- meindnoch 10mo ago>In 2 they show how to add virtual columns to an existing table No, in section 2 the table is created afresh. All 3 sections start with a CREATE TABLE.
- hiccuphippo 10mo agoYes, it seems each section has its own independent database so you have to create everything on each of them.
- ralferoo 10mo agoDepending on the amount of inserts, it might be more efficient to create all the indexes in one go. I think this is certainly true for normal columns. But I suspect with JSON the overhead of parsing it each time might make it more efficient to update all the indices with every insert. Then again, it's probably quicker still to insert the raw SQL into a temporary table in memory and then insert all of the new rows into the indexed table as a single query.
- N_Lens 10mo agoWhat a neat trick, I love SQLite as well.
- moregrist 10mo agoGenerated 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.
- pipe01 10mo agoMongoDB is dead, long live MongoDB
- tracker1 10mo agoAs 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.
- advisedwang 10mo agoExclusively using computed columns, and never directly querying the JSON does have the advantage of making it impossible to accidentally write a unindexed query.
- selimthegrim 10mo agoI did hear about it at a local DBA conference but didn't think it was a big deal
- tracker1 10mo agoIt'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.
- pawelduda 10mo agoI'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
- groundzeros2015 10mo ago> We've got an embedded SQLite-in-the-browser component on our blog What?
- qwertox 10mo agoProbably using https://sqlite.org/wasm/doc/trunk/index.md https://sqlite.org/wasm/doc/trunk/index.md
- Seattle3503 10mo agoIt says full speed, but no benchmarks were performed to verify if performance was really equivalent.
- joshtbradley 10mo agoDude 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!!
- kevinsync 10mo agoHilariously, 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!
- deleted 10mo ago[deleted]
- daotoad 10mo agoRereading TFM can be quite illuminating.
- srameshc 10mo agoI 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'
- mikepurvis 10mo agoWhoa. Is that first query building an index of random filesystem json files on the fly?
- NortySpock 10mo agoIt's not an index, it's just (probably parallel) file reads That being said, it would be trivial to tweak the above script into two steps, one reading data into a DuckDB database table, and the second one reading from that table.
- lame_lexem 10mo agocan we all agree to never store datasets uncompressed. duckdb supports reading many compression formats
- hawk_ 10mo agoHow much impact do the various compression formats have on query performance?
- loa_observer 10mo agoduckdb is super fast for analytic tasks, especially when u use it with visual eda tool like pygwalker. it allows u handles millions of data visuals and eda in seconds. but i would say, comparing duckdb and sqlite is a little bit unfair, i would still use sqlite to build system in most of cases, but duckdb only for analytic. you can hardly make a smooth deployment if you apps contains duckdb on a lot of platform
- javantanna 10mo agoYour website looks like supermemory.ai , BTW its pretty cool
- dmezzetti 10mo agoI love this feature. I've long used json_extract to create dynamic columns with txtai sql: https://neuml.github.io/txtai/embeddings/query/#dynamic-columns https://neuml.github.io/txtai/embeddings/query/#dynamic-colu... You can do the same with DuckDB and Postgres too.
- stacktraceyo 10mo agoCan I do this with pocket base?
- verytrivial 10mo agoIf you replace JSON with XML in this model it is exactly what the "document store" databases from the 90s and 00s were doing -- parsing at insert and update time, then touching only indexes at query time. It is indeed cool that sqlite does this out of the box.
- rcarmo 10mo agoI've been using this trick for a while, and it actually got me to do quite a bit without an ORM (just hacking a sane models.py with a few stable wrappers and calling it a day)
- morshu9001 10mo agojson columns pretty much obviated the need for ORMs. It used to be that you'd sometimes have a deep nested thing you really only ever query all at once rather than in pieces, so you'd use an ORM to automate that, but now you can just shove it into json. And then use regular SQL for the relations you actually care about.
- maxpert 10mo agoLOL what are the odds, I posted in `Show HN` about Marmot today https://github.com/maxpert/marmot/releases/tag/v2.2.0 https://github.com/maxpert/marmot/releases/tag/v2.2.0 and in my head I was thinking exact same thing for supporting MySQL's JSON datatype. At some level I am starting to feel, I might as well be able to expose a full MongoDB compatible protocol that let's you talk to tables as collections, solving this problem once it for all! But this technique I guess is very common now.
- oars 10mo agoGreat article with clear instructions - could be quite useful if I need to do stuff with storing JSON in SQLite in the future.
- eliasdejong 10mo agoIt is also possible to encode JSON documents directly as a serialized B-tree. Then you can construct iterators on it directly, and query internal fields at indexed speeds. It is still a serialized document (possible to send over a network), though now you don't need to do any parsing, since the document itself is already indexed. It is called the Lite³ format. Disclaimer: I am working on this. https://github.com/fastserial/lite3 https://github.com/fastserial/lite3
- the_duke 10mo agoWould love a Rust implementation of this.
- conradev 10mo agoThis is super cool! I've always liked Rkyv (https://rkyv.org https://rkyv.org) but it requires Rust which can be a big lift for a small project. I see this supports binary data (`lite3_val_bytes`) which is great!
- eliasdejong 10mo agoThank you. Having a native bytes type is non-negotiable for any performance intensive application that cannot afford the overhead of base64 encoding. And yes, Rkyv also implements this idea of indexing serialized data. The main differences are: 1) Rkyv uses a binary tree vs Lite³ B-tree (B-trees are more cache and space efficient). 2) Rkyv is immutable once serialized. Lite³ allows for arbitrary mutations on serialized data. 3) Rkyv is Rust only. Lite³ is a 9.3 kB C library free of dependencies. 4) Rkyv as a custom binary format is not directly compatible with other formats. Lite³ can be directly converted to/from JSON. I have not benchmarked Lite³ against Rust libraries, though it would be an interesting experiment.
- conradev 10mo agoThat second point is huge – Rkyv does have limited support for in-place mutation, but it is quite limited! If you added support for running jq natively, that would be very cool. Lite³ brings the B-trees, jq brings the query parser and bytecode, combined, you get SQLite :P
- rrmdp 10mo agoThe fact the DB is portable is amazing, I use it for all my projects now but didn't know about this JSON feature
- zackify 10mo agoI love jsonb support in sqlite. Particularly with drizzle, it means I can use sqlite on device with expo-sqlite, and store our data format in a single field, with very little syntax, and the schema and queries all become fully type safe. Also being able to use the same light orm abstraction server side with bun:sqlite is huge.
- bambax 10mo agoOpening an article on HN, seeing one of my comments quoted at the top, and then finding out the whole article is about that one comment: that's a first! > So, thanks bambax! You're most welcome! And yes, SQLite is awesome!!
- kristianp 10mo agoThis is the comment that inspired tfa: https://news.ycombinator.com/item?id=37083561 https://news.ycombinator.com/item?id=37083561