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.

Canescent

What is your postgres version? – Chastain

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');

Chastain

@RezaS not directly this, but you can combine the recursive variant (in https://mcmap.net/q/86853/-how-do-i-modify-fields-inside-the-new-postgresql-json-datatype ) with this (see my comment there). – Chastain

Do you need the , {} in the query? I think you can leave that out too – Veta

@RezaS without that, the function would return null when you empty an object of all its keys, I think. – Supervision

@Supervision yes, exactly. however in new "versions", I used to write concat('{', ..., '}'), because concat will ignore nulls. – Chastain

I am trying to pass two values and it is not working and not giving any errors: UPDATE my_table SET data = json_object_delete_keys(data, '{"attrB", "attrC"}'); – Faintheart

@Faintheart the function in the answer is VARIADIC -- you should call it like json_object_delete_keys(data, 'attrB', 'attrC') or json_object_delete_keys(data, VARIADIC '{"attrB", "attrC"}'::text[]) – Chastain

instead of manually build json with string_agg/array_agg we can build it with json_agg("key","value") – Rhadamanthus

* json_object_agg – Rhadamanthus

@AlexanderHasselbach this answer was created for 9.3 a long ago, when there were no json_object_agg(). Now there is an explicit operator for OP's problem (so there is no need to use that function either), I'll update my answer soon. – Chastain

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}

Bicipital

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';

Nebulosity

I dunno why there's no one up vote u. Your solution is very simple and works, great. Thank u so much. – Guarani

This is the best solution: works not only with json data type but also with text. – Europium

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}';

Reductase

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!

Zsa

Answer 6

Simply use the #- operator, for example:

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

Advocation

for a top-level key, you can just use -. It would go like this SELECT '{"foo": 10, "bar": [], "baz": {}}'::jsonb - 'baz'; (see postgresql.org/docs/9.5/functions-json.html for the differences between the two operators) – Lecherous

Answer 7

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

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

Fireworks

This is far too simple, yet the correct solution – Girlish

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));

Jato

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.

Woald

Answer 10

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;

Tundra

Not so bad. You could improve it with regexp_replace - make the comma optional and it would work in any case. – Chanson

The problem with this is if the JSON expression evaluates to NULL (for example, the key does not exist), the entire string concatenation evaluates to NULL due to the || operator, which then sets the entire JSON column to NULL. So if you run this on a JSON column with inconsistent data or accidentally type a key that doesn't exist, it will delete all your data. A better solution is using the concat() function which ignores NULL values, and thus won't destroy your data. UPDATE my_table SET data = REPLACE(data::text, concat(',"attrB":' , (data -> 'attrB')::text), '')::json; – Starch

I wasn't saying it is perfect, but it is a quick and dirty hack. – Culp

Answer 11

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

Ranch

UPDATE tenants t SET config = newValue FROM ( SELECT id, json_object_agg(l.key, l.value)::text AS newValue FROM tenants t, LATERAL ( SELECT * FROM json_each(t.config::json -> 'features') WHERE "key" <> ALL (ARRAY['edr']) ) AS l GROUP BY id ) upd WHERE t.id = upd.id and t.id = 100 I want to iterate over the documents inside the features. Any idea how I could do that? Thanks. – Talus

Answer 12

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>

Exploitation

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
    }
  }
]

Spartacus