Re: [sqlite] Fastest way to search json array values?

> On Nov 9, 2016, at 3:04 AM, Wout Mertens wrote: > >> 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

Re: [sqlite] Fastest way to search json array values?

On Wed, Nov 9, 2016 at 12:20 PM Clemens Ladisch wrote: > Wout Mertens wrote: > > 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

Re: [sqlite] Fastest way to search json array values?

Wout Mertens wrote: > 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? Something like this: http://www.sqlite.org/fts5.html#external_content_tables > Another approach I

Re: [sqlite] Fastest way to search json array values?

On Wed, Nov 9, 2016 at 11:42 AM Clemens Ladisch 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 =

Re: [sqlite] Fastest way to search json array values?

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

[sqlite] Fastest way to search json array values?

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