Spring Data Rest - PATCH Postgres jsonb field

Question

The short version is: How to patch the JSON object contained in a Postgres jsonb field using Spring Data Rest PATCH method?

Here comes the long version, please consider the following entity:

@Entity
@Table(name = "examples")
public class Example {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String jsonobject;

    @JsonRawValue
    public String getJsonobject() {
        return jsonobject == null ? null : jsonobject;
    }

    public void setJsonobject(JsonNode jsonobject) {
        this.jsonobject = jsonobject == null ? null : jsonobject.toString();
    }
}

jsonobject is of Postgres type jsonb. These getter/setter are the way to serialize/deserialize it for Spring Data Rest mentioned here. We also tried to give the field its own Type, as mentioned in these answers.

Our goal is to patch the JSON object this field contains, using Spring Data Rest.

For example:

GET /examples/1
{
    "id": 1,
    "jsonobject": {
         "foo": {"bar": "Hello"},
         "baz": 2
    }
}

PATCH /examples/1
{
    "jsonobject": {
        "foo": {"bar": "Welcome"}
    }
}

Expected output:

GET /examples/1
{
    "id": 1,
    "jsonobject": {
         "foo": {"bar": "Welcome"},
         "baz": 2
    }
}

Current output:

GET /examples/1
{
    "id": 1,
    "jsonobject": {
         "foo": {"bar": "Welcome"}
    }
}

Spring Data Rest patches the Example resource and overrides the value for each requested attribute, instead of trying to dig into the JSON object's properties to only patch the requested nested properties.

This is when we thought Spring Data Rest support of application/merge-patch+json and application/json-patch+json media types would come in handy. Here are the outputs for each media type:

application/merge-patch+json:

PATCH /examples/1
{
    "jsonobject": {
        "foo": {"bar": "Welcome"}
    }
}

Output:

GET /examples/1
{
    "id": 1,
    "jsonobject": {
         "foo": {"bar": "Welcome"}
    }
}

application/json-patch+json:

PATCH /examples/1
[
    { "op": "replace", "path": "/jsonobject/foo/bar", "value": "Welcome" }
]

Output:

{
    "cause": {
        "cause": null,
        "message": "EL1008E:(pos 8): Property or field 'foo' cannot be found on object of type 'java.lang.String' - maybe not public?"
    },
    "message": "Could not read an object of type class com.example.Example from the request!; nested exception is org.springframework.expression.spel.SpelEvaluationException: EL1008E:(pos 8): Property or field 'foo' cannot be found on object of type 'java.lang.String' - maybe not public?"
}

Which comes down to the same idea: only entity attributes are looked up, and either overridden entirely or not found.

The question is the following: is there a way to have Spring Data Rest understand it is dealing with a jsonb field and therefore look for JSON nested properties rather than only looking up the entity attributes?

Nb: @Embeddable/@Embedded annotations are most likely to be avoided, since they imply knowing the nested property names, which would lower the interest for a jsonb field.

Thank you for reading.

Hysteria

Answer 1

well, your EntityManager doesn't knows that there is some structure inside of your jsonObject field, which is pure string for it. You should implement your own workarounds. One example of how you can start working is here https://github.com/bazar-nazar/pgjson But such approach will require you each time read the object from database, and make another serialize/deserialize roundtrip.

BUT IF you are on postgresql, you can use all its power (note: this will make your application tightly coupled with postgresql, and thus database will become harder to replace)

I would suggest to implement custom jdbc queries, like simple example:

public static class JsonPatchRequest {
    String path;
    String operation;
    String value;
}


@Inject
private JdbcTemplate jdbcTemplate;

@PatchMapping(value = "/example/{id}") 
public void doPatch(@PathVariable("id") Long id, @RequestBody JsonPatchRequest patchRequest) {
    // this line should transform your request path from  "/jsonobject/foo/bar"  to "{foo,bar}" string
    String postgresqlpath = "{" + patchRequest.path.replaceFirst("/jsonobject/", "").replaceAll("/", ",") + "}";

    switch(patchRequest.operation) {
        case "replace" :
            jdbcTemplate.execute("UPDATE example SET jsonobject = jsonb_set(jsonobject, ?, jsonb ?) WHERE id = ?", new PreparedStatementCallback<Void>() {
                @Override
                public Void doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    ps.setString(1, postgresqlpath);

                    // this one transforms pure value, to string-escaped value (manual workaround)   so  'value' should become '"value"'
                    ps.setString(2, "\"".concat(patchRequest.value).concat("\""));

                    ps.setLong(3, id);

                    ps.execute();
                    return null;
                }
            });
            break;
        case "delete" :
            jdbcTemplate.execute("UPDATE example SET jsonobject = jsonobject #- ? WHERE id = ? ", new PreparedStatementCallback<Void>() {
                @Override
                public Void doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    ps.setString(1, postgresqlpath);
                    ps.setLong(2, id);
                    ps.execute();
                    return null;
                }
            });
            break;
    }
}

also note: the first approach will force you to make the jsonobjet field of predefined type, and thus it can be replaced with pure normalized entity, and so not much to do with it. The second approach doesn't force you to have any kind of structure inside of your json.

hope this will help you.

Autoclave

Yes, we are going for a likewise solution. The only issue being PostgreSQL, even in 9.6, does not really get the "JSON merge patch" RFC either. jsonb_set answers the problem but needs to be performed for each nested property. We wish the concatenate || operator did not blindly overwrite missing properties, that would be way more useful. We'll accept your answer after a reply from David Siro. – Hysteria

I guess in this case you could go with PL/v8 and create custom function to perform such patching. using already established solutions (like github.com/Starcounter-Jack/JSON-Patch) – Autoclave

Answer 2

Assumes Hibernate 5 is used as JPA implementation

Make your jsonobjectfield to be a specific class type (with fields you want) instead of String.

Then you can add a custom Hibernate user type for a jsonb types.

@Entity
@Table(name = "examples")
public class Example {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Basic
    @Type(type = "com.package.JsonObjectType")
    private JsonObject jsonobject;
}

The custom type implementation is quite verbose, but essentially it uses Jackson's ObjectMapper to pass the the object as String into JDBC statement (and vice versa when retrieving from ResultSet).

public class JsonObjectType implements UserType {

    private ObjectMapper mapper = new ObjectMapper();

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

    @Override
    public Class<JsonObject> returnedClass() {
        return JsonObject.class;
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
        final String cellContent = rs.getString(names[0]);
        if (cellContent == null) {
            return null;
        }
        try {
            return mapper.readValue(cellContent.getBytes("UTF-8"), returnedClass());
        } catch (final Exception ex) {
            throw new HibernateException("Failed to convert String to Invoice: " + ex.getMessage(), ex);
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.OTHER);
            return;
        }
        try {
            final StringWriter w = new StringWriter();
            mapper.writeValue(w, value);
            w.flush();
            st.setObject(index, w.toString(), Types.OTHER);
        } catch (final Exception ex) {
            throw new HibernateException("Failed to convert Invoice to String: " + ex.getMessage(), ex);
        }
    }

    @Override
    public Object deepCopy(final Object value) throws HibernateException {
        try {
            // use serialization to create a deep copy
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            ObjectOutputStream oos = new ObjectOutputStream(bos);
            oos.writeObject(value);
            oos.flush();
            oos.close();
            bos.close();

            ByteArrayInputStream bais = new ByteArrayInputStream(bos.toByteArray());
            return new ObjectInputStream(bais).readObject();
        } catch (ClassNotFoundException | IOException ex) {
            throw new HibernateException(ex);
        }
    }

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

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

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

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

    @Override
    public boolean equals(final Object obj1, final Object obj2) throws HibernateException {
        if (obj1 == null) {
            return obj2 == null;
        }
        return obj1.equals(obj2);
    }

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

Finally, you need to tell hibernate to store Java objects as jsonb Postgre type. That means creating your custom dialect class (and configure that in).

public class MyPostgreSQL94Dialect extends PostgreSQL94Dialect {

    public MyPostgreSQL94Dialect() {
        this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
    }
}

With all of that, you should be fine and Spring Data Rest patching mechanism should work.

PS

Answer heavily inspired by this github repo, which does essentially the same, but with Hibernate 4. Take a look at that.

Ovalle

Hello David. What is the JsonObject class? You wrote "with fields you want", so does your answer imply knowing the property names of the stored JSON object? – Hysteria

The GitHub repo answers the comment: their MyJson class has two fields to store the JSON properties. We do not want this behavior: the sole interest of the jsonb field is to be able to store whatever data as long as it is a proper JSON object. So the end user, Spring Data Rest API consumer, should be able to store objects with any property, as long as they are serializable into JsonObject, Gson or other JSON implementation. Having static properties on this class makes the jsonb type useless, at least for us. Thank you for your time anyway :) – Hysteria