Tortoise-ORM: How to query a Postgres `ArrayField` from tortoise.contrib.postgres.fields

Question

Just created a Postgres ArrayField in Tortoise-ORM via from tortoise.contrib.postgres.fields import ArrayField to host some tags I wish to put. Looking at my Postgres db the generated field is indeed of type text[] which is good but how exactly do I query it using the Tortoise Query API?

I've tried several attempts at using filter but each one either returns an empty list or raises an error.

Table

+----+---------------+
| id | tags (text[]) |
+----+---------------+
| 1  | {foo,bar}     |
+----+---------------+
| 2  | {foo}         |
+----+---------------+
| 3  | {bar}         |
+----+---------------+

# End results

# Works but uses: CAST("tags" AS VARCHAR) LIKE '%foo%'
await Card.filter(tags__contains='foo').values_list('id', flat=True)   

# Doesn't work
await Card.filter(tags__search=['foo']).values_list('id', flat=True)     # error
await Card.filter(tags__search='foo').values_list('id', flat=True)       # error
await Card.filter(tags=['foo']).values_list('id', flat=True)             # error
await Card.filter(tags='foo').values_list('id', flat=True)               # error
await Card.filter(tags__in=['foo']).values_list('id', flat=True)         # []
await Card.filter(tags__in='foo').values_list('id', flat=True)           # []

Also tried it using set or tuple but the results are the same.

Update

I haven't been able to find a way to query an ArrayField in TORM so I'm using JSONField instead which has much better support.

# Old
tags = ArrayField('text', null=True)    # can't query for now

# New
tags = JSONField(default=[])

I will keep this question here in case someone else has the same problem.

Answer 1

I was also considering using JSONField, although in Postgres Array field is a bit more flexible in some cases. I found this comparison in DBA community.

I ended up implementing my own typed ArrayField: How to use Postgresql Array field in Tortoise-ORM

In your case, I suppose, you will need to implement your own filter operator for array contains. I would check out existing filters for an example: default filters and contrib Postgres filters.

Here is how contains operator looks in PostgreSQL itself: Postgres: check if array field contains value?

Hope this is somehow helpful :)