News for hackers. Stuff that matters.
Re:JSON with Sqlite by Groxx on Thursday February 28, 2019 @11:43PM (#19277976)
Since you can make effectively arbitrary index expressions[1], I'd expect the answer to be "yes". The article doesn't explicitly state if they're deterministic or not, but it seems like some of them could / should be.

[1]: https://www.sqlite.org/expridx.html

Re:JSON with Sqlite by regecks on Thursday February 28, 2019 @11:57PM (#19278019)
I gave it a shot and it appears to work as expected.

  sqlite> create table a (id int primary key, j json);
  sqlite> insert into a values (1, '{"hello":"world"}');
  sqlite> create index idx_a on a (json_extract(j, '$.hello'));
  sqlite> explain query plan select * from a where json_extract(j, '$.hello') = 'world';
  QUERY PLAN
  `--SEARCH TABLE a USING INDEX idx_a (<expr>=?)
  sqlite> explain query plan select * from a where json_extract(j, '$.foo') = 'world';
  QUERY PLAN
  `--SCAN TABLE a