How to use CriteriaDefinition for PostgreSQL arrays?

[Wuzhengyu97]

I am using Spring Data R2DBC with a PostgreSQL database. My goal was to use CriteriaDefinition to describe a query for a column of type text[] in PostgreSQL. Specifically, I wanted to perform array-related operations such as checking if the array contains a value (using the PostgreSQL @> operator).

For example, I expected to write something like this:

Query.query(Criteria.where("tags").contains("electronics"));

However, I could not find any built-in support for such array operators in CriteriaDefinition. This left me wondering if I have missed some part of the documentation or if this functionality is not supported.

The lack of array operator support means that for now, I would have to resort to raw SQL for such queries, like this:

String sql = "SELECT * FROM products WHERE tags @> ARRAY['electronics']::text[]";
databaseClient.sql(sql).all();

This feels frustrating, especially because I want to leverage the abstraction of R2dbcEntityTemplate and avoid writing raw SQL. Is there currently a way to describe such queries using CriteriaDefinition, or are array-related operations not yet supported for PostgreSQL?

I would greatly appreciate any insights or guidance on this topic.

[schauder]

You are correct, there is currently no special support for this operator.

I'm also not sure if it makes sense to support all the operators (and functions) that one database or another supports.

Instead we should come up with a way to define arbitrary criteria, or extend Criteria in a simple way, so that users can provide there own operators and functions.

[Wuzhengyu97]

@schauder

I believe the most straightforward and simple solution would be to add a new constructor method to Criteria, allowing developers to pass in custom string conditions, which can then be parsed into the framework's internal Condition object. For example, an expression like tags @> ARRAY['electronics']::text[], which essentially represents a database comparison operation, can be mapped to the Comparison type within the framework. This way, users can conveniently utilize database-specific operators (such as PostgreSQL's @>), without breaking the existing structure and design principles of Criteria.

At its core, I believe the framework should at least provide users with an option to support custom operators, rather than limiting them to the predefined set of operators. This would not only enhance the flexibility of the framework but also meet the demands of users dealing with specific database features or complex business scenarios. At the same time, it would preserve the ability to generate and control the underlying SQL.

To be candid, I'm not very familiar with this framework yet, and I've only taken a brief look at its code. Therefore, this is just an initial idea based on intuition. If there's anything inappropriate in it, I would appreciate some feedback and guidance. I strongly believe that having a mechanism to support custom operators would be a great complement to the framework's extensibility.

[Wuzhengyu97]

Perhaps I can provide a PR to try implementing this feature?
@schauder

[schauder]

That would be nice.