DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Postgres JSON Functions With Hibernate 5
  • Modify JSON Data in Postgres and Hibernate 6
  • How Spring and Hibernate Simplify Web and Database Management
  • Graceful Shutdown: Spring Framework vs Golang Web Services

Trending

  • Measuring the Impact of AI on Software Engineering Productivity
  • Cookies Revisited: A Networking Solution for Third-Party Cookies
  • Artificial Intelligence, Real Consequences: Balancing Good vs Evil AI [Infographic]
  • Issue and Present Verifiable Credentials With Spring Boot and Android
  1. DZone
  2. Coding
  3. Frameworks
  4. Postgres JSON Functions With Hibernate 6

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.

By 
Szymon Tarnowski user avatar
Szymon Tarnowski
DZone Core CORE ·
Jul. 03, 23 · Tutorial
Likes (4)
Comment
Save
Tweet
Share
23.0K Views

Join the DZone community and get the full member experience.

Join For Free

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:

  • Stack Overflow: Hibernate 6.2 and json navigation
  • Hibernate ORM 6.2 - Composite aggregate mappings
  • GitHub: hibernate6-tests-native-support-1

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

  • Postgres JSON Functions With Hibernate 5
  • Modify JSON Data in Postgres and Hibernate 6
  • How Spring and Hibernate Simplify Web and Database Management
  • Graceful Shutdown: Spring Framework vs Golang Web Services

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!