PostgreSQL: Remove attribute from JSON column
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:
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 afterFROM (VALUES ('{"attrA":1,"attrB":true,"attrC":["a","b","c"]}'::json)) AS v(data),LATERAL (SELECT * FROM json_each(data) WHERE "key" <> 'attrB') AS lGROUP 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 STRICTAS $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_tableSET data = json_object_delete_keys(data, 'attrB');
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}
I couldn't get SELECT '{"a": "b"}'::jsonb - 'a';
to work in 9.5.2. However, SELECT '{"a": "b"}'::jsonb #- '{a}';
did work!