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}

Answer 3

I was facing similar issue to remove a key-value pair from an existing json column in postgres. I was able to fix this using - operator as follows:

UPDATE my_table
SET data = data::jsonb - 'attrB'
WHERE id = 'A';

Answer 4

If you want to remove a sub-field, like:

{
  "a": {
    "b": "REMOVE ME!"
  }
}

You can simply use:

UPDATE my_table
SET my_json_column = my_json_column::jsonb #- '{a,b}';

Answer 5

I couldn't get SELECT '{"a": "b"}'::jsonb - 'a'; to work in 9.5.2. However, SELECT '{"a": "b"}'::jsonb #- '{a}'; did work!

Answer 6

Simply use the #- operator, for example:

SELECT '{"foo": 10, "bar": [], "baz": {}}'::jsonb #- '{baz}';

Answer 7

It is an ugly hack but if attrB isn't your first key and it appears only once then you can do the following:

UPDATE my_table SET data = REPLACE(data::text, ',"attrB":' || (data->'attrB')::text, '')::json;

Answer 8

While this is certainly easier in 9.5+ using the jsonb operators, the function that pozs wrote to remove multiple keys is still useful. For example, if the keys to be removed are stored in a table, you could use the function to remove them all. Here is an updated function, using jsonb and postgresql 9.5+:

CREATE FUNCTION remove_multiple_keys(IN object jsonb, 
                                     variadic keys_to_delete text[], 
                                     OUT jsonb)
  IMMUTABLE
  STRICT
  LANGUAGE SQL
AS 
$$
  SELECT jsonb_object_agg(key, value)
     FROM (SELECT key, value 
           FROM jsonb_each("object")
           WHERE NOT (key = ANY("keys_to_delete")) 
     ) each_subselect
$$
;

If the keys to be removed are stored in a table, (e.g. in the column "keys" of the table "table_with_keys") you could call this function like this:

SELECT remove_multiple_keys(my_json_object, 
                            VARIADIC (SELECT array_agg(keys) FROM table_with_keys));

Answer 9

One other convenient way of doing this is to use hstore extension. This way you can write some more convenient function to set/delete keys into a json object. I came up with following function to do the same:

CREATE OR REPLACE FUNCTION remove_key(json_in json, key_name text)
 RETURNS json AS $$
 DECLARE item json;
 DECLARE fields hstore;
BEGIN
 -- Initialize the hstore with desired key being set to NULL
 fields := hstore(key_name,NULL);

 -- Parse through Input Json and push each key into hstore 
 FOR item IN  SELECT row_to_json(r.*) FROM json_each_text(json_in) AS r
 LOOP
   --RAISE NOTICE 'Parsing Item % %', item->>'key', item->>'value';
   fields := (fields::hstore || hstore(item->>'key', item->>'value'));
 END LOOP;
 --RAISE NOTICE 'Result %', hstore_to_json(fields);
 -- Remove the desired key from store
 fields := fields-key_name;

 RETURN hstore_to_json(fields);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
STRICT;

A simple example of use is:

SELECT remove_key(('{"Name":"My Name", "Items" :[{ "Id" : 1, "Name" : "Name 1"}, { "Id" : 2, "Name 2" : "Item2 Name"}]}')::json, 'Name');
-- Result
"{"Items": "[{ \"Id\" : 1, \"Name\" : \"Name 1\"}, { \"Id\" : 2, \"Name 2\" : \"Item2 Name\"}]"}"

I have another function to do the set_key operation as well as following:

CREATE OR REPLACE FUNCTION set_key(json_in json, key_name text, key_value text)
RETURNS json AS $$
DECLARE item json;
DECLARE fields hstore;
BEGIN
 -- Initialize the hstore with desired key value
 fields := hstore(key_name,key_value);

 -- Parse through Input Json and push each key into hstore 
 FOR item IN  SELECT row_to_json(r.*) FROM json_each_text(json_in) AS r
 LOOP
   --RAISE NOTICE 'Parsing Item % %', item->>'key', item->>'value';
   fields := (fields::hstore || hstore(item->>'key', item->>'value'));
 END LOOP;
 --RAISE NOTICE 'Result %', hstore_to_json(fields);
 RETURN hstore_to_json(fields);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
STRICT;

I have discussed this more in my blog here.

Answer 10

I was struggling to find a simple update query that removed json keys in Postgres 9.4 without creating a function, so here's an update to @posz answer.

UPDATE someTable t
SET someField = newValue
FROM (
    SELECT id,
        json_object_agg(l.key, l.value)::text AS newValue
    FROM someTable t,
        LATERAL (
            SELECT *
            FROM json_each(t.someField::json)
            WHERE "key" <> ALL (ARRAY['key1', 'key2', 'key3'])
        ) AS l
    GROUP BY id
) upd
WHERE t.id = upd.id

Query assumes you have a table like this:

CREATE TABLE myTable (
    id SERIAL PRIMARY KEY,
    someField text
);

I guess you can use this line from @posz answer instead of json_object_agg line, to make it work on older postgres, but I didn't test it.

('{' || array_to_string(array_agg(to_json(l.key) || ':' || l.value), ',') || '}')::json AS after

Also make sure to run select from subrequest to make sure you're updating correct data

Answer 11

In my case

{"size": {"attribute_id": 60, "attribute_name": "Size", "attribute_nameae": "Size" "selected_option": {"option_id": 632, "option_name": "S"}}, "main_color": {"attribute_id": 61, "attribute_name": "Main Color", "selected_option": {"option_id": 643, "option_name": "Red"}}}

Remove size->attribute_nameae

UPDATE table_name set jsonb_column_name = jsonb_set(jsonb_column_name, '{size}', (jsonb_column_name->'size') -  'attribute_namea') WHERE <condition>

Answer 12

For PostgreSQL version > 9.6, you can simply run this:

UPDATE my_table 
set data = data::jsonb - 'attrB'

Answer 13

In case if you want to remove JSON attribute not only from a single document but also from an array consider using statement like this:

    UPDATE table_to_update t
    SET jsonColumnWithArray = (SELECT JSON_AGG(jsonItem)
               FROM (SELECT JSON_ARRAY_ELEMENTS(t1.jsonColumnWithArray)::JSONB #- '{customerChange, oldPeriod}' AS jsonItem
                     FROM table_to_update t1
                     WHERE t1.id = t1.id) AS t2);

Input

[
  {
    "customerChange": {
      "newPeriod": null,
      "oldPeriod": null
    }
  },
  {
    "customerChange": {
      "newPeriod": null,
      "oldPeriod": null
    }
  }
]

Output

[
  {
    "customerChange": {
      "newPeriod": null
    }
  },
  {
    "customerChange": {
      "newPeriod": null
    }
  }
]