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