5

I'm having trouble getting SQLite to index an expression. Specifically, I want to create an index on a particular property in a JSON object.

CREATE TABLE test (tags JSON);
INSERT INTO test VALUES(JSON_OBJECT('someKey', 'someValue'));

CREATE INDEX test_idx ON test(JSON_EXTRACT(tags, '$.someKey'));
EXPLAIN QUERY PLAN SELECT JSON_EXTRACT(tags, '$.someKey') FROM test;

This query plan says:

SCAN TABLE test

What I'm expecting is some sort of SEARCH TABLE test USING INDEX....

Any thoughts on how I can correct this?

Brad
  • 159,648
  • 54
  • 349
  • 530

2 Answers2

7

It turns out that the index isn't used if I attempt to SELECT it.

I have to use the JSON_EXTRACT() expression in a WHERE or ORDER BY clause. If I do that, it works fine.

EXPLAIN QUERY PLAN SELECT * FROM test WHERE JSON_EXTRACT(tags, '$.someKey')="asdf";

SEARCH TABLE test USING INDEX test_idx (=?)

Brad
  • 159,648
  • 54
  • 349
  • 530
2

Index makes a balanced tree and is intended for locating and sorting. An expression doesn't need to locate or sort unless in WHERE or ORDER BY subclause

Zim
  • 1,528
  • 1
  • 10
  • 6
  • 1
    Thanks for the explanation, @Zim. In my specific use case, I was using it in a WHERE clause, but I was testing by just having the expression in SELECT, which wasn't working, and I hadn't understood why. – Brad Apr 25 '21 at 05:27