Tortoise-ORM Filtering entries that contain a value in a JSONField list with an SQLite database

Question

I'm working on a project that's using Tortoise-ORM v0.18.1 and a SQLite db. I need to find an entry that has a given value inside a json field.

I have a model like this:

class Foo(Model):
    name = fields.CharField(max_length=50)
    boo = fields.JSONField()

The entries look like this:

Foo(name='First', boo=[1, 2, 3])
Foo(name='Second', boo=[4, 5, 6])
Foo(name='Third', boo=[4, 7, 8])

I'm looking to filter the entries that have "4" in the "boo" field.

I tried:

entries = await Foo.filter(boo_contains=4)

But entries contain the three objects instead of just the Second and Third.

Is there any way i can filter those out?

Documentation says "contains" is valid for text fields AND postgresql and MySQL drivers, so there's probably no way but querying all fields and then manually filtering or making a raw SQL query, but I figured it was worth the shot asking here if there.

I guess another option would be to have the boo field be a TEXTField and then just use the "contains" operator, I'm not sure if it would be a better option.

Answer 1

Try this.

entries = await Foo.filter(boo__contains=[4])