SQLite Forum

json_contains Function Idea
Login

json_contains Function Idea

(1) By SeverKetor on 2021-04-08 23:22:00 [link] [source]

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.

(2.1) By ddevienne on 2021-04-09 07:18:32 edited from 2.0 in reply to 1 [link] [source]

Deleted

(3) By Bjoern Hoehrmann (bjoern) on 2021-06-10 23:13:37 in reply to 1 [link] [source]

I needed something like this a few days ago and implemented it using the https://github.com/0x09/sqlite-statement-vtab extension:

    DROP TABLE IF EXISTS JSON_SET_CONTAINS;
    CREATE VIRTUAL TABLE JSON_SET_CONTAINS USING statement((
      SELECT
        -- TODO: Should JSON_SET_CONTAINS(NULL, ...) return NULL?
        EXISTS(
          SELECT 1 FROM JSON_EACH(?1) each WHERE each.value = ?2
        ) AS value
    ))
    ;
sqlite> select value from json_set_contains('[3,2,1]', 2);
value
-----
1    
Run Time: real 0.000 user 0.000759 sys 0.000000

(My arrays are ordered and contain no duplicate values to make equivalence testing easy, hence the set in the name.)

(4) By SeverKetor on 2021-06-11 02:27:14 in reply to 3 [source]

Neat, I'll have to look into that extension.

As of just a few days ago I started working on my own extension just for this function (slow going since I don't know C). It's still not quite done (I'm betting there are things I hadn't considered, like if the JSON was NULL as mentioned in your version. Also I use '#include "json1.c"' which results in a lot of extra stuff being compiled into the .so, which isn't great), but I'm thinking of posting it in this thread when I'm done.

A quick test showed 6-8x speed improvement over my python user-defined function and a 10-50% improvement over using json_each. Not really important for my usage since all uses of it in my code currently are fast anyway, but it's been fun figuring out how to make it work and learning a little C.