I have searched the forum a few times now looking for a similar suggestion and have not found anything, and after reading the documentation multiple times there does not appear to be anything on the same level of simplicity as this suggested function (though I will admit to being blind as a bat at times).
#Description:
I think there should be another function added to the JSON1 extension which would allow for checking whether or not a value exists in a JSON object or array. In my own code (using a user-defined function) I have named this json_contains(), and it does seem convenient to have. It would not add any missing functionality but it would make certain queries simpler and more readable. Using the style of the existing documentation, I would describe it as such:
>json_contains(*json,path,value*)
>
>The json_contains(X,P,V) function returns 1 if X is well-formed JSON and at path P there exists a JSON array containing the value V. If a JSON object lies at path P then returns 1 if there exists a key equal to to V. Otherwise returns 0 if the object or array does not contain the value V.
This would be equivalent to the Python "in" keyword when used with lists and dictionaries and would simplify WHERE clauses when the query is checking for rows with JSON columns containing some value. If the path specified does not exist, then the function would return NULL like json_extract() and json_array_length() do.
#Examples:
```
CREATE TABLE example ("ID" INTEGER, "Data" JSON);
INSERT INTO example VALUES (1, '{"key":"object value"}'), (2, '["list value"]');
SELECT ID FROM example WHERE json_contains(Data, '$', 'key'); → Returns 1
SELECT ID FROM example WHERE json_contains(Data, '$', 'list value'); → Returns 2
SELECT ID FROM example WHERE json_contains(Data, '$', 'object value'); → Returns zero rows```
For simple select queries there is little gain when compared to using json_each() and writing the query like: `SELECT ID FROM example, json_each(example.Data) WHERE json_each.value='object value';` but this may instead be used to remove a select sub-query from a delete or update statement.
```
DELETE FROM example WHERE json_contains(Data, '$', 'list value');
Versus
DELETE FROM example WHERE ID IN (SELECT ID FROM example, json_each(example.Data) WHERE json_each.value='object value');```
#Notes:
I'm fairly certain this would be new, but if I have missed something or some way of using json_each() or similar in update/delete queries I'd love to know. Writing the WHERE clauses this way seems intuitive to me, though I am undoubtedly biased due to coding in Python and using the "in" keyword often.