How do I update a JSON column with a NULL value?

Question

Hi Im trying to add / update data in my json column - where the value inside is NULL not having much luck - here is my query that errors

update departments 
set (data->>'formal_name') = departments.name 
where (data->>'formal_name') is null

thanks for any suggestions

Answer 1

Unless you are using some Alpha release of PostgreSQL 9.5 you cannot update the data inside a JSON column:

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#JSONB-modifying_operators_and_functions

Also, consider the advices given in this answer:

How to perform update operations on columns of type JSONB in Postgres 9.4

And if you really need to modify the data in the JSON field, you will probably have to decompose the JSON with json_array_elements() and then build it again as a whole, as explained here:

https://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array