Storing JSON data as JSONB in PostgreSQL with Quarkus Panache is stored as escaped string

Question

I'm working on a project that uses Quarkus with Panache and PostgreSQL, and I'm trying to store JSON data as JSONB in the database. However, when I insert JSON data into the database, it gets escaped, and I'd like to store it without escaping in order to be able to query the JSON from the postgress.

Here's my code for inserting the JSON data:

JsonObject jsonObject = Json.createObjectBuilder()
        .add("name", "John")
        .add("age", 30)
        .add("city", "New York")
        .build();
String jsonString = jsonObject.toString(); // Convert JsonObject to JSON String
return update("update OfferEntity o set o.items = ?1",  jsonString).onItem().transform(ignored -> offer);

However, in the PostgreSQL database, the inserted field looks like this:

"{\"name\":\"John\",\"age\":30,\"city\":\"New York\"}"

I want it to be stored as:

{"name":"John","age":30,"city":"New York"}

This is my entity class for OfferEntity:

@Entity
@Table(name = "offering")
@Cacheable
@WithSession
public class OfferEntity extends PanacheEntityBase {

    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "items", columnDefinition = "jsonb", insertable = false, updatable = false)
    private String items;

    public String getItems() {
        return items;
    }

    public void setItems(String items) {
        this.items = items;
    }
}

I'm using the latest Panache dependencies. How can I modify my code or configuration to store the JSON data as JSONB in PostgreSQL without escaping it? Please note that the framework can read the values from the database even if they are stored escaped but i also want to search them with query where this is not possible because the JSON is broken with all those escapes

UPDATE

I have also tried the following cases but the result is the same i get the values inside the database as

"{\"name\":\"John\",\"age\":30,\"city\":\"New York\"}"

First i tried this

public class Item implements Serializable {
    private String name;
    private int age;
    private String city;//with getters setters
}

and OfferEntity class looked like this

    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "items", columnDefinition = "jsonb", insertable = false, updatable = false)
    private Item items;

    public Item getItems() {
        return items;
    }

    public void setItems(Item items) {
        this.items = items;
    }

and I tried also make Items a map

    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "items", columnDefinition = "jsonb", insertable = false, updatable = false)
    private Map<String, String> items;

    public Map<String, String> getItems() {
        return items;
    }

    public void setItems(Map<String, String> items) {
        this.items = items;
    }

Is this a hibernate bug? The version I use for hibernate is the 6.2.7 final

Answer 1

I think hibernate doesn't support string doc as the standard mapping, try

public class OfferEntity extends PanacheEntityBase {

    @JdbcTypeCode(SqlTypes.JSON)
    private Map<String, String> items;
}

Or create your own POJO class

public class Items implements Serializable {
    private String name;
    private int age;
    private String city;
    //setters & getters
}

public class OfferEntity extends PanacheEntityBase {

    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "items", columnDefinition = "jsonb", insertable = false, updatable = false)
    private Items items;
}

Refer to this doc

UPDATED:

For hibernate-reactive using io.vertx.core.json.JsonObject refer to this post

Answer 2

You could try

@Convert(converter = ItemsConverter.class)
private JSONObject items;

being

@Converter
public class ItemConverter implements AttributeConverter<JSONObject, String> {
    @Override
    public String convertToDatabaseColumn(JSONObject data) {
        return data.toString();
    }

    @Override
    public JSONObject convertToEntityAttribute(String inputString) {
        JSONParser parser = new JSONParser();
        JSONObject r = new JSONObject();
        try {
            r = (JSONObject) parser.parse(inputString);
        } catch (ParseException e) {
            
        }
        return r;
    }
}

Answer 3

I think this should work if the type was JSONObject instead of String