Search code examples
pythonpython-3.xsqlitepeewee

Search text in JSONField

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?

Solution

  • 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