PostgreSQL: Remove attribute from JSON column

Question

I need to remove some attributes from a json type column.

The Table:

CREATE TABLE my_table( id VARCHAR(80), data json); INSERT INTO my_table (id, data) VALUES (   'A',    '{"attrA":1,"attrB":true,"attrC":["a", "b", "c"]}' ); 

Now, I need to remove attrB from column data.

Something like alter table my_table drop column data->'attrB'; would be nice. But a way with a temporary table would be enough, too.

Answer 1

Update: for 9.5+, there are explicit operators you can use with jsonb (if you have a json typed column, you can use casts to apply a modification):

Deleting a key (or an index) from a JSON object (or, from an array) can be done with the - operator:

SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'        jsonb '["a",1,"b",2]' - 1    -- will yield jsonb '["a","b",2]' 

Deleting, from deep in a JSON hierarchy can be done with the #- operator:

SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}' -- will yield jsonb '{"a":[null,{"b":[]}]}' 

For 9.4, you can use a modified version of the original answer (below), but instead of aggregating a JSON string, you can aggregate into a json object directly with json_object_agg().

Related: other JSON manipulations whithin PostgreSQL:

• How do I modify fields inside the new PostgreSQL JSON datatype?

Original answer (applies to PostgreSQL 9.3):

If you have at least PostgreSQL 9.3, you can split your object into pairs with json_each() and filter your unwanted fields, then build up the json again manually. Something like:

SELECT data::text::json AS before,        ('{' || array_to_string(array_agg(to_json(l.key) || ':' || l.value), ',') || '}')::json AS after FROM (VALUES ('{"attrA":1,"attrB":true,"attrC":["a","b","c"]}'::json)) AS v(data), LATERAL (SELECT * FROM json_each(data) WHERE "key" <> 'attrB') AS l GROUP BY data::text 

With 9.2 (or lower) it is not possible.

Edit:

A more convenient form is to create a function, which can remove any number of attributes in a json field:

Edit 2: string_agg() is less expensive than array_to_string(array_agg())

CREATE OR REPLACE FUNCTION "json_object_delete_keys"("json" json, VARIADIC "keys_to_delete" TEXT[])   RETURNS json   LANGUAGE sql   IMMUTABLE   STRICT AS $function$ SELECT COALESCE(   (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')    FROM json_each("json")    WHERE "key" <> ALL ("keys_to_delete")),   '{}' )::json $function$; 

With this function, all you need to do is to run the query below:

UPDATE my_table SET data = json_object_delete_keys(data, 'attrB'); 

Answer 2

This has gotten much easier with PostgreSQL 9.5 using the JSONB type. See JSONB operators documented here.

You can remove a top-level attribute with the "-" operator.

SELECT '{"a": {"key":"value"}, "b": 2, "c": true}'::jsonb - 'a' // -> {"b": 2, "c": true} 

You can use this within an update call to update an existing JSONB field.

UPDATE my_table SET data = data - 'attrB' 

You can also provide the attribute name dynamically via parameter if used in a function.

CREATE OR REPLACE FUNCTION delete_mytable_data_key(     _id integer,     _key character varying)   RETURNS void AS $BODY$ BEGIN     UPDATE my_table SET         data = data - _key     WHERE id = _id; END; $BODY$   LANGUAGE plpgsql VOLATILE   COST 100; 

The reverse operator is the "||", in order to concatenate two JSONB packets together. Note that the right-most use of the attribute will overwrite any previous ones.

SELECT '{"a": true, "c": true}'::jsonb || '{"a": false, "b": 2}'::jsonb  // -> {"a": false, "b": 2, "c": true}