Querying by JSONField

[Soyuzbek]

Hi dear all!

Does Tortoise-ORM provide filtering by keys in JSONField?

Consider:

`class Item(Model):
name = fields.CharField()
data = fields.JSONField()

item = await Item.create(name='something', data = {'some_key': 'some_value'})
item = await Item.create(name='something', data = {'some_key': 'some_value1'})
item = await Item.create(name='something', data = {'some_key': 'some_value2'})
`

Can I filter something like bellow?:
items = await Item.filter(data__some_key__contains='some')

like Django does.

[AnimeshRy]

Anyone update on this as this is not currently available. You can't query JSON field data in tortoise I suppose?

[ktolstikhin]

As for now, v0.25.1, you can filter by key value of the JSONField like this:

items = await Item.filter(data__contains={"some_key": "some_value"})

[merlinz01]

Note that this works great for PostgreSQL but not for SQLite.

[Soyuzbek]

I found that it is possible to use filter lookup for Json fields.
the snippet bellow

items = await Item.filter(data__filter={'some_key__in': ('value1', 'value2')})

Firstly we have to register in lookup to tortoise:

from pypika.terms import Term, BasicCriterion


class In(BasicCriterion):
    def __init__(self, left, right, alias=None) -> None:
        super().__init__('IN', left, right, alias=alias)

    def get_sql(self, quote_char='"', with_alias=False, **kwargs):
        sql = "{left}{comparator}{right}".format(
            comparator=self.comparator,
            left=self.left.get_sql(quote_char=quote_char, **kwargs),
            right=self.right.get_sql(quote_char=quote_char, **kwargs),
        )

        return sql
        
def _postgres_json_in(left: Term, value: tuple):
    return In(left, left.wrap_constant(value))  

# models.py
from tortoise.contrib.postgres.json_functions import operator_keywords
from .utils import _postgres_json_in

operator_keywords["in"] = _postgres_json_in