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
– user3738870, Commented Dec 2, 2022 at 10:07

Lorena · Accepted Answer · 2022-12-02 12:13:52Z

6

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,
)

answered Dec 2, 2022 at 12:13

Lorena

1131 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related