Querying an indexed JSON expression with parameter binding

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Querying an indexed JSON expression with parameter binding

Kris Adler
I'm using a SQLite database with a single-table schema. The table includes
a column for JSON data. I have some shared logic that queries for rows
where various JSON fields match some value. So I have a generic query like
the following:

SELECT thing_data FROM thing WHERE json_extract(thing_data, "$key1") =
"$val1";


I've added an index on some specific JSON fields like the following:

CREATE INDEX thing_name ON thing(json_extract(thing_data, "$.name"));


I've found that query plans for select statements like the one above do
*not* use the index. But, if I change the query to hard-code the JSON field
path, the query planner uses the index:

SELECT thing_data FROM thing WHERE json_extract(thing_data, "$.name") =
"$val1";


There is a relevant note in the sqlite3_prepare_v2 docs:

If the specific value bound to host parameter in the WHERE clause might
> influence the choice of query plan for a statement, then the statement will
> be automatically recompiled, as if there had been a schema change, on the
> first sqlite3_step() call following any change to the bindings of that
> parameter. The specific value of WHERE-clause parameter might influence the
> choice of query plan if the parameter is the left-hand side of a LIKE or
> GLOB operator or if the parameter is compared to an indexed column and the
> SQLITE_ENABLE_STAT3 compile-time option is enabled.


So my question is: if I bind a value to the $key1 host parameter that makes
the json_extract expression match one used in an index-on-expression,
should the query planner then make use of the index-on-expression after the
statement is recompiled? Or, is the index-on-expression only used when the
original SQL text contains the exact expression, before any parameter
binding?

Thanks!
Kris
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Querying an indexed JSON expression with parameter binding

Richard Hipp-3
On 8/14/18, Kris Adler <[hidden email]> wrote:
> So my question is: if I bind a value to the $key1 host parameter that makes
> the json_extract expression match one used in an index-on-expression,
> should the query planner then make use of the index-on-expression after the
> statement is recompiled?

I'm guessing it will not, since the subroutine in the query planner
that checks to see if an expression in the WHERE clause matches an
expression in the index does not know that $key is equal to '$.name'.
For the time being, you should hard-code the field name.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users