Postgresql Array Functions with QueryDSL

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


predicate.and(booleanTemplate("arraycontains(0, string_to_array('someTag,anotherTag', ','))", qEntity.tags));

QuerySyntaxException: unexpected AST node: ( near line 3, column 20 [select blablabla where arraycontains(entity.tags,string_to_array('someTag,anotherTag', ','))]

3 Answers
3

WHERE tags @> '"someTag","anotherTag"'::text;

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

tagsStr

String

,

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:

doS+

doS+ @>

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

arraycontains

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.

function

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, toy have to replace String with String in the ParameterExpression row above

String

String

ParameterExpression

cb

javax.persistence.criteria.CriteriaBuilder

predicate.and(booleanTemplate("arraycontains(0,1)", entity.tags, tags));

Expression<String>

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)

Expressions.booleanTemplate("FUNCTION('check_array', 0, 1) = true", entity.tags, tagsStr)

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.