Hi all, Optimization question: If I have an array in JSON, what would be the best way to check that a given array contains a subset of the stored array?
Right now I have the WHERE clause EXISTS(SELECT 1 FROM json_each(tbl.json, "$.foo") j WHERE j.value = ?) AND EXISTS(SELECT 1 FROM json_each(tbl.json, "$.foo") j WHERE j.value = ?) But this seems O(n) for the stored array. Any other suggestions? Maybe counting the results? I came up with the fairly odd-looking 2 IN (SELECT COUNT(*) FROM ( SELECT 1 FROM json_each(tbl.json, "$.foo") j WHERE j.value in (?,?) ) (So the count of matched rows from the json_each must equal the count of arguments given to the query). This seems already faster. Any better way? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
