Reading jsonb from Hibernate 6 native query

Question

The following code behaves differently between Hibernate 6.1 and 6.4 on postgresql:

var rows =
   session
     .createNativeQuery("SELECT CAST('[1,2,3]' as jsonb) as data, 2 as id", Object[].class)
      .addScalar("data")
      .addScalar("id", StandardBasicTypes.INTEGER)
      .list();
rows.forEach(row -> System.out.println(row[0].getClass() + " -> " + row[0]));

Output with 6.1:

class java.util.ArrayList -> [1, 2, 3]

Output with 6.4:

class java.lang.String -> [1, 2, 3]

In 6.1, it converts the jsonb column into an ArrayList; but on 6.4 it just reads it as a String. I tried passing in a bunch of different StandardBasicTypes and none of them made 6.4 behave like 6.1. So, how can I do this in 6.4?

Here is code to reproduce this: https://github.com/chadselph/hibernate-json-native-query

Answer 1

using UserType!

var rows =
   session
     .createNativeQuery("SELECT CAST('[1,2,3]' as jsonb) as data, 2 as id", Object[].class)
      .addScalar("data", JsonbType.class)
      .addScalar("id", StandardBasicTypes.INTEGER)
      .list();
rows.forEach(row -> System.out.println(row[0].getClass() + " -> " + row[0]));

*** JsonbType implements UserType ***

public class JsonbType implements UserType {

    private final ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public int getSqlType() {
        // Postgres specific type for jsonb
        return Types.OTHER;
    }

    @Override
    public Class returnedClass() {
        return ArrayList.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return Objects.equals(x, y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        assert (x != null);
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, int position, SharedSessionContractImplementor session, Object owner) throws SQLException{
        try {
            final String json = rs.getString(position);
            return json == null ? null : objectMapper.readValue(json, ArrayList.class);
        } catch (IOException e) {
            throw new HibernateException("Error converting jsonb to ArrayList", e);
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
            throws HibernateException, SQLException {
        try {
            if (value == null) {
                st.setNull(index, Types.OTHER);
            } else {
                st.setObject(index, objectMapper.writeValueAsString(value), Types.OTHER);
            }
        } catch (JsonProcessingException e) {
            throw new HibernateException("Error converting ArrayList to jsonb", e);
        }
    }

    @Override
    public Object deepCopy(Object value) {
        try {
            final String json = objectMapper.writeValueAsString(value);
            return objectMapper.readValue(json, ArrayList.class);
        } catch (IOException e) {
            throw new HibernateException("Error deep copying jsonb", e);
        }
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Serializable disassemble(Object value) {
        return  (Serializable) this.deepCopy(value);
    }

    @Override
    public Object assemble(Serializable cached, Object owner) {
        return this.deepCopy(cached);
    }

    @Override
    public Object replace(Object detached, Object managed, Object owner) {
        return this.deepCopy(detached);
    }

}