Search text in JSONField

Question

I use peewee with SQLite. One of my models contains the JSONField (docs link) and now I need to query all rows where this field contains my string.

class Market(Model):
    ...
    data = JSONField()
    class Meta:
        database = db

If I try the following code, nothing returns:

Market.select().where(Market.data.contains(search))

Example data:

Market.data: ['123', '456', '789']
search: 456

How to find all rows where Market.data field contains the search text?

user_254346 · Answer

The sqlite json1 extension doesn't provide a "contains" function. You probably would want to use the json_each() or json_tree table-valued functions:

https://sqlite.org/json1.html#examples_using_json_each_and_json_tree_

Sqlite json1 extension docs:

https://sqlite.org/json1.html

Peewee sqlite json1 extension docs:

http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#sqlite-json1

Search text in JSONField

1 Answers