2

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

Improve this question

3 Answers 3

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I have tried creating a POJO of Items but the result inside the database is still the same also using the Map i get the same result everything is stored inside the databased using escapes "{\"name\":\"John\",\"age\":30,\"city\":\"New York\"}". I will also add this to the question
Are u using quarkus-hibernate-reactive-panache or quarkus-hibernate-orm-panache?
I'm using quarkus-hibernate-reactive-panache
Then refer to this stackoverflow.com/questions/73471277/…
Okay im not sure exactly how i will do that because i will probably need to manipulate the converter class as well but i'm marking this as resolved because i asked for a simple json and the reference to the other post helped me alot
|
0

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;
    }
}
Improve this answer

1 Comment

From what package do you get JSONParser and JSONObject?
0

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

Improve this answer

2 Comments

I have also tried this JsonObject jsonObject = Json.createObjectBuilder() .add("name", "John") .add("age", 30) .add("city", "New York") .build(); OfferEntity f = new OfferEntity(); f.setItems(jsonObject); return update("update OfferEntity o set o.items = ?2 where o.id = ?1", offer.getId(), f.getItems()).onItem().transform(ignored -> offer); But inside the database, i get this ``` "{\"name\":{\"valueType\":\"STRING\"},\"age\":{\"valueType\":\"NUMBER\"}}" ```
Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.