Postgres JSON Functions With Hibernate 5
Take a look at how you can add support for the JSON functions in your project that uses Hibernate 5 and Postgres with the posjsonhelper library.
Join the DZone community and get the full member experience.
Join For FreePostgres database supports a few JSON types and special operations for those types.
In some cases, those operations might be a good alternative for document databases like MongoDB or other NoSQL databases. Of course, databases like MongoDB might have better replication processes, but this subject is outside of the scope of this article.
In this article, we will focus on how to use JSON operations in projects that use Hibernate framework with version 5.
Example Model
Our model looks like the example below:
@Entity
@Table(name = "item")
public class Item {
@Id
private Long id;
@Column(name = "jsonb_content", columnDefinition = "jsonb")
private String jsonbContent;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getJsonbContent() {
return jsonbContent;
}
public void setJsonbContent(String jsonbContent) {
this.jsonbContent = jsonbContent;
}
}
Important!: We could use a specific JSON type for the jsonbContent
property, but in Hibernate version 5, that would not give any benefits from an operations standpoint.
DDL operation:
create table item (
id int8 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"]}}');
Native Query Approach
In Hibernate 5, we can use a native approach where we execute a direct SQL command.
Important!: Please, for presentation purposes, omit the fact that the below code allows SQL injection for expression for the LIKE
operator. Of course, for such action, we should use parameters and PreparedStatement
.
private EntityManager entityManager;
public List<Item> findAllByStringValueAndLikeOperatorWithNativeQuery(String expression) {
return entityManager.createNativeQuery("SELECT * FROM item i WHERE i.jsonb_content#>>'{string_value}' LIKE '" + expression + "'", Item.class).getResultList();
}
In the above example, there is the usage of the #>>
operator that extracts the JSON sub-object at the specified path as text (please check the Postgres documentation for more details).
In most cases, such a query (of course, with an escaped value) is enough. However, if we need to implement the creation of some kind of dynamic query based on parameters passed in our API, it would be better some kind of criteria builder.
Posjsonhelper
Hibernate 5 by default does not have support for Postgres JSON functions. Fortunately, you can implement it by yourself or use the posjsonhelper library which is an open-source project.
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>hibernate5</artifactId>
<version>0.1.0</version>
</dependency>
To use the posjsonhelper
library in your project, you need to use the Postgres dialect implemented in the project. For example:
com.github.starnowski.posjsonhelper.hibernate5.dialects.PostgreSQL95DialectWrapper ...
In case your project already has a custom dialect class, then there is also the possibility of using:
com.github.starnowski.posjsonhelper.hibernate5.PostgreSQLDialectEnricher;
Using Criteria Components
The example below has similar behavior to the previous example that used a native query. However, in this case, we are going to use a criteria builder.
private EntityManager entityManager;
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((CriteriaBuilderImpl) cb, singletonList("string_value"), root.get("jsonbContent")), expression));
return entityManager.createQuery(query).getResultList();
}
Hibernate is going to generate the SQL code as below:
select
item0_.id as id1_0_,
item0_.jsonb_content as jsonb_co2_0_
from
item item0_
where
jsonb_extract_path_text(item0_.jsonb_content,?) like ?
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 like:
?&
- Checks if all of the strings in the text array exist as top-level keys or array elements. So generally if we have JSON property that contains an array then you can check if it contains all elements that you are searching by.?|
- Checks if any of the strings in the text array exist as top-level keys or array elements. So generally if we have JSON property that contains an array then you can check if it contains at least of elements that you are searching by.
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
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 searching by.
private EntityManager entityManager;
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, (CriteriaBuilderImpl) cb, new JsonBExtractPath((CriteriaBuilderImpl) cb, singletonList("top_element_with_set_of_values"), root.get("jsonbContent")), tags.toArray(new String[0])));
return entityManager.createQuery(query).getResultList();
}
In case the tags would contain two elements, then Hibernate would generate the below SQL:
select
item0_.id as id1_0_,
item0_.jsonb_content as jsonb_co2_0_
from
item item0_
where
jsonb_all_array_strings_exist(jsonb_extract_path(item0_.jsonb_content,?), array[?,?])=true
"?|" Wrapper
The below code example illustrates how to create a query that looks at records for which JSON property that contains an array has at least one string element that we are searching by.
private EntityManager entityManager;
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, (CriteriaBuilderImpl) cb, new JsonBExtractPath((CriteriaBuilderImpl) cb, singletonList("top_element_with_set_of_values"), root.get("jsonbContent")), tags.toArray(new String[0])));
return entityManager.createQuery(query).getResultList();
}
In case the tags would contain two elements then Hibernate would generate the below SQL:
select
item0_.id as id1_0_,
item0_.jsonb_content as jsonb_co2_0_
from
item item0_
where
jsonb_any_array_strings_exist(jsonb_extract_path(item0_.jsonb_content,?), array[?,?])=true
For more examples of how to use numeric operators please check the demo dao object and dao tests.
Conclusion
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.
Opinions expressed by DZone contributors are their own.
Comments