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"]}}