Postgresql Jsonb Java Hibernate 6 JdbcTypeCode

Question

To persist I use Hibernate 6 and use on the entity:

@JdbcTypeCode(SqlTypes.JSON)

private String value;

When you save to the database, save wrapped in quotes and inside the json escape the quotes:

"{\"name\":\"pepito\", \"lastname\":\"perez\"}"

when I use the query to select value -> 'name' does not work, I do not understand why it does not save in the database in the following way:

{"name":"pepito", "lastname":"perez"}

Thanks.

Answer 1

What you are seeing here is that Hibernate serializes the string as JSON. I guess it's not very useful to do that and Hibernate could instead interpret a string a plain JSON, but in the end, I would rather suggest you model this as java.util.Map instead, which is then serialized to the proper JSON representation that you expect.

Either way, I created a Hibernate issue (HHH-15458) for this as I understand that this is the way people would expect this to work when they don't care about the representation of the JSON structure in their model.

Answer 2

Hibernate ORM 6.1.3.Final have fix for this issue, strings annotated with @JdbcTypeCode(SqlTypes.JSON) and @JdbcTypeCode(SqlTypes.SQLXML) will no longer be serialized to JSON/XML. Instead, they will be interpreted as plain JSON/XML to avoid the overhead of serialization/deserialization.

Reference

https://in.relation.to/2022/09/08/hibernate-orm-613-final/

Answer 3

@Entity
  public class Entity {

  @JdbcTypeCode(SqlTypes.JSON)
  private JsonNode json;
}

@Test
@Commit
void shouldSaveJson() throws JsonProcessingException {
  ObjectMapper objectMapper = new ObjectMapper();
  JsonNode jsonMap = objectMapper.readValue(jsonEscapedString,JsonNode.class);
  Entity entity = new Entity(jsonMap);
  Entity saved = entityRepository.saveAndFlush(question);
  entityManager.clear();

  Entity fetched = entityRepository.findById(saved.getId()).orElse(null);
  Assertions.assertNotNull(fetched);
}

Instead of JsonNode you can use Map<String, Object>