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