Natively support PGARRAY from PostgreSQL within the ORM

[odimko]

Is your feature request related to a problem? Please describe.
The request is not related to a problem and it is about a new feature, namely, I would like to be able to do exact matching of elements in a column in a database with a provided list of values natively by the means of PGARRAY data type, PostgreSQL built-in.

Describe the solution you'd like
Possible usage is mentioned in the fixed issue: using __icontains for string matching returns partial string matches. However, fo my use case, I need exact matches. Thus, I would like, instead of implicitly converting JSONB to VARCHAR, to be able to have my data in the PGARRAY data type, so that if I have two rows with ["book", "new book", "interesting bookstore", "interesting book"] and ["new book", "interesting bookstore", "interesting book"] values in the Phrases column respectively, to search for matches with "book" and only get the first row, since it contains this element.

Describe alternatives you've considered
A solution (and a manual alternative) would be to create a new field type in a similar fashion as here for the JSONB field:

tortoise-orm/tortoise/fields/data.py

Line 371 in bad2710

class JSONField(Field, dict, list): # type: ignore

After that a new filter is needed, that could also be created similarly to

tortoise-orm/tortoise/filters.py

Line 150 in a3d8507

def contains(field: Term, value: str) -> Criterion:

Additional context
The feature would bring more value to the ORM when it comes to the exact matching of elements with a database.

[grigi]

Seems reasonable, we should do it.

Ok, for filtering by it, the SQL would be something like:

SELECT * FROM foo WHERE ANY(bar) = 'value'

Ah, and UNNEST(bar) would be a good reverse aggregate? Possibly only usable in .values()?
And there is also index access support? How would we even filter on that?

I think we should create an ArrayField but the actual type of field is flexible, so can we do CharArrayField, IntArrayField, TextArrayField?

We will have to try and implement some kind of emulation for SQLite and MySQL which don't support this
(doesn't have to be perfect, e.g. I have no idea how to emulate UNNEST())

[odimko]

Hi @grigi! It's nice to see you have answered so fast :)
I am afraid I have not been fully clear on what I was requesting, sorry for the possible misunderstanding. What I meant in my request was to have the functionality in tortoise-orm similar to the array functions in PostgreSQL, described in the documentation, specifically (please see Table 9.51. Array Operators):

Operator	Description	Example	Result
<@	is contained by	ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6]	t

So essentially I would like to be able to check if one array is contained in another one (if each of its elements appears in the other array).
Please let me know if this makes things clearer.

[odimko]

hey @grigi! I was wondering if there are any updates on this request?
Thanks in advance.

[grigi]

Hi @odimko No, sorry no updates. I don't have much time this week to do anything much.

[odimko]

Hi @grigi! Just checking in to ask if there's any progress with my request. Any update would be appreciated.
Cheers, Dmytro.

[grigi]

Sorry, no. Since the whole covid-19 thing I have extremely limited time. I'm still around and healthy, but don't see much time becoming available for the next few weeks. if anybody wants to have a go at this feature, you're welcome.