Thanks for visiting DZone today,
Please enter at least three characters to search
Join the DZone community and get the full member experience.
Join For FreePostgres database supports a fewJSON types and special operations for those types.
In some cases, those operations might be a good alternative for document databases likeMongoDB or otherNoSQL 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 useHibernate framework with version 5.
Our model looks like the example below:
CORE@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 thejsonbContent 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 jsonINSERT INTO item (id, jsonb_content) VALUES (6, '{}');-- int valuesINSERT 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 valuesINSERT 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 valuesINSERT 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 valuesINSERT 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 elementsINSERT 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"]}}');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 theLIKE operator. Of course, for such action, we should use parameters andPreparedStatement.
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 thePostgres 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.
Hibernate 5 by default does not have support forPostgres JSON functions. Fortunately, you can implement it by yourself or use theposjsonhelper 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 theposjsonhelper library in your project, you need to use thePostgres 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;
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 ?Thejsonb_extract_path_text is a Postgres function that is equivalent to the #>> operator (please check the Postgres documentation linked earlier for more details).
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.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."
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[?,?])=trueThe 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[?,?])=trueFor more examples of how to use numeric operators please check the demodao object anddao tests.
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.
The likes didn't load as expected. Please refresh the page and try again.
CONTRIBUTE ON DZONE
LEGAL
CONTACT US