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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
