JSON SET
March 9th, 2023
I’ve been using the HTTP PATCH
request for optional parameters for update. It’s quite nice, especially when you’re “wholesale” replacing row field values, clients can update only the fields they’re touching with JSON.
JSON Patch is the standardized way of doing this, but it’s built for applying operations to JSON objects, not updating SQL fields. Operations require a bit more smarts on the server-side, but then you’re venturing into CDRTs. I’ve defined something dumber, meant to be painfully easy to implement and to cover the 80/20 case of updating Postgres rows from JSON PATCH requests. I call it JSON SET after the UPDATE...SET
SQL syntax.
Example
We’ll look at manipulations for the following example table.
CREATE TABLE posts (
id serial PRIMARY KEY,
body text,
tags text[]
)
Object
Replace value
{"body": "new body"}
Remove value
{"body": null}
Array
You can completely replace an array value with the Object syntax. These are for Array operations.
Append element
{"tags": {"": "new tag"}}
Replace tag at position
{"tags": {"1": "tag rename"}}
Remove all matching elements
{"tags": {"null": "existing tag"}}
Extras: array methods
Append, replace, and remove should be the 80% case. But JavaScript Array methods push
, concat
, pop
, splice
, shift
, unshift
are supported. The value is the array of arguments expected by the function. pop
, shift
ignore their values.
{"tags": {"push": ["new tag"]}}
{"tags": {"splice": [0, 1, "new first tag"]}}