How to define query operators for JSONField (sqlite ext)? about peewee HOT 1 CLOSED

Sqlite does not come with a json_contains() type of function that would work as you describe. You'll need to instead use it's existing operators (extract) or implement a user-defined json_contains() for that particular operation.

Peewee provides a sample json_contains() but it does not behave as you described for absent keys in the data.

Simple example:

db = SqliteExtDatabase(':memory:', json_contains=True)
...
q = KV.select().where(fn.json_contains(KV.opt, json.dumps({'k1': 0})))
for row in q:
    print(row.value)
# 11.0
# 12.0

q = KV.select().where(fn.json_contains(KV.opt, json.dumps({'k1': 1, 'k2': 999})))
for row in q:
    print(row.value)
# no output, since k2 not present in source

So you'll probably want to modify the example json_contains() user-defined function to behave as you described. The source for it is here:

from peewee.