Add filter on array and jsonb field types

[ijreilly]

Scope & Context

So far we had left out the possibility to filter on field types involving jsonB and array columns (ex: Links, Emails...).
It is now doable thanks to the recent work that replaced pg_graphql (@Weiko ).

Let's introduce filter features for these fields !

[charlesBochet]

We are still waiting on full pg_graphql deprecation to move forward on this one!

[gitstart-app]

Here is the GitStart Ticket for this issue: https://clients.gitstart.com/twenty/5449/tickets/TWNTY-6784

[charlesBochet]

@gitstart-twenty to work on this issue you currently need the IsQueryRunnerTwentyORMEnabled feature flag to be enable on your workspace

We have introduced new resolvers that do not use pg_graphql (pg_graphql does not support filter on Array fields / jsonb fields)

Please reach out to @Weiko to prepare this ticket if needed

[ijreilly]

@lucasbordeau fyi

[charlesBochet]

@ijreilly @gitstart-twenty while investigating this last week, it seems that these types are already present in the graphql schema, not sure they are applied by the graphql-api-runner though

[Weiko]

@charlesBochet I've tried on main and indeed the API exposes some filters already but they are not implemented (or broken). Some are missing as well (in the schema). See screenshots below.

Json Filter

• isFilter: allows us to filter out NULL or NOT_NULL records. [Implemented already]
• containsFilter, this one is missing and should be very naive and not leverage jsonb comparisons but only search in the whole json (not the values). Later we might add "hasKey", "containsValues", etc... [To do]

Array Filter

containsFilter, containsAnyFilter, notContains are self explanatory. Those are exposed via the API already but the filter is not implemented when building the ORM query so they will throw an error.

Backend implementation

Check packages/twenty-server/src/engine/api/graphql/workspace-schema-builder/graphql-types/input folder to define Filter types. This is used to generate the types in the gql schema.
Check packages/twenty-server/src/engine/api/graphql/graphql-query-runner/graphql-query-parsers/graphql-query-filter/graphql-query-filter-condition.parser.ts to see how gql filters are parsed and converted to ORM conditions.
You can activate debug mode with DEBUG_MODE=true to see the queries that are generated or use getSql() in the query builder in GraphqlQueryFindManyResolverService if this can help.