CODEWITHSUNDEEP
javapostgresqlhibernatejpajsonb
Arihant Jain
Arihant Jain

Reputation: 221

PostgreSQL throws "column is of type jsonb but expression is of type bytea" with JPA and Hibernate

This is my entity class that is mapped to a table in postgres (9.4) I am trying to store metadata as jsonb type in the database

@Entity
@Table(name = “room_categories”)
@TypeDef(name = “jsonb”, typeClass = JsonBinaryType.class)
public class RoomCategory extends AbstractEntity implements Serializable {
    private String name;
    private String code;
    @Type(type = "jsonb")
    @Column(columnDefinition = "json")
    private Metadata metadata;

}

This is the metadata class:

public class Metadata implements Serializable {
    private String field1;
    private String field2;

}

I have used following migration file to add jsonb column:

databaseChangeLog:
– changeSet:
id: addColumn_metadata-room_categories
author: arihant
changes:
– addColumn:
schemaName: public
tableName: room_categories
columns:
– column:
name: metadata
type: jsonb

I am getting this error while creating the record in postgres: ERROR: column “metadata” is of type jsonb but expression is of type bytea Hint: You will need to rewrite or cast the expression.

This is the request body i am trying to persist in db:

  {
    “name”: “Test102”,
    “code”: “Code102”,
    “metadata”: {
    “field1”: “field11”,
    “field2”: “field12”
    }
    }

Please help how to convert bytea type to jsonb in java spring boot app

Upvotes: 22

Views: 47666

Answers (5)

Dmitri Algazin
Dmitri Algazin

Reputation: 3456

One more options which worked in out project,

then 'String metadata' field in Java dto,

and in Postgres schema it is defined as "metadata jsonb"

dependency "io.hypersistence:hypersistence-utils-hibernate-63:3.9.0"
https://mvnrepository.com/artifact/io.hypersistence/hypersistence-utils-hibernate-63

import io.hypersistence.utils.hibernate.type.json.JsonType;

@Type(JsonType.class)
@Column(columnDefinition = "jsonb")
private String metadata;

Upvotes: 1

PedroArs99
PedroArs99

Reputation: 167

Im coming a few years later but maybe useful for others:

In my case I fixed this problem by adding the following parameter to the connection string:

stringtype=unspecified

Upvotes: 1

Jonathan
Jonathan

Reputation: 883

if you use the JpaRespository save interface method, you can add the cast method above the issue property

@ColumnTransformer(write = "?::jsonb")
private Metadata metadata;

Upvotes: 20

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153730

The cause of the error

You could get this PostgreSQL:

ERROR: column “metadata” is of type jsonb but expression is of type bytea Hint: You will need to rewrite or cast the expression.

if you are executing a native SQL DML statement.

Native SQL DML statement

For instance, let's assume you want to do something like this:

int updateCount = entityManager.createNativeQuery("""
    UPDATE
        room_categories
    SET
        metadata = :metadata
    WHERE
        code = :code AND
        metadata ->> 'field1' is null            
    """)
.setParameter("code ", "123-ABC")
.setParameter(
    "metadata",
    new Metadata()
        .setField1("ABC")
        .setField2("123")
)
.executeUpdate();

The bytea type stands for byte array, and, since the Metadata type implements the Serializable interface, Hibernate falls back to using the SerializableType when no other type is more appropriate.

But, since you cannot bind a byte array to a jsonb column, PostgreSQL throws the aforementioned error.

The fix

To fix it, we have to set the JsonBinaryType explicitly using the Hibernate-specific setParameter Query method:

int updateCount = entityManager.createNativeQuery("""
    UPDATE
        room_categories
    SET
        metadata = :metadata
    WHERE
        code = :code AND
        metadata ->> 'field1' is null            
    """)
.setParameter("code ", "123-ABC")
.unwrap(org.hibernate.query.Query.class)
.setParameter(
    "metadata",
    new Metadata()
        .setField1("ABC")
        .setField2("123"),
    JsonBinaryType.INSTANCE
)
.executeUpdate();

First, we had to unwrap the JPA Query to a Hibernate org.hibernate.query.Query and call the setParameter method that takes a Hibernate Type instance.

Now, Hibernate will know that the metadata parameter needs to be handled by the JsonBinaryType, and not by the SerializableType.

Upvotes: 4

Ali Faradjpour
Ali Faradjpour

Reputation: 360

Simply convert your object by an ObjectMapper to a json string and then use (::jsonb) as cast to jsonb type:

INSERT INTO room_categories (name, code, metadata)
    VALUES (?, ?, ? ::jsonb );

(you will need to use native queries to query data stored as jsonb)

Upvotes: 18

Related Questions