I had to do some data wrangling which involved renaming a stack of jsonb keys in PostgreSQL.

This stackoverflow answer gives the correct SQL syntax. As it’s a bit fiddly getting the paths in the right place, I dredged up some PL/pgSQL to write a function I can re-use.

Top tips for writing PL/pgSQL (it’s been a long time): don’t accidentally put tab characters in your script, and remember assignment is := not just plain old equals.

Here’s the function:

CREATE OR REPLACE FUNCTION rename_jsonb_key(data jsonb, basepath text[], oldname text, newname text)
returns jsonb
AS
$$
DECLARE
    oldpath text[];
    newpath text[];
    value jsonb;
    result jsonb;
BEGIN
    IF data #> basepath ? oldname THEN
        oldpath := basepath || oldname;
        newpath := basepath || newname;
        value := data #> oldpath;
        result := jsonb_set(data #- oldpath, newpath, value);
    ELSE
        result := data;
    END IF;
    RETURN result;	
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

and here’s an update which uses it:

 UPDATE my_table 
 SET my_field = rename_jsonb_key(my_field, '{path, to, key}','oldname','newname');