Support querying json field

[lewoudar]

Hi,
First of all I'm really impressed by the work made on ormar. I hope to use it more in the future. Something I think will be useful for many developers is the support of JsonField querying. We have many cases where it is convenient to store data as json, so querying this json data is also something we need.
Taking the example from the django documentation if we have a model like the following:

# skipping some initialization
class Dog(ormar.Model):
    class Meta(BaseMeta):
        tablename = 'dog'

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=200)
    data: Optional[dict] = ormar.JSON(nullable=True)

and we instantiate some models like this:

await Dog.objects.create(name='Rufus', data={
            'breed': 'labrador',
            'owner': {
                'name': 'Bob',
                'other_pets': [{'name': 'Fishy'}]
            }
        })
await Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': None})

we should be able to query the data field like this:

await Dog.objects.filter(data__owner__name='Bob').all()  # should return a queryset with rufus dog
await Dog.objects.filter(data__owner__other_pets__0__name='Fishy').all()  # should return a queryset with rufus dog

Let me know what you think :)

[collerek]

Thanks for the nice word! :)
That seems like a nice idea.

Currently, the dunder notation is reserved for chaining related models and not into JSON data but that should be doable, especially that it seems that sqlalchemy core has required functionality to build similar queries: https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.JSON

[lewoudar]

I'm glad to read this kind of answer. Hope to see it implement one day :)

[lewoudar]

A workaround I found for now is to play the sqlalchemy table related to ormar model

# skip some initialization
with engine.connect() as conn:
    dog_table = Dog.Meta.table
    # equivalent to: await Dog.objects.filter(data__owner__name='Bob').all()
    statement = sqlalchemy.select(dog_table).where(dog_table.c.data['owner']['name'].as_string() == 'Bob')
    for row in conn.execute(statement):
        print(row)
    
     # equivalent to: await Dog.objects.filter(data__owner__other_pets__0__name='Fishy').all()
    statement = sqlalchemy.select(dog_table).where(
        dog_table.c.data['owner']['other_pets'][0]['name'].as_string() == 'Fishy'
    )
    for row in conn.execute(statement):
        print(row)

[lewoudar]

I also realized I can do the same with databases, so I don't even need direct calls to sqlalchemy 😅

async with database:
    dog_table = Dog.Meta.table
    query = dog_table.select().where(dog_table.c.data['owner']['name'].as_string() == 'Bob')
    row = await database.fetch_one(query=query)
    print(row)
    
    query = dog_table.select().where(dog_table.c.data['owner']['other_pets'][0]['name'].as_string() == 'Fishy')
    row = await database.fetch_one(query=query)
    print(row)