How to define query operators for JSONField (sqlite ext)?

[alzuse]

If I have such a model:

class KV(Model):
    name = TextField()
    opt = JSONField()
    value = FloatField()
KV.create_table()
KV.create(name='foo', opt={'k1': 0, 'k2': 1}, value=11)
KV.create(name='foo', opt={'k1': 0, 'k2': 0}, value=12)
KV.create(name='foo', opt={'k1': 1         }, value=13)

When querying, if KV.opt == {'k1': 0, 'k2': 1}, the result value should be 11; if KV.opt == {'k1': 1} or KV.opt == {'k1': 1, 'k2': 0} or KV.opt == {'k1': 1, 'k2': 1}, the result value should be 13, that is to say, as long as k1 is 1, other items in json dict don't matter.

The desired goal is that when KV.opt needs to be part of the matching opt, it counts as a successful match. How to customize a query operator for this? Thanks!

[coleifer]

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: https://github.com/coleifer/peewee/blob/c597250c5adcf759c30284a7cdca72e00644ca82/playhouse/sqlite_ext.py#L1324-L1359