postgresql jsonb remove key

Removing a Key from JSONB Data in PostgreSQL#

If you are working with JSONB data in PostgreSQL, there may be instances where you need to remove a key from the JSONB object. Here are a few ways to accomplish this.

Using the delete operator (->)#

One way to remove a key from a JSONB object in PostgreSQL is to use the delete operator (->). This operator removes a key-value pair from a JSONB object and returns the modified object.

UPDATE table_name SET jsonb_column = jsonb_column -> 'key_to_remove'

Using the remove_path function#

Another way to remove a key from a JSONB object is to use the remove_path function. This function removes a specified path from a JSONB object and returns the modified object. In this case, we would specify the path to the key we want to remove.

UPDATE table_name SET jsonb_column = jsonb_remove_path(jsonb_column, '{key_to_remove}')

Using the set operator (->>)#

A third option is to use the set operator (->>) to overwrite the value of the key you want to remove with null. This effectively removes the key-value pair from the JSONB object.

UPDATE table_name SET jsonb_column = jsonb_column ->> '{key_to_remove, null}'