Is there a QueryDSL representation of JSON_CONTAINS predicate?

Question

On my MySQL database (8.0.23), I have a JSON column that is multi value indexed. I would like to use QueryDSL to query using the index with JSON_CONTAINS. I have validated that the column is indexed properly and the index is being used when I run the query; for instance,

EXPLAIN SELECT *
FROM user u
WHERE JSON_CONTAINS(JSON_EXTRACT(u.alias,'$'), JSON_QUOTE('John'));

indicates the multi value index is being used as expected.

So far, I have attempted

Expressions.booleanTemplate("JSON_CONTAINS(JSON_EXTRACT({0}, '$'), JSON_QUOTE({1})) = 1", expression, str)

and have the BooleanExpression as the predicate to achieve the same using QueryDSL; please note that without the = 1, executing it would throw the following error.

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362)
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
    at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:282)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748)

Indeed, the = 1 seems like preventing the index from being used for the same query. For instance,

EXPLAIN SELECT *
FROM user u
WHERE JSON_CONTAINS(JSON_EXTRACT(u.alias,'$'), JSON_QUOTE('John')) = 1;

would indicate no index is being used. It was the same with != 0, IS TRUE or IS NOT FALSE

Therefore, the problem I'm having is that with QueryDSL I have not found a way to have a BooleanExpression on JSON_CONTAINS(JSON_EXTRACT(u.alias,'$'), JSON_QUOTE('John')) without the = 1 at the end. However, MySQL seems like it's not using the multi value index when there is = 1 at the end of the JSON_CONTAINS

I have attempted suggestions from https://stackoverflow.com/a/68684997/18476687 but had no luck so far.

Would there be a way to represent JSON_CONTAINS(JSON_EXTRACT(u.alias,'$'), JSON_QUOTE('John')) without the = 1 on QueryDSL so that multi value index is being used?

score 1 · Accepted Answer · answered Jun 10 '22 at 20:19

I found a hacky workaround for this that actually works pretty well. By extending the Hibernate dialect you can create custom HQL functions that return arbitrary SQL.

So you can define a dialect with a custom HQL function custom_json_contains like:

public class CustomMySQL8Dialect extends MySQL8Dialect {
    public CustomMySQL8Dialect() {
        registerFunction("custom_json_contains", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN, "JSON_CONTAINS(JSON_EXTRACT(?1, ?2), JSON_QUOTE(?3)) AND 1"));
    }
}

Then you can use this method in a QueryDSL where clause (or HQL):

public static <T> BooleanExpression jsonContains(Expression<T> expression, String path, String str) {
    return Expressions.booleanTemplate("CUSTOM_JSON_CONTAINS({0}, {1}, {2}) = 1", expression, path, str);
}

This will make Hibernate render the following SQL:

JSON_CONTAINS(JSON_EXTRACT(u.alias, '$'), JSON_QUOTE('John')) AND 1=1

The = 1 is still there but at least now it doesn't do anything!

Is there a QueryDSL representation of JSON_CONTAINS predicate?

1 Answers1