Question 271399 in PostgreSQL Question posted in
The official documentation can be found here.

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

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 = ?2 where o.id = ?1", offer.getId(), 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 = "offer")
@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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search