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.
Join the DZone community and get the full member experience.
Join For FreeThis 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:
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:
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.
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.
<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.
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.
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:
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.
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:
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:
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:
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:
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.
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.
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:
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.
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:
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.
Opinions expressed by DZone contributors are their own.
Comments