Delete attribute from json metadata in postgres (9.4.7)

0
votes

I have a table xyz that has a jsob column called metadata, which looks something like 

"{"decisions": {"final_decision": "ADVANCE"}, "exception": {} }".

I want to remove the 'exception' attribute and have an sql 

update xyz set metadata = metadata - metadata->'exception' where process_id='1e3aeac3';

But this gives me an exception while running
ERROR: operator does not exist: jsonb - jsonb
LINE 1: update xyz set metadata= metadata - 'exception'...

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I tried searching but I wasn't able to get around the cast issue.

1
sqlpostgresqlpostgresql-9.4

1 Answers

2
votes

The second argument of the delete (-) operator is text.

If you want to delete the attribute, use it's key: 

update xyz 
set metadata = metadata - 'exception'
returning *;

                   metadata                   
----------------------------------------------
 {"decisions": {"final_decision": "ADVANCE"}}
(1 row)

SqlFiddle

The operator was introduced in Postgres 9.5

In Postgres 9.4 use the function:

create function jsonb_remove_key(json_object jsonb, key_to_remove text)
returns jsonb language sql immutable as $$
    select jsonb_object_agg(key, value)
    from jsonb_each(json_object)
    where key <> key_to_remove
$$;

update xyz
set metadata = jsonb_remove_key(metadata, 'exception')
returning *;