Postgres JSON Functions With Hibernate 5

Tutorials
Hibernate PostgreSQL

Postgres 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:

Java

  

@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:

SQL

  

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:

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"]}}');

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.

Java

  


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.

XML

  

        <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.

Java

  

	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:

SQL

  

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.

PLSQL

  

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.

Java

  

    
	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:

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.

Java

  

	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:

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.

Source:
https://dzone.com/articles/postgres-json-functions-with-hibernate-5