Spring Data Rest - PATCH Postgres jsonb field

Question

Short version: how to fix the JSON object contained in the Postgres jsonb field using the Spring Data Rest PATCH method?

Here is the long version, please consider the following object:

 @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 type Postgres jsonb . These getter / setter is a way to serialize / deserialize it for the Spring Data Rest mentioned here . We also tried to provide a field for our type, as mentioned in these answers .

Our goal is to fix 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 Result:

 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 fixes the Example resource and redefines the value for each requested attribute instead of trying to insert only the correction of the requested nested properties into the properties of the JSON object.

This is when we thought that media support application/merge-patch+json and application/json-patch+json would matter Spring. 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 boils down to the same idea: only the attributes of the entity are scanned and either completely redefined or not found.

The question is this: is there a way for Spring Data Rest to understand that it is dealing with a jsonb field and therefore is looking for nested JSON properties, not just searching for entity attributes?

Nb: @Embeddable/@Embedded Annotations are likely to be eliminated, as they imply knowledge of the names of nested properties, which will reduce interest for the jsonb field.

Thanks for reading.

Answer 1

well, your EntityManager does not know that inside your jsonObject field there is some kind of structure for which it is a blank line. You must implement your own workarounds. One example of how you can get started is https://github.com/bazar-nazar/pgjson But this approach will require you to read an object from the database each time and do another serialize / deserialize roundtrip.

BUT IF you are in postgresql, you can use all your power (note: this will make your application closely related to postgresql, and therefore the database will be more difficult to replace)

I would suggest implementing custom jdbc queries as a 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 a jsonobjet field of a predefined type and, therefore, it can be replaced with a pure normalized entity and, therefore, not much can be done with it. The second approach does not force you to have any structure inside your json.

hope this helps you.

Answer 2

Hibernate 5 is assumed to be used as a JPA implementation.

Create a jsonobject field for a specific class type (with the required fields) instead of String .

Then you can add a custom Hibernate type for 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 pretty verbose, but essentially it uses the Jackson ObjectMapper to pass the object as a String to a JDBC statement (and vice versa when retrieving from a 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 say that hibernate stores Java objects as jsonb Postgre. This means creating your custom dialect class (and customizing it).

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

With all this, you should be fine and Spring's Data Rest fix should work.

PS

The answer is strongly inspired by this github repo, which does pretty much the same, but with Hibernate 4. Take a look at this.