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?

Is there a QueryDSL representation of JSON_CONTAINS predicate?

0 Answers0