How to Delete a Key from JSONB in PostgreSQL Easily

To delete a key from a jsonb column in PostgreSQL, use the - operator with the key name. For example, jsonb_column - 'key_name' returns the JSONB data without that key.

Syntax

The syntax to delete a key from a jsonb column uses the - operator followed by the key name in single quotes.

• jsonb_column - 'key': Removes the key named key from the JSONB object.
• This operator returns a new JSONB value without modifying the original data unless used in an UPDATE statement.

sql

jsonb_column - 'key_name'

Example

This example shows how to remove the key age from a JSONB column named data in a table called users. It demonstrates both selecting the modified JSONB and updating the table to remove the key permanently.

sql

CREATE TABLE users (id serial PRIMARY KEY, data jsonb);

INSERT INTO users (data) VALUES
  ('{"name": "Alice", "age": 30, "city": "NY"}'),
  ('{"name": "Bob", "age": 25, "city": "LA"}');

-- Select JSONB without the 'age' key
SELECT id, data - 'age' AS data_without_age FROM users;

-- Update table to remove 'age' key permanently
UPDATE users SET data = data - 'age';

-- Verify update
SELECT * FROM users;

Output

 id |           data_without_age           
----+-------------------------------------
  1 | {"name": "Alice", "city": "NY"}
  2 | {"name": "Bob", "city": "LA"}
(2 rows)

UPDATE 2

 id |                 data                 
----+-------------------------------------
  1 | {"name": "Alice", "city": "NY"}
  2 | {"name": "Bob", "city": "LA"}
(2 rows)

Common Pitfalls

Common mistakes when deleting keys from jsonb include:

• Using double quotes instead of single quotes around the key name, which causes syntax errors.
• Trying to delete a key from a json (not jsonb) column, which does not support the - operator.
• Expecting the original data to change without an UPDATE statement; the - operator only returns a modified copy.

sql

/* Wrong: double quotes cause error */
SELECT data - '"age"' FROM users;

/* Right: use single quotes */
SELECT data - 'age' FROM users;

Quick Reference

Operation	Syntax	Description
Delete key from jsonb	jsonb_column - 'key'	Removes the specified key from the JSONB object
Delete multiple keys	jsonb_column - ARRAY['key1', 'key2']	Removes multiple keys at once
Update jsonb column	UPDATE table SET jsonb_column = jsonb_column - 'key' WHERE condition;	Permanently removes key in table data

Key Takeaways

Use the - operator with single quotes to delete a key from a jsonb column.

The - operator returns a new jsonb value; use UPDATE to save changes.

jsonb supports deleting multiple keys using an array of keys.

Do not use double quotes around keys; always use single quotes.

The operator works only on jsonb, not on json data type.