Partial document update based on JSON Patch

[steve-chavez]

Problem

Currently there's no way to do a partial document update.

• Accept patch format for JSONB - set. filter for PATCH #465 (comment) discusses json patch but there are certain operations (like test) which don't make much sense given our horizontal filtering.
• An alternative is proposed on Accept patch format for JSONB - set. filter for PATCH #465 (comment), but it's flexibility makes it harder to implement. Additionally it's not isolated to a single content type.

Solution

Use a vendored media type based on json patch where we support the operations we lack. As prior art, azure cosmosdb json patch (also see here) does a similar thing and adds support for increment and set, which are not defined in the JSON Patch RFC.

For starters, we can add support for increment. Like so:

PATCH /items?id=eq.1
Content-Type: application/vnd.pgrst.json-patch

[
  { "op": "incr", "path": "/inventory", "value": 10 },
]

Path would refer to any column and the generated SQL would be like UPDATE items SET inventory = inventory + 10.

Later on we can extend this to add other operations, like mult, div, concat, etc.

[JanKaul]

Am I assuming correctly that the implementation would involve a new media type handler and a postgres implementation of jsonpatch?

What other steps would be required to implement this functionality as a builtin media type?

[steve-chavez]

@JanKaul I was thinking this should be a built-in media type handler in Haskell code (like the one used for application/vnd.pgrst.plan) but maybe it can be done as you say..

a new media type handler

We would need to finish #2826 for that (media type handlers only work for Accept right now, not for Content-Type).

a postgres implementation of jsonpatch?

That one would be insufficient because it only works on JSON columns, the intention for increments was to work on int, bigint, any type really. We already expose rows as "json documents", so it makes sense to allow json patch for all types of columns.

So we would need plpgsql/dynamic SQL on the function to generate the UPDATE. It could work...

---

On second thought, since this JSON patch is postgREST-specific it makes more sense to just have it as built-in handler.

[JanKaul]

That makes sense. Thanks for the clarification. I will have a look at the implementation of application/vnd.pgrst.plan.