Postgres JSON Functions With Hibernate 6

In this article, look at the posjsonhelper library and learn about adding support for the JSON functions for your project that use Hibernate 6 and Postgres.

Szymon Tarnowski

CORE ·

Jul. 03, 23 · Tutorial

Likes (4)

Comment

Save

20.6K Views

This is a continuation of the previous article where it was described how to add support for the Postgres JSON functions and use Hibernate 5. In this article, we will focus on how to use JSON operations in projects that use Hibernate framework with version 6.

Native Support

Hibernate 6 already has some good support for query by JSON attributes as the below example presents.

We have our normal entity class that has one JSON property:

    Java
   
 

   import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.annotations.Type;
import org.hibernate.type.SqlTypes;

import java.io.Serializable;


@Entity
@Table(name = "item")
public class Item implements Serializable {

    @Id
    private Long id;

    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "jsonb_content")
    private JsonbContent jsonbContent;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public JsonbContent getJsonbContent() {
        return jsonbContent;
    }

    public void setJsonbContent(JsonbContent jsonbContent) {
        this.jsonbContent = jsonbContent;
    }
}
  

The JsonbContent type looks like the below:

    Java
   
 

   import jakarta.persistence.Embeddable;
import jakarta.persistence.Enumerated;
import jakarta.persistence.EnumType;
import org.hibernate.annotations.Struct;

import java.io.Serializable;
import java.util.List;

@Embeddable
public class JsonbContent implements Serializable{

    private Integer integer_value;
    private Double double_value;
    @Enumerated(EnumType.STRING)
    private UserTypeEnum enum_value;
    private String string_value;
	//Getters and Setters
}
  

When we have such a model we can for example query by the string_value attribute.

    Java
   
 

       public List<Item> findAllByStringValueAndLikeOperatorWithHQLQuery(String expression) {
        TypedQuery<Item> query = entityManager.createQuery("from Item as item_ where item_.jsonbContent.string_value like :expr", Item.class);
		query.setParameter("expr", expression);
        return query.getResultList();
    }
  

Important! - Currently, there seems to be some limitation with the support of query by attributes which is that we can not query by complex types like arrays. As you can see, the JsonbContent type has the Embeddable annotation, which means that If you try to add some property that is a list we could see an exception with the following message: The type that is supposed to be serialized as JSON can not have complex types as its properties: Aggregate components currently may only contain simple basic values and components of simple basic values.

In the case when our JSON type does not need to have properties with a complex type, then native support is enough.

Please check the below links for more information:

However, sometimes it is worth having the possibility to query by array attributes. Of course, we can use native SQL queries in Hibernate and use Postgres JSON functions which were presented in the previous article. But it would be also useful to have such a possibility in HQL queries or when using programmatically predicates. This second approach is even more useful when you are supposed to implement the functionality of a dynamic query. Although dynamically concatenating a string that is supposed to be an HQL query might be easy but better practice would be to use implemented predicates. This is where using the posjsonhelper library becomes handy.

Posjsonhelper

The project exists Maven central repository, so you can easily add it by adding it as a dependency to your Maven project.

    XML
   
 

   <dependency>
            <groupId>com.github.starnowski.posjsonhelper</groupId>
            <artifactId>hibernate6</artifactId>
            <version>0.2.1</version>
</dependency>
  

Register FunctionContributor

To use the library, we have to attach the FunctionContributor component. We can do it in two ways. The first and most recommended is to create a file with the name org.hibernate.boot.model.FunctionContributor under the resources/META-INF/services directory.

As the content of the file, just put posjsonhelper implementation of the org.hibernate.boot.model.FunctionContributor type.

    Plain Text
   
   com.github.starnowski.posjsonhelper.hibernate6.PosjsonhelperFunctionContributor

The alternative solution is to use com.github.starnowski.posjsonhelper.hibernate6.SqmFunctionRegistryEnricher component during application start-up, as in the below example with the usage of the Spring Framework.

    Java
   
 

   import com.github.starnowski.posjsonhelper.hibernate6.SqmFunctionRegistryEnricher;
import jakarta.persistence.EntityManager;
import org.hibernate.query.sqm.NodeBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationListener;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.event.ContextRefreshedEvent;

@Configuration
public class FunctionDescriptorConfiguration implements
        ApplicationListener<ContextRefreshedEvent> {

    @Autowired
    private EntityManager entityManager;

    @Override
    public void onApplicationEvent(ContextRefreshedEvent event) {
        NodeBuilder nodeBuilder = (NodeBuilder) entityManager.getCriteriaBuilder();
        SqmFunctionRegistryEnricher sqmFunctionRegistryEnricher = new SqmFunctionRegistryEnricher();
        sqmFunctionRegistryEnricher.enrich(nodeBuilder.getQueryEngine().getSqmFunctionRegistry());
    }
}
  

For more details please check "How to attach FunctionContributor."

Example Model

Our model looks like the example below:

    Java
   
 

   package com.github.starnowski.posjsonhelper.hibernate6.demo.model;

import io.hypersistence.utils.hibernate.type.json.JsonType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.annotations.Type;
import org.hibernate.type.SqlTypes;


@Entity
@Table(name = "item")
public class Item {

    @Id
    private Long id;

    @JdbcTypeCode(SqlTypes.JSON)
    @Type(JsonType.class)
    @Column(name = "jsonb_content", columnDefinition = "jsonb")
    private JsonbContent jsonbContent;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public JsonbContent getJsonbContent() {
        return jsonbContent;
    }

    public void setJsonbContent(JsonbContent jsonbContent) {
        this.jsonbContent = jsonbContent;
    }
}
  

Important!: In this example, the JsonbConent property is a custom type (as below), but it could be also the String type.

    Java
   
 

   package com.github.starnowski.posjsonhelper.hibernate6.demo.model;

import jakarta.persistence.*;
import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.type.SqlTypes;

import java.io.Serializable;
import java.util.List;
public class JsonbContent implements Serializable{

    private List<String> top_element_with_set_of_values;
    private Integer integer_value;
    private Double double_value;
    @Enumerated(EnumType.STRING)
    private UserTypeEnum enum_value;
    private String string_value;
    private Child child;

//  Setters and Getters
}
  

DDL operations for the table:

    SQL
   
 

   create table item (
        id bigint not null,
        jsonb_content jsonb,
        primary key (id)
    )
  

For presentation purposes, let's assume that our database contains such records:

    SQL
   
 

   
INSERT INTO item (id, jsonb_content) VALUES (1, '{"top_element_with_set_of_values":["TAG1","TAG2","TAG11","TAG12","TAG21","TAG22"]}');
INSERT INTO item (id, jsonb_content) VALUES (2, '{"top_element_with_set_of_values":["TAG3"]}');
INSERT INTO item (id, jsonb_content) VALUES (3, '{"top_element_with_set_of_values":["TAG1","TAG3"]}');
INSERT INTO item (id, jsonb_content) VALUES (4, '{"top_element_with_set_of_values":["TAG22","TAG21"]}');
INSERT INTO item (id, jsonb_content) VALUES (5, '{"top_element_with_set_of_values":["TAG31","TAG32"]}');

-- item without any properties, just an empty json
INSERT INTO item (id, jsonb_content) VALUES (6, '{}');

-- int values
INSERT INTO item (id, jsonb_content) VALUES (7, '{"integer_value": 132}');
INSERT INTO item (id, jsonb_content) VALUES (8, '{"integer_value": 562}');
INSERT INTO item (id, jsonb_content) VALUES (9, '{"integer_value": 1322}');

-- double values
INSERT INTO item (id, jsonb_content) VALUES (10, '{"double_value": 353.01}');
INSERT INTO item (id, jsonb_content) VALUES (11, '{"double_value": -1137.98}');
INSERT INTO item (id, jsonb_content) VALUES (12, '{"double_value": 20490.04}');

-- enum values
INSERT INTO item (id, jsonb_content) VALUES (13, '{"enum_value": "SUPER"}');
INSERT INTO item (id, jsonb_content) VALUES (14, '{"enum_value": "USER"}');
INSERT INTO item (id, jsonb_content) VALUES (15, '{"enum_value": "ANONYMOUS"}');

-- string values
INSERT INTO item (id, jsonb_content) VALUES (16, '{"string_value": "this is full sentence"}');
INSERT INTO item (id, jsonb_content) VALUES (17, '{"string_value": "this is part of sentence"}');
INSERT INTO item (id, jsonb_content) VALUES (18, '{"string_value": "the end of records"}');

-- inner elements
INSERT INTO item (id, jsonb_content) VALUES (19, '{"child": {"pets" : ["dog"]}}');
INSERT INTO item (id, jsonb_content) VALUES (20, '{"child": {"pets" : ["cat"]}}');
INSERT INTO item (id, jsonb_content) VALUES (21, '{"child": {"pets" : ["dog", "cat"]}}');
INSERT INTO item (id, jsonb_content) VALUES (22, '{"child": {"pets" : ["hamster"]}}');
  

Using Criteria Components

Below is an example of the same query presented at the beginning, but created with SQM components and criteria builder:

    Java
   
 

   public List<Item> findAllByStringValueAndLikeOperator(String expression) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Item> query = cb.createQuery(Item.class);
        Root<Item> root = query.from(Item.class);
        query.select(root);
        query.where(cb.like(new JsonBExtractPathText(root.get("jsonbContent"), singletonList("string_value"), (NodeBuilder) cb), expression));
        return entityManager.createQuery(query).getResultList();
    }

  

Hibernate is going to generate the SQL code as below:

    SQL
   
 

   select
        i1_0.id,
        i1_0.jsonb_content 
    from
        item i1_0 
    where
        jsonb_extract_path_text(i1_0.jsonb_content,?) like ? escape ''
  

The jsonb_extract_path_text is a Postgres function that is equivalent to the #>> operator (please check the Postgres documentation linked earlier for more details).

Operations on Arrays

The library supports a few Postgres JSON function operators, such as:

?&- This checks if all of the strings in the text array exist as top-level keys or array elements. So generally if we have a JSON property that contains an array, then you can check if it contains all elements that you are searching by.
?| - This checks if any of the strings in the text array exist as top-level keys or array elements. So generally if we have a JSON property that contains an array, then you can check if it contains the least of the elements that you are searching by.

Besides executing native SQL queries, Hibernate 6 does not have support for the above operations.

Required DDL Changes

The operator above can not be used in HQL because of special characters. That is why we need to wrap them, for example, in a custom SQL function. Posjsonhelper the library requires two custom SQL functions that will wrap those operators. For the default setting these functions will have the implementation below.

    SQL
   
   CREATE OR REPLACE FUNCTION jsonb_all_array_strings_exist(jsonb, text[]) RETURNS boolean AS $$
SELECT $1 ?& $2;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION jsonb_any_array_strings_exist(jsonb, text[]) RETURNS boolean AS $$
SELECT $1 ?| $2;
$$ LANGUAGE SQL;

For more information on how to customize or add programmatically required DDL please check the section "Apply DDL changes."

"?&" Wrapper

The below code example illustrates how to create a query that looks at records for which JSON property that contains an array has all string elements that we are using to search.

    Java
   
 

   public List<Item> findAllByAllMatchingTags(Set<String> tags) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Item> query = cb.createQuery(Item.class);
        Root<Item> root = query.from(Item.class);
        query.select(root);
        query.where(new JsonbAllArrayStringsExistPredicate(hibernateContext, (NodeBuilder) cb, new JsonBExtractPath(root.get("jsonbContent"), (NodeBuilder) cb, singletonList("top_element_with_set_of_values")), tags.toArray(new String[0])));
        return entityManager.createQuery(query).getResultList();
    }
  

In case the tags contain two elements, then Hibernate would generate the below SQL:

    SQL
   
 

   select
        i1_0.id,
        i1_0.jsonb_content 
    from
        item i1_0 
    where
        jsonb_all_array_strings_exist(jsonb_extract_path(i1_0.jsonb_content,?),array[?,?])
  

"?|" Wrapper

The code in the example below illustrates how to create a query that looks at records for which JSON property contains an array and has at least one string element that we are using to search.

    Java
   
 

   public List<Item> findAllByAnyMatchingTags(HashSet<String> tags) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Item> query = cb.createQuery(Item.class);
        Root<Item> root = query.from(Item.class);
        query.select(root);
        query.where(new JsonbAnyArrayStringsExistPredicate(hibernateContext, (NodeBuilder) cb, new JsonBExtractPath(root.get("jsonbContent"), (NodeBuilder) cb, singletonList("top_element_with_set_of_values")), tags.toArray(new String[0])));
        return entityManager.createQuery(query).getResultList();
    }
  

In case the tags contain two elements, then Hibernate would generate SQL as below:

    SQL
   
 

   select
        i1_0.id,
        i1_0.jsonb_content 
    from
        item i1_0 
    where
        jsonb_any_array_strings_exist(jsonb_extract_path(i1_0.jsonb_content,?),array[?,?])
  

For more examples of how to use numeric operators please check the demo dao object and dao tests.

Why Use the posjsonhelper Library When Hibernate Has Some Support for JSON Attributes Query

Besides those two operators that support the array types mentioned above, the library has two additional useful operators. The jsonb_extract_path and jsonb_extract_path_text are wrappers for #> and #>> operators. The Hibernate supports the ->> operator. To see the difference between those operators, please check the Postgres documentation linked earlier.

However, as you read at the beginning of the article, the native query support for JSON attributes is only allowed when the JSON class has properties with simple types. And more importantly, you can not query by attribute if it is not mapped to the property in the JSON type. That might be a problem if you assume that your JSON structure can be more dynamic and have an elastic structure not defined by any schema.

With the posjsonhelper operator, you don't have this problem. You can query by any attribute which you want. It does not have to be defined as a property in JSON type. Furthermore, the property in our entity that stores the JSON column does not have to be a complex object like JsonbConent in our examples. It can be a simple string in Java.

Conclusion

As was mentioned in the previous article, in some cases, Postgres JSON types and functions can be good alternatives for NoSQL databases. This could save us from the decision of adding NoSQL solutions to our technology stack which could also add more complexity and additional costs.

That also gives us flexibility when we need to store unstructured data in our relation base and the possibility to query in those structures.

JSON Hibernate PostgreSQL Framework Java (programming language)

Opinions expressed by DZone contributors are their own.

Related

Trending