How to save a JsonNode param (micronaut + kotlin) as a jsonb in postgres database

Question

I'm trying to save an entity that has a JsonNode attribute and has to be converted to jsonb to be stores in a postgres database.

This is the database table:

CREATE TABLE IF NOT EXISTS configuration_data (
   id SERIAL NOT NULL PRIMARY KEY,
   namespace_name TEXT NOT NULL,
   dimension_id TEXT NOT NULL,
   dimension_value TEXT,
   path TEXT,
   data JSONB,
   version BIGINT DEFAULT 0
);

This is the entity:

@MappedEntity(value = "configuration_data")
data class ConfigurationDataEntity(
    @field:Id @GeneratedValue
    val id: Long,
    @MappedProperty("namespace_name")
    val namespaceName: String,
    @MappedProperty("dimension_id")
    val dimensionId: String,
    @MappedProperty("dimension_value")
    val dimensionValue: String,
    @MappedProperty("path")
    val path: String,
    @MappedProperty("data")
    val data: JsonNode,
    @MappedProperty("version")
    val version: Long,
)

And I'm trying to save an entity like that into the repository:

val entity = ConfigurationDataEntity(1L, coordinates.namespace, "acc", "abc", "path", data, 1L)

repository.save(entity)

The error I'm getting is the following:

Caused by: io.micronaut.data.exceptions.DataAccessException: Error executing PERSIST: Unable to set PreparedStatement value: Can't infer the SQL type to use for an instance of com.fasterxml.jackson.databind.node.ObjectNode. Use setObject() with an explicit Types value to specify the type to use.

I imagine it is because postgres can not store a jsonNode in a jsonb object, but I don't know how to convert a JsonNode to jsonb.

Thank you very much in advance :)

Have you tried adding `@TypeDef(type = DataType.JSON)` to the `data` field? — user3738870, Dec 02 '22 at 10:07

score 5 · Accepted Answer · answered Dec 02 '22 at 12:13

Worked adding this annotation to the field:

@field:TypeDef(type = DataType.JSON)

So the entity remains like:

@MappedEntity(value = "configuration_data")
data class ConfigurationDataEntity(
    @field:Id @GeneratedValue
    val id: Long,
    @MappedProperty("namespace_name")
    val namespaceName: String,
    @MappedProperty("dimension_id")
    val dimensionId: String,
    @MappedProperty("dimension_value")
    val dimensionValue: String,
    @MappedProperty("path")
    val path: String,
    @MappedProperty("data")
    **@field:TypeDef(type = DataType.JSON)**
    val data: JsonNode,
    @MappedProperty("version")
    val version: Long,
)

How to save a JsonNode param (micronaut + kotlin) as a jsonb in postgres database

1 Answers1