Modify JSON Data in Postgres and Hibernate 6

In this series on supporting the Postgres JSON functions in a project using the Hibernate framework, learn about modification operations on JSON records.

Szymon Tarnowski

CORE ·

Oct. 09, 24 · Tutorial

Likes (6)

Comment

Save

10.2K Views

This is another article in the series related to supporting the Postgres JSON functions in a project using the Hibernate framework with version 6. The topic for the article is modification operations on JSON records. As in the previous article, it is worth mentioning that Postgres might now have such comprehensive operations as other NoSQL databases like MongoDB for JSON modification (although, with the proper function constructions, it is possible to achieve the same effect). It still suits most projects that require JSON modification. Plus, with transaction support (not support in a NoSQL database at such a level), it is a pretty good idea to use Postgres with JSON data. Of course, NoSQL databases have other benefits that might suit better projects.

There are generally many articles on Postgres' support for JSON. This article focuses on integrating this support with the Hibernate 6 library.

In case someone is interested in querying JSON data or text search using Postgres and Hibernate, please see the below links:

Test Data

For the article, let's assume that our database has a table called the item, which has a column with JSON content, like in the below example:

    SQL
   
 

   create table item (
       id int8 not null,
        jsonb_content jsonb,
        primary key (id)
    )
  

We also might have some test data:

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

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

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

-- enum values
INSERT INTO item (id, jsonb_content) VALUES (13, '{"enum_value": "SUPER"}');

-- string values
INSERT INTO item (id, jsonb_content) VALUES (18, '{"string_value": "the end of records"}');
  

Native SQL Execution

Like in other Java frameworks, with Hibernate, you can run native SQL queries — which is well documented and there are a lot of examples on the internet. That is why in this article, we won't focus on native SQL operation execution. However, there will be examples of what kind of SQL the JPA operations generate. Because Hibernate is a JPA implementation, it makes sense to show how JPA API can modify JSON data in the Postgres database.

Modify JSON Object Properties and Not the Entire JSON Object (Path)

Setting the whole JSON payload for one column is easy and does not require much explanation. We just set the value for the property in our Entity class, which represents a column with JSON content.

It is similar to setting single or multiple properties for JSON for one database row. We just read the table row, deserialize the JSON value to a POJO representing a JSON object, set values for particular properties, and update the database records with the whole payload. However, such an approach might not be practical when we want to modify JSON properties for multiple database rows.

Suppose we have to do batch updates of particular JSON properties. Fetching from the database and updating each record might not be an effective method.

It would be much better to do such an update with one update statement where we set values for particular JSON properties. Fortunately, Postgres has functions that modify JSON content and can be used in the SQL update statement.

Posjsonhelper

Hibernate has better support for JSON modification in version 7, including most of the functions and operators mentioned in this article. Still, there are no plans to add such support in version 6. Fortunately, the Posjsonhelper project adds such support for Hibernate in version 6. All the examples below will use the Posjsonhelper library. Check this link to find out how to attach a library to your Java project. You will also have to attach FunctionContributor.

All examples use Java entity class that represents the item table, whose definition was mentioned above:

    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;
    }
}
  

jsonb_set Function Wrapper

The jsonb_set function is probably the most helpful function when modifying JSON data is required. It allows specific properties for JSON objects and specific array elements to be set based on the array index.

For example, the below code adds the property "birthday" to the inner property "child".

    Java
   
 

   	// GIVEN
        Long itemId = 19L;
        String property = "birthday";
        String value = "1970-01-01";
        String expectedJson = "{\"child\": {\"pets\" : [\"dog\"], \"birthday\": \"1970-01-01\"}}";
        // when
        CriteriaUpdate<Item> criteriaUpdate = entityManager.getCriteriaBuilder().createCriteriaUpdate(Item.class);
        Root<Item> root = criteriaUpdate.from(Item.class);

        // Set the property you want to update and the new value
        criteriaUpdate.set("jsonbContent", new JsonbSetFunction((NodeBuilder) entityManager.getCriteriaBuilder(), root.get("jsonbContent"), new JsonTextArrayBuilder().append("child").append(property).build().toString(), JSONObject.quote(value), hibernateContext));

        // Add any conditions to restrict which entities will be updated
        criteriaUpdate.where(entityManager.getCriteriaBuilder().equal(root.get("id"), itemId));

        // Execute the update
        entityManager.createQuery(criteriaUpdate).executeUpdate();

        // then
        Item item = tested.findById(itemId);
        assertThat((String) JsonPath.read(item.getJsonbContent(), "$.child." + property)).isEqualTo(value);
        JSONObject jsonObject = new JSONObject(expectedJson);
        DocumentContext document = JsonPath.parse((Object) JsonPath.read(item.getJsonbContent(), "$"));
        assertThat(document.jsonString()).isEqualTo(jsonObject.toString());
  

This code would generate such a SQL statement:

    SQL
   
 

   update
        item 
    set
        jsonb_content=jsonb_set(jsonb_content, ?::text[], ?::jsonb) 
    where
        id=?
Hibernate: 
    select
        i1_0.id,
        i1_0.jsonb_content 
    from
        item i1_0 
    where
        i1_0.id=?
  

Concatenation Operator Wrapper "||"

The wrapper for the concatenation operator (||) concatenates two JSONB values into a new JSONB value.

Based on Postgres documentation, the operator behavior is as follows:

Concatenating two arrays generates an array containing all the elements of each input. Concatenating two objects generates an object containing the union of their keys, taking the second object's value when there are duplicate keys. All other cases are treated by converting a non-array input into a single-element array, and then proceeding as for two arrays. Does not operate recursively: only the top-level array or object structure is merged.

Here is an example of how to use this wrapper in your code:

    Java
   
 

           // GIVEN
        Long itemId = 19l;
        String property = "birthday";
        String value = "1970-01-01";

        // WHEN
        CriteriaUpdate<Item> criteriaUpdate = entityManager.getCriteriaBuilder().createCriteriaUpdate(Item.class);
        Root<Item> root = criteriaUpdate.from(Item.class);

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("child", new JSONObject());
        jsonObject.getJSONObject("child").put(property, value);
        criteriaUpdate.set("jsonbContent", new ConcatenateJsonbOperator((NodeBuilder) entityManager.getCriteriaBuilder(), root.get("jsonbContent"), jsonObject.toString(), hibernateContext));

        criteriaUpdate.where(entityManager.getCriteriaBuilder().equal(root.get("id"), itemId));

        entityManager.createQuery(criteriaUpdate).executeUpdate();

        // THEN
        Item item = tested.findById(itemId);
        assertThat((String) JsonPath.read(item.getJsonbContent(), "$.child." + property)).isEqualTo(value);
        JSONObject expectedJsonObject = new JSONObject().put(property, value);
        DocumentContext document = JsonPath.parse((Object) JsonPath.read(item.getJsonbContent(), "$.child"));
        assertThat(document.jsonString()).isEqualTo(expectedJsonObject.toString());
  

Code merge a JSON object with the child property with the already stored JSON object in the database.

This code generates such a SQL query:

    SQL
   
 

   update
        item 
    set
        jsonb_content=jsonb_content || ?::jsonb 
    where
        id=?
Hibernate: 
    select
        i1_0.id,
        i1_0.jsonb_content 
    from
        item i1_0 
    where
        i1_0.id=?
  

Delete the Field or Array Element Based on the Index at the Specified Path "#-"

The Posjsonhelper has a wrapper for the delete operation (#-). It deletes the field or array element based on the index at the specified path, where path elements can be either field keys or array indexes. For example, the below code removes from the JSON object property based on the "child.pets" JSON path.

    Java
   
 

           // GIVEN
        Item item = tested.findById(19L);
        JSONObject jsonObject = new JSONObject("{\"child\": {\"pets\" : [\"dog\"]}}");
        DocumentContext document = JsonPath.parse((Object) JsonPath.read(item.getJsonbContent(), "$"));
        assertThat(document.jsonString()).isEqualTo(jsonObject.toString());

        // WHEN
        CriteriaUpdate<Item> criteriaUpdate = entityManager.getCriteriaBuilder().createCriteriaUpdate(Item.class);
        Root<Item> root = criteriaUpdate.from(Item.class);

        // Set the property you want to update and the new value
        criteriaUpdate.set("jsonbContent", new DeleteJsonbBySpecifiedPathOperator((NodeBuilder) entityManager.getCriteriaBuilder(), root.get("jsonbContent"), new JsonTextArrayBuilder().append("child").append("pets").build().toString(), hibernateContext));

        // Add any conditions to restrict which entities will be updated
        criteriaUpdate.where(entityManager.getCriteriaBuilder().equal(root.get("id"), 19L));

        // Execute the update
        entityManager.createQuery(criteriaUpdate).executeUpdate();

        // THEN
        entityManager.refresh(item);
        jsonObject = new JSONObject("{\"child\": {}}");
        document = JsonPath.parse((Object) JsonPath.read(item.getJsonbContent(), "$"));
        assertThat(document.jsonString()).isEqualTo(jsonObject.toString());
  

The generated SQL would be:

    SQL
   
 

   update
        item 
    set
        jsonb_content=(jsonb_content #- ?::text[]) 
    where
        id=?
  

Delete Multiple Array Elements at the Specified Path

By default, Postgres (at least in version 16) does not have a built-in function that allows the removal of array elements based on their value. However, it does have the built-in operator, -#, which we mentioned above, that helps to delete array elements based on the index but not their value.

For this purpose, the Posjsonhelper can generate a function that must be added to the DDL operation and executed on your database.

    SQL
   
 

   CREATE OR REPLACE FUNCTION {{schema}}.remove_values_from_json_array(input_json jsonb, values_to_remove jsonb) RETURNS jsonb AS $$
DECLARE
    result jsonb;
BEGIN
    IF jsonb_typeof(values_to_remove) <> 'array' THEN
        RAISE EXCEPTION 'values_to_remove must be a JSON array';
    END IF;

    result := (
        SELECT jsonb_agg(element)
        FROM jsonb_array_elements(input_json) AS element
        WHERE NOT (element IN (SELECT jsonb_array_elements(values_to_remove)))
    );

    RETURN COALESCE(result, '[]'::jsonb);
END;
$$ LANGUAGE plpgsql;
  

One of the wrappers will use this function to allow the deletion of multiple values from the JSON array. This code removes a "mask" and "compass" elements for the "child.inventory" property.

    Java
   
 

   	// GIVEN
        Item item = tested.findById(24L);
        DocumentContext document = JsonPath.parse((Object) JsonPath.read(item.getJsonbContent(), "$"));
        assertThat(document.jsonString()).isEqualTo("{\"child\":{\"pets\":[\"crab\",\"chameleon\"]},\"inventory\":[\"mask\",\"fins\",\"compass\"]}");
        CriteriaUpdate<Item> criteriaUpdate = entityManager.getCriteriaBuilder().createCriteriaUpdate(Item.class);
        Root<Item> root = criteriaUpdate.from(Item.class);

        NodeBuilder nodeBuilder = (NodeBuilder) entityManager.getCriteriaBuilder();
        JSONArray toRemoveJSONArray = new JSONArray(Arrays.asList("mask", "compass"));
        RemoveJsonValuesFromJsonArrayFunction deleteOperator = new RemoveJsonValuesFromJsonArrayFunction(nodeBuilder, new JsonBExtractPath(root.get("jsonbContent"), nodeBuilder, Arrays.asList("inventory")), toRemoveJSONArray.toString(), hibernateContext);
        JsonbSetFunction jsonbSetFunction = new JsonbSetFunction(nodeBuilder, (SqmTypedNode) root.get("jsonbContent"), new JsonTextArrayBuilder().append("inventory").build().toString(), deleteOperator, hibernateContext);
        // Set the property you want to update and the new value
        criteriaUpdate.set("jsonbContent", jsonbSetFunction);

        // Add any conditions to restrict which entities will be updated
        criteriaUpdate.where(entityManager.getCriteriaBuilder().equal(root.get("id"), 24L));

        // WHEN
        entityManager.createQuery(criteriaUpdate).executeUpdate();

        // THEN
        entityManager.refresh(item);
        document = JsonPath.parse((Object) JsonPath.read(item.getJsonbContent(), "$"));
        assertThat(document.jsonString()).isEqualTo("{\"child\":{\"pets\":[\"crab\",\"chameleon\"]},\"inventory\":[\"fins\"]}");
  

Here is the SQL generated by the above code:

    SQL
   
 

   update
        item 
    set
        jsonb_content=jsonb_set(jsonb_content, ?::text[], remove_values_from_json_array(jsonb_extract_path(jsonb_content, ?), ?::jsonb)) 
    where
        id=?
  

Hibernate6JsonUpdateStatementBuilder: How To Combine Multiple Modification Operations With One Update Statement

All the above examples demonstrated the execution of a single operation that modifies JSON data. Of course, we can have update statements in our code that use many of the wrappers mentioned in this article together. However, being aware of how those operations and functions will be executed is crucial because it makes the most sense when the result of the first JSON operation is an input for the following JSON modification operations. The output for that operation would be an input for the next operation, and so on, until the last JSON modification operation.

To better illustrate that, check the SQL code.

    SQL
   
 

   update
        item 
    set
        jsonb_content=
        jsonb_set(
          jsonb_set(
            jsonb_set(
              jsonb_set(
                (
                  (jsonb_content #- ?::text[]) -- the most nested #- operator
                 #- ?::text[])
              , ?::text[], ?::jsonb) -- the most nested jsonb_set operation
            , ?::text[], ?::jsonb)
          , ?::text[], ?::jsonb)
        , ?::text[], ?::jsonb) 
    where
        id=?
  

This assumes that we have four jsonb_set function executions and two delete operations. The most nested delete operation is a first JSON modification operation because the original value from a column that stores JSON data is passed as a parameter.

Although this is the correct approach, and the existing wrapper allows the creation of such an UPDATE statement, it might not be readable from a code perspective. Fortunately, Posjsonhelper has a builder component that makes building such a complex statement easy.

The Hibernate6JsonUpdateStatementBuilder type allows the construction of update statements with multiple operations that modify JSON and rely on each other.

Below is a code example:

    Java
   
 

           // GIVEN
        Item item = tested.findById(23L);
                DocumentContext document = JsonPath.parse((Object) JsonPath.read(item.getJsonbContent(), "$"));
                assertThat(document.jsonString()).isEqualTo("{\"child\":{\"pets\":[\"dog\"]},\"inventory\":[\"mask\",\"fins\"],\"nicknames\":{\"school\":\"bambo\",\"childhood\":\"bob\"}}");
                CriteriaUpdate<Item> criteriaUpdate = entityManager.getCriteriaBuilder().createCriteriaUpdate(Item.class);
        Root<Item> root = criteriaUpdate.from(Item.class);

        Hibernate6JsonUpdateStatementBuilder hibernate6JsonUpdateStatementBuilder = new Hibernate6JsonUpdateStatementBuilder(root.get("jsonbContent"), (NodeBuilder) entityManager.getCriteriaBuilder(), hibernateContext);
        hibernate6JsonUpdateStatementBuilder.appendJsonbSet(new JsonTextArrayBuilder().append("child").append("birthday").build(), quote("2021-11-23"));
        hibernate6JsonUpdateStatementBuilder.appendJsonbSet(new JsonTextArrayBuilder().append("child").append("pets").build(), "[\"cat\"]");
        hibernate6JsonUpdateStatementBuilder.appendDeleteBySpecificPath(new JsonTextArrayBuilder().append("inventory").append("0").build());
        hibernate6JsonUpdateStatementBuilder.appendJsonbSet(new JsonTextArrayBuilder().append("parents").append(0).build(), "{\"type\":\"mom\", \"name\":\"simone\"}");
        hibernate6JsonUpdateStatementBuilder.appendJsonbSet(new JsonTextArrayBuilder().append("parents").build(), "[]");
        hibernate6JsonUpdateStatementBuilder.appendDeleteBySpecificPath(new JsonTextArrayBuilder().append("nicknames").append("childhood").build());

        // Set the property you want to update and the new value
        criteriaUpdate.set("jsonbContent", hibernate6JsonUpdateStatementBuilder.build());

        // Add any conditions to restrict which entities will be updated
        criteriaUpdate.where(entityManager.getCriteriaBuilder().equal(root.get("id"), 23L));

        // WHEN
        entityManager.createQuery(criteriaUpdate).executeUpdate();

        // THEN
        entityManager.refresh(item);
        document = JsonPath.parse((Object) JsonPath.read(item.getJsonbContent(), "$"));
        assertThat(document.jsonString()).isEqualTo("{\"child\":{\"pets\":[\"cat\"],\"birthday\":\"2021-11-23\"},\"parents\":[{\"name\":\"simone\",\"type\":\"mom\"}],\"inventory\":[\"fins\"],\"nicknames\":{\"school\":\"bambo\"}}");

  

The SQL statement that was mentioned previously was generated by this code.

To know more about how the builder works, please check the documentation.

Conclusion

Postgres database has a wide range of possibilities regarding JSON data modification operations. This leads us to consider Postgres a good document storage solution choice. So if our solution does not require higher read performance, better scaling, or sharding (although all those things could be achieved with Postgres database, especially with solutions provided by cloud providers like AWS), then is it worth considering storing your JSON documents in Postgres database — not to mention transaction support with databases like Postgres.

JSON Data (computing) Hibernate Property (programming) PostgreSQL Data Types

Opinions expressed by DZone contributors are their own.

Related

Trending