Postgresql Array Functions with QueryDSL

Question

I use the Vlad Mihalcea's library in order to map SQL arrays (Postgresql in my case) to JPA. Then let's imagine I have an Entity, ex.

@TypeDefs(
{@TypeDef(name = "string-array", typeClass = 
StringArrayType.class)}
)
@Entity
public class Entity {
    @Type(type = "string-array")
    @Column(columnDefinition = "text[]")
    private String[] tags;
}

The appropriate SQL is:

CREATE TABLE entity (
    tags text[]
);

Using QueryDSL I'd like to fetch rows which tags contains all the given ones. The raw SQL could be:

SELECT * FROM entity WHERE tags @> '{"someTag","anotherTag"}'::text[];

(taken from: https://www.postgresql.org/docs/9.1/static/functions-array.html)

Is it possible to do it with QueryDSL? Something like the code bellow ?

predicate.and(entity.tags.eqAll(<whatever>));

Answer 1

1. 1st step is to generate proper sql: WHERE tags @> '{"someTag","anotherTag"}'::text[];

2. 2nd step is described by coladict (thanks a lot!): figure out the functions which are called: @> is arraycontains and ::text[] is string_to_array

3. 3rd step is to call them properly. After hours of debug I figured out that HQL doesn't treat functions as functions unless I added an expression sign (in my case: ...=true), so the final solution looks like this:

    predicate.and(
            Expressions.booleanTemplate("arraycontains({0}, string_to_array({1}, ',')) = true", 
                    entity.tags,
                    tagsStr)
    );
   

where tagsStr - is a String with values separated by ,

Answer 2

Since you can't use custom operators, you will have to use their functional equivalents. You can look them up in the psql console with \doS+. For \doS+ @> we get several results, but this is the one you want:

                                          List of operators
   Schema   | Name | Left arg type | Right arg type | Result type |      Function       | Description 
------------+------+---------------+----------------+-------------+---------------------+-------------
 pg_catalog | @>   | anyarray      | anyarray       | boolean     | arraycontains       | contains

It tells us the function used is called arraycontains, so now we look-up that function to see it's parameters using \df arraycontains

                              List of functions
   Schema   |     Name      | Result data type | Argument data types |  Type  
------------+---------------+------------------+---------------------+--------
 pg_catalog | arraycontains | boolean          | anyarray, anyarray  | normal

From here, we transform the target query you're aiming for into:

SELECT * FROM entity WHERE arraycontains(tags, '{"someTag","anotherTag"}'::text[]);

You should then be able to use the builder's function call to create this condition.

ParameterExpression<String[]> tags = cb.parameter(String[].class);
Expression<Boolean> tagcheck = cb.function("Flight_.id", Boolean.class, Entity_.tags, tags);

Though I use a different array solution (might publish soon), I believe it should work, unless there are bugs in the underlying implementation.

An alternative to method would be to compile the escaped string format of the array and pass it on as the second parameter. It's easier to print if you don't treat the double-quotes as optional. In that event, you have to replace String[] with String in the ParameterExpression row above