[Lex-2008]

interesting, 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.

[pkhuong]

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.

[fny]

> 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';

Basically anything that alters the text of an expression within the where clause

[johnmaguire]

TIL. Are MySQL and Postgres this fragile too?

[paulddraper]

Yes, that’s the simpler and faster solution.

You need to ensure your queries match your index, but when isn’t that true :)

[0x457]

> 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]

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.

[debugnik]

I'm not sure 2015 counts as new, but that's same release that first introduced the JSON extension. There isn't a version of SQLite with JSON expressions but without indexes on expressions. Also, the JSON extension wasn't enabled by default until 2022, so most people using SQLite with JSON have got a version much newer than 2015.

[Lex-2008]

i initially misread "2015" as "2025", too... But no, it was part of SQLite for ten years already!