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.

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.