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

[Jens Alfke]

> 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 automatically update that
> derived table when a row gets inserted, deleted or updated? Any pointers on
> how to do that?

I’ve done similar things, in implementing map/reduce on top of SQLite, but I 
don’t think the approach I used will help you because it relied on some 
specifics of the schema I was using, and it also wasn’t as fast as it could 
have been. I need to revisit it soon, as I’m rewriting that same code.

The trigger approach looks promising. One complication, that doesn’t show up in 
that snippet on the FTS5 page, is that a row in the source table can produce 
multiple rows in the derived table. So the derived table would need a foreign 
key referencing the rowid in the source table, so that when a source row is 
updated or deleted all the derived rows can be replaced.

(If you get this working, I’d be interested to see the SQL code for it.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Wout Mertens]

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 query them with 5 OR clauses.
> > Would that use more or less resources?
>
> Check with EXPLAIN QUERY PLAN whether the indexes are used at all.
> It's possible you'll have to use UNION instead.


No:
sqlite> create table t(json JSON);
sqlite> create index foo on t(json);
sqlite> explain query plan select * from t where json = '5';
0|0|0|SEARCH TABLE t USING COVERING INDEX foo (json=?)
sqlite> explain query plan select * from t where json like '5';
0|0|0|SCAN TABLE t

Thanks for the FTS5 link!

Wout
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

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 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?

Check with EXPLAIN QUERY PLAN whether the indexes are used at all.
It's possible you'll have to use UNION instead.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Wout Mertens]

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 = "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

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

[Clemens Ladisch]

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.

Or extend the JSON1 extension to do this behind the scenes.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users