How do I configure a grails domain class attribute to be stored as (postgres 9.4) jsonb?

Question

I've tried to configure a domain class like this:

class Test {

    String data

    static constraints = {
    }

    static mapping = {
        data type: 'jsonb'
    }
}

This throws an exception (the reason, in the end, being Invocation of init method failed; nested exception is org.hibernate.MappingException: Could not determine type for: jsonb, at table: test, for columns: [org.hibernate.mapping.Column(data)]).

I also tried column: 'data', sqlType: 'jsonb', which creates a text column named data.

How do I correctly tell grails to use jsonb as the sql column type? Is it at all possible?

(The postgresql jdbc driver is used in version 9.4-1200.jdbc4 with hibernate 4.)

Decretal

Answer 1

To configure domain to map jsonb type to String you can:

1. Declare your own org.hibernate.usertype.UserType. Add to src/java:

   public class JSONBType implements UserType {
   
       @Override
       public int[] sqlTypes() {
           return new int[] { Types.OTHER };
       }
   
       @SuppressWarnings("rawtypes")
       @Override
       public Class returnedClass() {
           return String.class;
       }
   
       @Override
       public boolean equals(Object x, Object y) throws HibernateException {
           return (x != null) && x.equals(y);
       }
   
       @Override
       public int hashCode(Object x) throws HibernateException {
           return x.hashCode();
       }
   
       @Override
       public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor sessionImplementor, Object owner)
           throws HibernateException, SQLException {
           return rs.getString(names[0]);
       }
   
       @Override
       public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor sessionImplementor)
           throws HibernateException, SQLException {
           st.setObject(index, value, (value == null) ? Types.NULL : Types.OTHER);
       }
   
       @Override
       public Object deepCopy(Object value) throws HibernateException {
           if (value == null) return null;
           return new String((String)value);
       }
   
       @Override
       public boolean isMutable() {
           return false;
       }
   
       @Override
       public Serializable disassemble(Object value) throws HibernateException {
           return (Serializable)value;
       }
   
       @Override
       public Object assemble(Serializable cached, Object owner)
           throws HibernateException {
           return cached;
       }
   
       @Override
       public Object replace(Object original, Object target, Object owner)
           throws HibernateException {
           return deepCopy(original);
       }
   }
   

2. After that you can simply declare mapping in the domain:

   static mapping = {
       data type: "your.package.JSONBType", sqlType: "jsonb"
   }
   

Also you can map jsonb not to String, but directly to JSONObject or to your existing class or interface. In that case GORM will take responsibility for serializing/deserializing json and you no longer need do it explicitly in an application. Here is an example of such UserType implementation.

Obscurantism

Answer 2

You can use the Grails Postgresql Extensions plugin to use some Postgresql native types inside your domain classes.

At this moment the plugin supports the Json but not the Jsonb type. You have more information about the json support in the plugin documentation

DISCLAIMER: I'm one of the developers of the plugin.

Peripheral

As jsonb is not supported, this is, strictly speaking, not answering my question (no offense). I knew about postgresql-extensions; but grails doesn't need to do anything at all with the data in that field, it can treat its contents as text. It's just the database which should know it's jsonb. your input is still appreciated, thanks. – Lieb

It would be really nice, if you added a list of features that your plugin does not cover, but is implemented in postgres-sql. For Example: query on sub elements of a json-colum. – Center

Answer 3

Although, I'm answering this very late but I managed to achieve this with a very simple way which is working so smooth-

I created a custom Hibernate type which implements UserType:

package com.wizpanda.hibernate

import groovy.transform.CompileStatic
import org.grails.web.json.JSONObject
import org.hibernate.HibernateException
import org.hibernate.engine.spi.SessionImplementor
import org.hibernate.usertype.UserType

import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Types

/**
 * An implementation of {@link org.grails.web.json.JSONObject} column using Hibernate custom types.
 * https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#_custom_type
 * https://docs.jboss.org/hibernate/orm/current/javadocs/org/hibernate/usertype/UserType.html
 *
 * @author Shashank Agrawal
 */
@CompileStatic
class JSONObjectFooType implements UserType {

    @Override
    int[] sqlTypes() {
        return [Types.OTHER] as int[]
    }

    //@SuppressWarnings("rawtypes")
    @Override
    Class returnedClass() {
        return JSONObject.class
    }

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

    @Override
    int hashCode(Object x) throws HibernateException {
        return x.hashCode()
    }

    @Override
    Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        String value = rs.getString(names[0])
        if (!value) {
            return null
        }

        return new JSONObject(value)
    }

    @Override
    void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
        String valueToPersist

        if (value) {
            if (value instanceof JSONObject) {
                valueToPersist = value.toString()
            } else if (value instanceof String) {
                valueToPersist = new JSONObject(value).toString(0)
            } else {
                throw new HibernateException("Unknown type received for JSONObject based column")
            }
        }

        st.setObject(index, valueToPersist, Types.OTHER)
    }

    @Override
    Object deepCopy(Object value) throws HibernateException {
        if (!value) {
            return null
        }
        if (value instanceof JSONObject) {
            return new JSONObject(value.toString(0))
        }

        return value
    }

    @Override
    boolean isMutable() {
        return false
    }

    @Override
    Serializable disassemble(Object value) throws HibernateException {
        if (value instanceof JSONObject) {
            return value?.toString(0)
        }

        return value?.toString()
    }

    @Override
    Object assemble(Serializable cached, Object owner) throws HibernateException {
        if (!cached) {
            return null
        }

        return new JSONObject(cached.toString())
    }

    @Override
    Object replace(Object original, Object target, Object owner) throws HibernateException {
        return deepCopy(original)
    }
}

I'm using org.grails.web.json.JSONObject because this is internal from Grails, you can use others like org.json.JSONObject or Groovy json and replace the occurrences above.

Now, simple use this in your domain class-

class User {

    String email
    String name
    JSONObject settings

    static mapping = {
        settings type: JSONObjectFooType, sqlType: "text"
    }
}

Namaste!

Maverick

Answer 4

A bit late to the game, but for posterity here is my version of the solution that @jasp provided. The difference is that this solution will persist a Map object to a text column as JSON format. It uses the grails included Jackson libs.

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

import java.io.IOException;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Map;

public class JSONStringType implements UserType {
    private static final ObjectMapper _mapper = new ObjectMapper();

    @Override
    public int[] sqlTypes() {
        return new int[] { Types.VARCHAR };
    }

    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return String.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return (x != null) && x.equals(y);
    }

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

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
        try {
            String val = rs.getString(names[0]);
            return _mapper.readValue(val, Map.class);
        } catch (IOException e) {
            throw new HibernateException(e);
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
        try {
            String val;
            if (value == null)
                val = "{}";
            else if (value instanceof String)
                val = (String)value;
            else
                val = _mapper.writeValueAsString(value);
            st.setObject(index, val, (value == null) ? Types.NULL : Types.VARCHAR);
        } catch (JsonProcessingException e) {
            throw new HibernateException(e);
        }
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) return null;
        try {
            String val = _mapper.writeValueAsString(value);
            return val;
        } catch (JsonProcessingException e) {
            throw new HibernateException(e);
        }
    }

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

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable)value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner)
        throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
        throws HibernateException {
        return deepCopy(original);
    }
}

Usage:

import your.package.JSONStringType

class Book {
    String name
    String isbn
    Map attributes = [:]

    static constraints = {
    }

    static mapping = {
        attributes type: JSONStringType, sqlType: 'nvarchar(4000)'
    }
}

change sqlType to match your database column type. For SQL Server, nvarchar(4000) for efficient JSON document querying or nvarchar(MAX) for large JSON document storage.

Himyarite