CODEWITHSUNDEEP
javapostgresqljsonbmicronautmicronaut-data
Den B
Den B

Reputation: 921

Micronaut JSONB field

I have micronaut application using Postgres DB. Recently I needed to add JSONB field to my table.

But I couldn't find any working solution for my case. So here's my code:

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import jakarta.inject.Singleton;
import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;
import lombok.RequiredArgsConstructor;

import java.io.IOException;
import java.util.List;

@Singleton
@RequiredArgsConstructor
@Converter(autoApply = true)
public class JsonbConverter implements AttributeConverter<List<Metadata>, String> {

    private final ObjectMapper objectMapper;

    @Override
    public String convertToDatabaseColumn(List<Metadata> attribute) {
        try {
            return attribute == null ? null : objectMapper.writeValueAsString(attribute);
        } catch (JsonProcessingException e) {
            throw new RuntimeException("Error converting to JSON", e);
        }
    }

    @Override
    public List<Metadata> convertToEntityAttribute(String dbData) {
        try {
            return dbData == null ? null : objectMapper.readValue(dbData,
                objectMapper.getTypeFactory().constructCollectionType(List.class, Metadata.class));
        } catch (IOException e) {
            throw new RuntimeException("Error parsing JSON", e);
        }
    }
}

Here's my Metadata. By the way, I tried Map<String, String> and Map<String, Object>. Nothing worked.

@Data
@Introspected
@JsonInclude(JsonInclude.Include.NON_NULL)
@Schema(title = METADATA_TITLE, description = METADATA_DESCRIPTION)
public class Metadata {

    @NotBlank
    @Size(min = 1, max = 1000)
    private String field;

    @NotBlank
    @Size(min = 1, max = 1000)
    private String value;

    @JsonCreator
    public Metadata(@JsonProperty("field") String field,
                    @JsonProperty("value") String value) {
        this.field = field;
        this.value = value;
    }

}

And here's my entity object where I store my fields:

@Value
@Embeddable
class StoredIndexingDetails {

 
    @Nullable
//    @Convert(converter = JsonbConverter.class)
    @TypeDef(type = DataType.JSON, converter = JsonbConverter.class)
//    @Column(name = "indexing_primary_metadata", columnDefinition = "jsonb")
    List<Metadata> primaryMetadata;
}

As mentioned previously, tried different approaches. Tried to add @Column(name = "indexing_primary_metadata", columnDefinition = "jsonb").

My error is next:

Error executing PERSIST: ERROR: column \"indexing_primary_metadata\" is of type jsonb but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.

I also tried to add @ColumnTransformer(write = "?::jsonb") Code didn't throw error, but in db field was null. Any suggestions?

Upvotes: 0

Views: 69

Answers (1)

Den B
Den B

Reputation: 921

I've managed to resolve the issue. So, basically, my solution with @TypeDef(type = DataType.JSON, converter = JsonbConverter.class) seems to be working. The reason I received null in my table was because in my code this field was null because of the issue with nested entity.

For those, who tries to find solution:

@Column(columnDefinition = "json", name = "test_field")
@TypeDef(type = DataType.JSON, converter = JsonbConverter.class)
final List<Metadata> testField;

The above should work. The code for JsonbConverter is in question.

Upvotes: 0

Related Questions