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

Postgresql – How can I map arbitrary json field to a JSON column in postgres using Java JPA repositories?

I have an entity with specific fields, each of which is mapped to columns in a postgres database. I am using a JpaRepository to persist my entity. I have one column, called info, that can be arbitrary json, and is stored in a column of type JSON.

I have tried using the data types JSONObject from simple json library and JsonNode from jackson, but they are unable to be mapped to a proper JDBC type.

Is there a correct way to map arbitary json to postgres json column? I can change the datatype of info, but not of the database column.

The error I usually see is

 Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode`

Edit

I found a workaround converting between JsonNode and String, as seen here

  @Column(name = "info", columnDefinition = "JSON")
  @JsonIgnore
  private String infoString;

  @Transient
  @JsonProperty("info")
  @Schema(example = "{}", description = "additional information")
  private JsonNode info;

  private void infoToInfoString() {
    infoString = info.toString();
  }

  private void infoStringToInfo() throws JsonProcessingException {
    info = objectMapper.readTree(infoString);
  }
Tags:

2

Answers


  1. 0

    Mapping arbitrary JSON fields to a JSON column in PostgreSQL using JPA repositories can be a bit tricky due to the dynamic and unstructured nature of JSON data.

    1. Define your Entity Class
    2. Create a Converter: You need to define a converter that converts between your Java type (JsonNode) and the database type (JSON).
    3. Use JPA Repository: You can now use your JpaRepository to persist and retrieve entities containing arbitrary JSON data
    Login or Signup to reply.
  2. 0

    You can use a converter as mentioned. Here’s a sample implementation which should work.

    @Entity
public class InfoEntity {

  @Convert(converter = InfoAttributeConverter.class)
  @Column(name = "info", columnDefinition = "JSON")
  private Map<String, String> infoString;
}

@Converter(autoApply =true)
public class InfoAttributeConverter {

// you can use Jackson object mapper here
private final ObjectMapper objectMapper;
    
   // This coverts the Java Map to json key value pair before saving
   @Override
   public String convertToDatabaseColumn(Map infoAttributes) {
     return objectMapper.writeValueAsString(infoAttributes)
    }

    // This converts the json data in column to Map
    @Override
    public Map<String, String> convertToEntityAttribute(String dbData) {
     return objectMapper.readValue(dbData, new TypeReference<HashMap<String, String>>() {});
     }

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

Back To Top
Search