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>));

I came up with: `predicate.and(booleanTemplate("arraycontains({0}, string_to_array('someTag,anotherTag', ','))", qEntity.tags));` but the functions can't be execute and this exception is thrown: `QuerySyntaxException: unexpected AST node: ( near line 3, column 20 [select blablabla where arraycontains(entity.tags,string_to_array('someTag,anotherTag', ','))]` sources: https://stackoverflow.com/questions/41544332/call-mysql-nested-inner-functions-in-querydsl and https://stackoverflow.com/questions/22984343/how-to-call-mysql-function-using-querydsl — Serhii Romanov, Jan 18 '18 at 15:02

score 11 · Accepted Answer · edited Mar 07 '22 at 23:57

1st step is to generate proper sql: WHERE tags @> '{"someTag","anotherTag"}'::text[];
2nd step is described by coladict (thanks a lot!): figure out the functions which are called: @> is arraycontains and ::text[] is string_to_array
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 ,

Thank you so much for the `=true` part. Saved me hours. One remark: I'd rather use `org.hibernate.dialect.Dialect.registerFunction(String, SQLFunction)` to register a specific function name for the native jsonb operators. Feels somewhat 'cleaner'. — John, Nov 25 '19 at 13:50

coladict · Answer 2 · 2020-05-07T16:36:13.670

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

by `cb` you mean `javax.persistence.criteria.CriteriaBuilder`? I'd like to use only QueryDSL. I used the function you figured out and came with: `predicate.and(booleanTemplate("arraycontains({0},{1})", entity.tags, tags));` where tags is `Expression` - "QuerySyntaxException: unexpected AST node: ( near line 3, column 20 [select blablabla where arraycontains(entity.tags,(?1))]" then — Serhii Romanov, Jan 17 '18 at 10:50
Well, it's similar enough, and it was the custom operator that is the problem you needed to circumvent. This article should help you create the function call https://luisfpg.blogspot.com/2013/02/the-beauty-of-querydsl-in-sorting.html — coladict, Jan 17 '18 at 11:01
@SerhiiRomanov the correct syntax for the template is `FUNCTION('arraycontains', {0}, {1}) = true` — OrangeDog, Nov 14 '19 at 17:47

score 0 · Answer 3 · answered Sep 15 '18 at 03:38

For EclipseLink I created a function

CREATE OR REPLACE FUNCTION check_array(array_val text[], string_comma character varying ) RETURNS bool AS $$
        BEGIN
                RETURN arraycontains(array_val, string_to_array(string_comma, ','));
        END;
$$ LANGUAGE plpgsql;

As pointed out by Serhii, then you can useExpressions.booleanTemplate("FUNCTION('check_array', {0}, {1}) = true", entity.tags, tagsStr)

Postgresql Array Functions with QueryDSL

3 Answers3

Linked