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 FreeThis is another article in the series related to supporting the Postgres JSON functionsin 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 likeMongoDB 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 forJSON. 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:
For the article, let's assume that our database has a table called theitem, which has a column with JSON content, like in the below example:
COREcreate table item ( id int8 not null, jsonb_content jsonb, primary key (id) )We also might have some test data:
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 jsonINSERT INTO item (id, jsonb_content) VALUES (6, '{}');-- int valuesINSERT INTO item (id, jsonb_content) VALUES (7, '{"integer_value": 132}');-- 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}');-- enum valuesINSERT INTO item (id, jsonb_content) VALUES (13, '{"enum_value": "SUPER"}');-- string valuesINSERT INTO item (id, jsonb_content) VALUES (18, '{"string_value": "the end of records"}');Like in otherJava 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.
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 ourEntity 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 oneupdate 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.
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 projectadds 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 attachFunctionContributor.
All examples use Java entity class that represents theitem table, whose definition was mentioned above:
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; }}Thejsonb_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".
// 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:
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=?The wrapper for theconcatenation 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:
// 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 thechild property with the already stored JSON object in the database.
This code generates such a SQL query:
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=?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.
// 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:
update item set jsonb_content=(jsonb_content #- ?::text[]) where id=?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.
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.
// 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:
update item set jsonb_content=jsonb_set(jsonb_content, ?::text[], remove_values_from_json_array(jsonb_extract_path(jsonb_content, ?), ?::jsonb)) where id=?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.
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 fourjsonb_set function executions and twodelete operations. The most nesteddelete 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 anUPDATE statement, it might not be readable from a code perspective. Fortunately, Posjsonhelper has a builder component that makes building such a complex statement easy.
TheHibernate6JsonUpdateStatementBuilder type allows the construction of update statements with multiple operations that modify JSON and rely on each other.
Below is a code example:
// 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.
The Builder component allows to:
To know more about how the builder works, please check thedocumentation.
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.
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