On Wed, Nov 9, 2016 at 11:42 AM Clemens Ladisch <clem...@ladisch.de> wrote:
> Wout Mertens wrote: > > I'm using the JSON1 extension, and I want to find items by json array > > contents. > > > > This means a query like `SELECT foo.json FROM foo, json_each(foo.json) > > WHERE json_each.value = "bar";`, so basically scanning the entire table. > > > > I understand that virtual tables can not be indexed, so what other > > approaches would be possible here to prevent such a slow operation? > > Indexes (even expression indexes) require a 'real' table. > > You'd have to put the results of json_each() into a separate table that > can then be indexed. > I assume I can use some sort of trigger setup to automatically update that derived table when a row gets inserted, deleted or updated? Any pointers on how to do that? Another approach I thought of is to assume I will only have a certain number of array items (let's say 5), generate an "where is not null" expression index for each of them, and then query them with 5 OR clauses. Would that use more or less resources? Finally, I suppose I could use LIKE on the json field to see if it includes the string, and then further filter from there. However, creating an index on the json field does not seem to help with LIKE. Or extend the JSON1 extension to do this behind the scenes. I'm going to leave that option on the shelf for now :) Wout. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
