שנה נתוני JSON ב-PGSQL וב-Hibernate 6

create table item (

       id int8 not null,

        jsonb_content jsonb,

        primary key (id)

    )

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

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;

    }

}

    // 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());

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=?

        // 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());

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=?

        // 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());

update

        item 

    set

        jsonb_content=(jsonb_content #- ?::text[]) 

    where

        id=?

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;

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

update

        item 

    set

        jsonb_content=jsonb_set(jsonb_content, ?::text[], remove_values_from_json_array(jsonb_extract_path(jsonb_content, ?), ?::jsonb)) 

    where

        id=?

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=?