alt Hacker Newsinteresting, but can't you use "Index On Expression" <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.
Replies
> slight change in json path syntax (can't think of any right now) can cause SQLite to not use this index
It's pretty fragile...
-- Just changing the quoting
select * from events where json_extract(data, "$.type") = 'click';
-- Changing the syntax
select * from events where data -> '$.type' = 'click';
Yes, that’s the simpler and faster solution.
You need to ensure your queries match your index, but when isn’t that true :)
From 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.
Yeah, you can use index on expression and views to ensure the expression matches, like 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.