JSON Set

March 9th, 2023

I’ve been using the HTTP PATCH request for optional parameters for update. I was a bit baffled why it’s not used more. A bit of googling leads to JSON Patch as the “standardized” way, but it feels way too complicated most of the time, 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. Let’s take an example.

CREATE TABLE posts (
    id serial PRIMARY KEY,
    body text,
    tags text[]
);

UPDATE posts SET body = 'new body' WHERE id = 1;

The intuitive thing is to send a basic patch request:

PATCH /post/1 HTTP/1.1

{"body": "new body"}

But with JSON Patch, you need to send:

PATCH /post/1 HTTP/1.1

[
    { "op": "replace", "path": "/body", "value": "new body"}
]

I adhere to the Perl idea that tools should make “easy things easy, hard things possible.” For simplicity, I’m calling the original, intuitive protocol “JSON Set” after the UPDATE...SET SQL syntax.

But even with relational models, you sometimes want some simple operations, especially for array columns. While JSON Patch is more complete, but it’s not necessarily worth the complexity, especially if most of your arrays are small (<1kb) and translating JSON Patch operations to SQL statements isn’t worth it. However, you do want to preserve some level of atomicity, especially for more frequently-modified data. In JSON Set, you send up the previous value and the backend should update a field based on that. Sometimes, that might mean the backend can diff the before-and-after and apply a simple “append” operation. Or more simply, the backend can just reject a change if the before-value doesn’t match its current value.

PATCH /post/1?body=old%20body HTTP/1.1

{"tags": ["old", "tags", "and", "new"]}

UPDATE posts SET tags = ARRAY['old', 'tags', 'and', 'new'] WHERE id = 1 AND tags = ARRAY['old', 'tags'];

If you reject the value (NOOP), the error response should return the latest field values so the client can easily update and retry.

HTTP/1.1 412 Precondition Failed

{"tags": ["old", "tags", "another"]}

golang

Unmarshalling JSON Set is funky in golang, especially for nullable columns. I sketched out an undefined package that would let you distinguish between null and undefined when unmarshalling. However, omitempty tags don’t work with marshalling structs, so you need to override MarshalJSON at the struct level and return a nil pointer for undefined fields. It’s gross, but unless omitzero is implemented in a v2, it’s the only solution.

How you’d translate that to SQL for nullable column values is a whole different problem, especially if you’re committed to using sqlc like me. You’d just need to do it as a big o’ transaction of separate statements. Less efficient because you run separate UPDATE statements for each modified column, but given that most models have less than a dozen columns and most PATCH requests only update 1 column, it’s probably not worth optimizing.