Quantcast

Using nested SELECT with json_each()

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Using nested SELECT with json_each()

Jens Alfke-2
I’m writing code to generate SQL queries that use json_each to test the contents of a JSON array. The docs give this as an example:

        SELECT DISTINCT user.name
        FROM user, json_each(user.phone)
        WHERE json_each.value LIKE '704-%';

I’m not happy with this, as it requires the use of DISTINCT to avoid producing duplicate results when a row has more than one matching array value, and it it complicates my SQL-generation code to have to add arbitrary numbers of ‘tables’ to the FROM clause (I might have to do multiple tests on multiple arrays in the JSON.)

For my purposes it seems cleaner to use a nested SELECT with EXISTS:

        SELECT user.name
        FROM user
        WHERE EXISTS( SELECT 1 FROM json_each(user.phone) WHERE json_each.value like ‘704-%’ )

Is this going to be any less efficient? Or is there any other reason not to use it?

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using nested SELECT with json_each()

Clemens Ladisch
Jens Alfke wrote:
> SELECT DISTINCT user.name
> FROM user, json_each(user.phone)
> WHERE json_each.value LIKE '704-%';

0|0|0|SCAN TABLE user
0|1|1|SCAN TABLE json_each VIRTUAL TABLE INDEX 1:
0|0|0|USE TEMP B-TREE FOR DISTINCT

> For my purposes it seems cleaner to use a nested SELECT with EXISTS:
>
> SELECT user.name
> FROM user
> WHERE EXISTS( SELECT 1 FROM json_each(user.phone) WHERE json_each.value like ‘704-%’ )

0|0|0|SCAN TABLE user
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE json_each VIRTUAL TABLE INDEX 1:

> Is this going to be any less efficient?

The join and the correlated subquery are executed in the same way.

The first query stores all unique name values in a temporary index; the
second query is more efficient because it not only avoids this, but also
because the EXISTS implies a LIMIT 1.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using nested SELECT with json_each()

Jens Alfke-2
Thanks much!

(I’d forgotten about EXPLAIN QUERY PLAN — I had tried regular EXPLAIN, but the long list of virtual-machine instructions was too much for my brain.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using nested SELECT with json_each()

wmertens
This post has NOT been accepted by the mailing list yet.
Oh, this is great, thanks! In fact, I think this would be a very good addition to the JSON1 documentation.

Followup question: Is there any index I could add that would help sqlite with this query?
Loading...