Renaming properties in JSONB

To rename a property in a JSON column from foo to bar. in PostgreSQL you can use the jsonb_set function in combination with the #- operator (which removes the specified key from a JSONB object) to achieve this. Here’s a general approach to rename a property:

1. Remove the old key (foo): Use the #- operator to remove the foo key from the JSONB column.
2. Add the new key (bar): Use jsonb_set to add the bar key with the value previously under foo.

Here’s how you could write the SQL statement:

UPDATE recipes SET contents = jsonb_set(contents #- '{foo}', '{bar}', contents->'foo') WHERE contents ? 'foo';

This query does the following:

• contents #- '{foo}' removes the foo key (and its value) from the contents.
• jsonb_set is then used to add a new key bar with the value that foo had. The {bar} argument specifies the path where the new key is added, and contents->'foo' specifies the value to be set for the new key.
• The WHERE clause ensures that this operation only affects rows where contents has the foo key.