Accept patch format for JSONB - `set.` filter for `PATCH`

[sscarduzio]

I'd like to add a field in a jsonb field (a la jsonb_set, ref. http://www.postgresql.org/docs/9.5/static/functions-json.html).

Ideally this would happen similarly to when you update part of the columns of a row with the PATCH verb.

Is this in the roadmap?

[niquola]

👍

[begriffs]

I can see how it's annoying to replace a big json object entirely just to change one part of it. We can think of ways to allow such a patch, and in the meantime there are two workarounds

• Create a stored procedure for updating the jsonb column of a table
• Normalize the data -- move the JSON attributes into another table and relate them to the current row by a foreign key

[sscarduzio]

On the same note of array patching proposed in #488

Given a table with a jsonb column, and a tuple with primary key 'x'
(x, {"w": true, "a": "z"})

PATCH /table/column?id=x
{
 "value": {"a": "b"}
} 

Would affect the row as follows:

(x, {"w": true, "a", "b"})

[begriffs]

Both #488 and this issue are special cases of the general ability to manipulate JSONB in a patch request.

If we're going to extend the behavior of PATCH it probably makes sense to choose a well-known patch format, such as RFC6902. Here is how it specifies adding an array element: https://tools.ietf.org/html/rfc6902#page-12

I'm closing the other issue as a dup and we can continue conversation here. This seems like a nontrivial amount of work!

[sscarduzio]

wait, in what issue are we following up on this?

[begriffs]

If I'm understanding correctly 488 is a special case of this issue, so let's track JSONB patching here.

[begriffs]

After reviewing this, I'm not sure how common the need is. It is also achievable with a stored procedure (documentation about how to call them is here). So I'm closing this issue.

[egeste]

+1, this is definitely a useful feature. I could extract out the datum into it's own column, or perform this behavior in an RPC, but I would strongly prefer to have a baked-in interface for performing PATCHes against a JSONB column

[steve-chavez]

This seems really useful. Implementing that RFC seems unnecessarily complicated(also slower because of the parsing) considering we already have the pg function that does all the work for us.

A simpler approach would be to define our own content type(could be application/vnd.pgrst.patch+json) and then send the payload to jsonb_set, much like the idea sscarduzio commented above.

For anyone interested in this being implemented, you can sponsor development on our Patreon or you could also contact me directly(email in profile) so we can arrange development on a fee basis.

[steve-chavez]

The upcoming jsonb subscripting syntax could help here. We could do:

PATCH /tbl?columns=jsonb_column[key]

{"jsonb_column": "value"}

brackets([]) need to be url-encoded though.