Search code examples
hibernatejpaquerydsl

how to treat mysql json column with queryDSL?

I want to know how to treat MySQL JSON column.

product table:
...
    category_ids                     json                         null,
...

category_ids ex: [19, 102, 108]

If I want to search list of product contains category id 102 how can I make query with queryDSL?

I tried with JsonNode type, but it doesn't work.

Solution

  • Based on the tags, I assume you use querydsl-jpa to create JPQL (or Hibernates HQL) queries. This is important to note, because in order to get JSON types to work with Querydsl, they not only have to work with Querydsl and MySQL, but foremost with the query language itself, in this case coming from Hibernate.

    I assume you already managed to map your json column to Jacksons JsonNode type properly. If not, then its important to note that for any non-standard type, a custom type needs to be registered with Hibernate. You can either cook up your own custom type, or use an already available implementation out there. I highly recommend the hibernate-types library developed by Vlad (with contributions from a few others, including myself). How to integrate hibernate-types into your project is explained fairly well in his answer to this Stackoverflow question: https://stackoverflow.com/a/37946530/2104280

    The first thing you need to do is to set up the following Hibernate Types Maven dependency in your project pom.xml configuration file:

       <dependency>
       <groupId>com.vladmihalcea</groupId>
       <artifactId>hibernate-types-52</artifactId>
       <version>${hibernate-types.version}</version>
   </dependency>

    Now, you need to declare the JsonType on either class level or in a package-info.java package-level descriptor, like this:

       @TypeDef(name = "json", typeClass = JsonType.class)

    And, the entity mapping will look like this:

       @Type(type = "json")
   @Column(columnDefinition = "jsonb")
   private Location location;

    Now you will be able to use the JSON property in simple operations in queries. Operations that are allowed include basically the basic comparisons (=, != and IS (NOT) NULL). Other operations are not available, because these are defined in MySQL only, and not in the JPQL nor HQL query languages.

    You want to check whether a JSON array contains a specific element. I assume that category_ids represents a JSON array. Then that means the MySQL snippet you want to produce likely is something along the lines of category_ids ? :categoryId.

    I'd do like to point out that that will only work for IDs stored as string as opposed to a number in the JSON array. You might want to map category_ids as a bigint[] instead. Like JSON types, array types are also supported by the Hibernate-Types library, so the answer works the same for arrays.

    However, JSON_CONTAINS(category_ids, :categoryId) is not valid HQL/JPQL, because the function does not exists in that query language. If we want to render JSON_CONTAINS(category_ids, :categoryId) in HQL/JPQL, we would need to define a custom function that renders JSON_CONTAINS(category_ids, :categoryId) as SQL.

    There are two ways to declare a custom function in Hibernate.

    1. Extend the dialect that you are using and declare the function there. This approach is well described in https://stackoverflow.com/a/42486291/2104280 .
    2. Use the MetadataBuilderInitializer SPI to register custom functions during bootstrap. This approach is described in https://stackoverflow.com/a/41369853/2104280 .

    Using the MetadataBuilderInitializer approach, the code would end up like the following:

    public class JSONMetadataBuilderInitializer
        implements MetadataBuilderInitializer {
    @Override
    public void contribute(MetadataBuilder metadataBuilder,
            StandardServiceRegistry serviceRegistry) {
        metadataBuilder.applySqlFunction("json_contains_key",
            new SQLFunctionTemplate(BooleanType.INSTANCE,
                "JSON_CONTAINS(?1, ?2, '$'"));
    }
}

    Finally, you end up with a Hibernate type for your JsonNode property, and a custom function to check whether a key is present. The key can be used as follows in JPQL/HQL:

    SELECT product FROM Product product WHERE json_contains_key(product.categories, '1234') = true;

    Which would produce the following SQL:

    SELECT * FROM product WHERE JSON_CONTAINS(categories, '1234', '$') = true;

    However, you want to construct this JPQL/HQL query through Querydsl, which means that a few steps remain. We need to construct new expression types for the created function. There are essentially two options:

    1. Create a new Operator implementation, and register a Template for that Operator in an extended JPQLTemplates.
    2. Create a TemplateExpression (literally, a fragment of JPQL).

    The second approach is easier so is the one I'll use here. Using a TemplateExpression, you end up with the following code in Querydsl to generate the aforementioned JPQL query:

    queryFactory.select(QProduct.product)
    .from(QProduct.product)
    .where(Expressions.booleanTemplate(
        "json_contains_key({0}, {1})",
        QProduct.product.categories,
        Expressions.constants("1234").isTrue())
    .fetch();

    However, this isn't as fluent as you might be used to in Querydsl. It is possible to extend Querydsl with custom expression types, and hereby hypothetically introduce a JsonExpression. It is also possible to extend querydsl-apt to automatically generate paths of this expression type in the static metamodel (the Q-classes). I have built the extension library hibernate-types-querydsl-apt exactly for this purpose. It adds various custom expression types for custom types from the Hibernate-Types project (such as ranges, arrays, json, hstore, intervals). It also registers the custom functions for the primary operations automatically. Using my extension, the use case for arrays would be as simple as:

    @Entity
@TypeDefs({
        @TypeDef(name = "int-array", typeClass = IntArrayType.class, defaultForType = int[].class)
})
public class ArrayEntity {
    @Id
    Long id;

    @Type(type = "int-array")
    @Column(name = "sensor_values", columnDefinition = "integer[]")
    int[] sensorValues;
}

    List<Tuple> fetch = new JPAQuery<>(entityManager, ExtendedHQLTemplates.DEFAULT)
    .from(arrayEntity)
    .select(arrayEntity)
    .where(arrayEntity.sensorValues.contains(123))
    .fetch();

    More examples are available at: https://github.com/jwgmeligmeyling/hibernate-types-querydsl-apt/blob/master/querydsl-ext-testsuite/src/test/java/com/pallasathenagroup/querydsl/ArrayEntityPathTest.java .