I spend my days mostly working in a Postgres 13 environment with some core tables set up nearly a decade ago, meaning I'm used to accessing, inserting, and updating JSON being a bit painful. Especially if it's an hstore column. But lately I've been transitioning Serial Reader from MongoDB to Postgres which was an opportunity to install the latest and greatest Postgres and opened a world of improvements!

I don't use JSON columns much but found it convenient to speed up the transition and shut down those pesky MongoDB servers to mimic some of the NoSQL patterns 1:1 in a temporary legacy table, stuffed in a JSON column. Which meant I also needed to replicate some of the more precise operations MongoDB was performing, like updating one key value, or perhaps editing one key value in one item nested within a list or deep in a dictionary.

I was delighted to find such operations are not that painful since Postgres 14. In fact just a straightforward key value update is almost too easy:

-- String
UPDATE cool_table
SET some_json_column['name'] = '"Michael"'
WHERE some_json_column['user_id'] = '"ABCDE"';

-- Bool
UPDATE cool_table
SET some_json_column['is_active'] = 'true'
WHERE some_json_column['is_active'] = 'false';

-- Int
UPDATE cool_table
SET some_json_column['login_attempts'] = '5'
WHERE some_json_column['login_attempts'] > '5';

Note the odd syntax of the value, where strings need to be double quoted and non-strings still need to be provided as strings. I found a helpful shortcut (which also handles the wacky world of escaped string characters) is to lean on the Python 'json.dumps' function to avoid some clunky looking formatted strings.

But otherwise, this is wonderful and matches would I would blindly, naively try without referencing any docs! 💙

For more complex updates, I found 'jsonb_set' still comes in handy for querying which particular sub-element needs to be adjusted:

UPDATE cool_table
SET some_json_column = jsonb_set(
    cool_data,
    '{books}',
    (
        SELECT jsonb_agg(
            CASE
                WHEN book->>'_id' = '123456' THEN
                    jsonb_set(book, \'{progress}\', '0.5')
                ELSE
                    book
                END
        )
        FROM jsonb_array_elements(cool_data->'books') AS book
    )
) WHERE id='some_id';

I think if this weren't all just a temporary pattern until I can fully transition to more proper SQL schema, it would be nice to try to set up a Postgres function to standardize/hide away some of that complexity. But overall I was pleased how easily Postgres replicated some of the operations I had thought were MongoDB-exclusive strengths. Truly, just use Postgres for everything :)

Source: "Working With JSON in Postgres 14" by Aaron Bos