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]

:+1:

[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.

[steve-chavez]

An advantage of adopting RFC6902 is that it could also serve us to increment a value(common operation):

{ "op": "replace", "path": "/a/b/c", "value": " +=3" }
{ "op": "replace", "path": "/a/b/d", "value": "-=1" }

Taken from https://github.com/json-api/json-api/issues/214#issuecomment-49104564

Also a json path lib already available https://github.com/GallagherCommaJack/Haskell-JSON-Patch

---

It would also serve to update a column based on another column:

PATCH /employees?id=eq.1
Content-Type: application/json-patch+json

{ "op": "copy", "from": "/first_name", "path": "/middle_name" }

[micahjon]

I realize implementing RFC 6902 would be more complex than just using Postgres' built-in functionality, but it would allow developers to use existing JSON diff & patch solutions instead of having to roll their own, for instance:

• JavaScript fast-json-patch (1.5k stars) (1.4 million weekly downloads)
• Go evanphx/json-patch (700 stars)
• Java java-json-tools/json-patch (540 stars)

As someone writing apps, I'd much rather use a battle-tested patching solution at the front-end and API layers, even if it's marginally more expensive at the database layer. To be fair, I'm out of my depth on the exact technical tradeoffs here and could be missing something.

I think especially for libraries like this one (and companies like Supabase) that are building on PostgreSQL with developer ergonomics front and center, compatibility with the broader ecosystem matters and will spur adoption.

[steve-chavez]

Some observations about implementing JSON patch

• Operating on a jsonb column type, single row(/tbl?id=eq.1)
  • Can be done in pure SQL. There's a ready made example here, it basically relies on jsonb_set.
  • A json pointer of more than one level({"path": "/column/key/other", ..}) can be assumed to operate on a jsonb column type.
• Operating on a non-jsonb column type, single row(/tbl?id=eq.1)
  • Needs dynamic SQL to SET a particular column. Has to be done in Haskell code.
    • A json pointer of one level({"path": "/column", ..}) can be assumed to operate on a non-jsonb column type.
    • The add operation and remove operation won't work here, since it would imply adding and removing a column.
    • move could work by nullifying the first column value.
    • copy should work fine
    • replace would be most useful here, since it would allow us to increment a value as mentioned above.
• Operating on multiple rows(/tbl?id=in.(1,2,3,4,5))
  • The json pointer would have to choose one of the rows by the array index{"path": "/1/column", ..}. In this case we'd have to force an order filter is present, otherwise the order won't be guaranteed.
  • Haven't figured out how the resulting SQL would look here. I guess we could limit json patch to a single row for now.
  • bulk json patch doesn't seem that commonly implemented.

Drawbacks

• Operating on non-jsonb types(which we need) would require parsing the JSON body in Haskell code, this would be bad for performance/throughput.
  • Typically the json patch bodies are small though, since they're a partial document update.
• Incrementing a value(or doing another math operation) is not really JSON Patch, it's more of a hack. So JSON patch libraries won't really help with DX here.
  • azure supports increment in json patch https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update#supported-operations. We could use a special content-type for this, like application/vnd.pgrst.json-patch.
• The test operation is redundant with horizontal filtering
  • we can choose not to implement it in our custom content type.
• Overall it feels shoehorned(inconsistent with our feature set) and complex.

  • same, we can choose which operations to support in our custom content type.

Will propose an alternative that doesn't have the drawbacks and it's more flexible.

[steve-chavez]

Alternative

Introduce a new set filter, it takes a list of identifiers(similar to what the in operator does for values) plus reserved keywords, e.g. ?col1=set.(col1,plus,col2,body->values). The reserved keywords are:

• body: taking Wolfgang's idea about filter values on the body, with this we can refer to a value in the body to do the SET.
• plus/minus/concat/etc: operators that we'll allow(SET col = col + col2).
• default: equivalent to doing SET col = DEFAULT.
• null: to do SET col = NULL.

This would cover all the UPDATE use cases we've been asked until now.

Use cases

Replace a column based on another one:

PATCH /tbl?col1=set.(col2)&id=eq.1
(Body is unecessary here)

UPDATE tbl SET col1 = col2 WHERE id = 1

Incrementing a counter by a value:

PATCH /tbl?counter=set.(counter,plus,body->value)&id=eq.1
{"value": 5}

UPDATE tbl SET counter = counter + 5 WHERE id = 1

Append to an array:

PATCH /tbl?col_arr=set.(col_arr,concat,body->value)&id=eq.1
{"value": [1,2,3]}

update tbl set col_arr = col_arr || [1,2,3]

JSON Patch replace(add is more or less the same):

PATCH /tbl?jsonb_col->key=set.(body->value)&id=eq.1
{"value": {"another" 3}}

# whenever an arrow(`->` ) is used, we assume `jsonb_set`
update tbl set jsonb_col = jsonb_set(jsonb_col, '{key}', '{"another" 3}') where id = 1

JSON Patch copy:

PATCH /tbl?jsonb_other->a=set.(jsonb_col->b)&id=eq.1
(Body is optional here)

update tbl set jsonb_other = jsonb_set(jsonb_other, '{a}', jsonb_col->b) where id = 1

JSON Patch move(same as copy above plus nullifying):

PATCH /tbl?jsonb_other->a=set.(jsonb_col->b)&jsonb_col->b=set.(null)&id=eq.1
(Body is unecessary here)

update tbl set
jsonb_other = jsonb_set(jsonb_other, '{a}', jsonb_col->b),
jsonb_col = jsonb_set(jsonb_col, '{b}', null)
where id = 1

JSON Patch remove should be just nullifying. JSON Patch test is unneeded in our case.

Advantages

• No need to parse the body(higher perf)
• More flexible as it can apply multiple operators
• Can work on multiple rows
• Also covers all the JSON patch cases

Backwards compat

Right now it can be assumed that all the json keys in the body are used in set.

PATCH /people?age=lt.13 HTTP/1.1

{ "category": "child", "eye_color": "brown" }

Implicitly is:

PATCH /people?age=lt.13&category=set.(body->category)&eye_color=set.(body->eye_color) HTTP/1.1

{ "category": "child", "eye_color": "brown" }

And they can be combined as:

PATCH /people?age=lt.13&age=set.(age,plus,body->age_increment) HTTP/1.1

{ "category": "child", "age_increment": "2" }

# it would translate to
UPDATE people SET category = 'child', age = age + 2 WHERE age < 13

---

The parentheses inside the set value are to maintain consistency with in but it should be fine omitting them as well.

[wolfgangwalther]

I suppose a slightly different syntax, which is a tiny bit better to read:

PATCH /people?age=lt.13&age=set.age+plus+body->age_increment HTTP/1.1
{ "category": "child", "age_increment": "2" }

Because in your client-app you can most likely write that as age plus body->age_increment, i.e. use spaces instead of +.

This would also keep parentheses and commas for row constructors, to be able to set custom types:

PATCH /coordinates?point=set.(body->x,body-y) HTTP/1.1
{ "x": 1, "y": 2 }

would translate to

UPDATE coordinates SET point = ROW(1,2)

[oalexdoda]

Just do what MongoDB does: https://www.mongodb.com/docs/manual/reference/operator/update/set/#set-fields-in-embedded-documents - it's been available for quite some time.

[steve-chavez]

Edit: not a good idea since the syntax would be tied to a particular media type. Maybe this could work later for urlencoded or some other media.

Just one final improvement that will turn this into a simpler interface.

Using the suggestions on https://github.com/PostgREST/postgrest/issues/2066, we can put the set grammar in a header, by defining a new media type. This way we can avoid the whole body->col convention.

PATCH /people?age=lt.13

Content-Type: application/vnd.pgrst.patch+json; category.set=$category; age.set=age+plus+$age_increment

{ "category": "child", "age_increment": "2" }

PATCH /coordinates HTTP/1.1

Content-Type: application/vnd.pgrst.patch+json; point.set=($x,$y)

{ "x": 1, "y": 2 }

• Operators supported: plus, minus, div, mult.

• $key refers to the json field in the body. This makes sense if we think of it as a named parameter in a prepared statement($1, $2, etc).

• There's prior art for a "dynamic header": Set-Cookie. So we should be fine defining our media type as Content-Type: application/vnd.pgrst.patch+json; <param-name>=<param-value>

• Later we can extend the available operators by using https://github.com/PostgREST/postgrest/issues/2805

[steve-chavez]

Just one improvement that could make parsing easier, use $body instead of body:

PATCH /people?age=lt.13&age=set.age+plus+$body->age_increment HTTP/1.1
{ "category": "child", "age_increment": "2" }

It would also make more clear that this key has a special meaning.

[mckinlde]

Hi, I am a novice end-user. I found this while looking for a way to do the append to an array use case, as in https://github.com/PostgREST/postgrest/issues/465#issuecomment-1199858002:

In my case jsonb is not relevant; I have a bigint[] and I'd like to pass something like:

     const { data: userData, error: userError } = await patchPgData(
      rdsUri,
      Token,
      `/user?id=eq.${session.user_id}`,
      { saved_articles: `array_append(saved_articles, ${article.article_id})` }
    );

An RPC was suggested in https://github.com/PostgREST/postgrest/issues/488, and while that would work it seems unnecessarily specific. Could the built-in PostgreSQL Array-append function be called from postgrest? That (seems like) would cover many use cases, not require custom RPCs, and allow postgres errors to propogate through end-user http requests as I can attest I am already used to.

[AC4G]

We already have an select query parameter with which we can directly access an value inside of a json (example: GET http://localhost:3000/items?id=eq.2&select=parameter->damage) so wouldn't it be useful to have something similar for updating a specific value?

What I had in mind

• Updating values by key:

  PATCH http://localhost:3000/items?id=eq.2&update=parameter->damage,parameter->health HTTP/1.1
  { "damage": +12.34, "health": 78.3 }

  • Deletion by key:

    PATCH http://localhost:3000/items?id=eq.2&delete=parameter->effect->confusion HTTP/1.1

    (deletes the confusion key inside of effect)

• Updating and deletion by key in one request:

  PATCH http://localhost:3000/items?id=eq.2&update=parameter->damage,parameter->health&delete=parameter->effect->confusion HTTP/1.1
  { "damage": +12.34, "health": 78.3 }

[yuvalkarmi]

Hey folks, chiming in here to ask where we stand with this? It would be extremely helpful to have such patch functionality for JSON objects, and I see the discussion about this dates 7 years :)

[popadotstudio]

What would it take to make this happen considering there's this little thing called jsonb_set `been around now :)