Delete attribute from json metadata in postgres (9.4.7)

Question

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.

klin · Accepted Answer · 2018-02-21 18:52:11Z

2

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

edited Feb 21, 2018 at 18:52

answered Feb 20, 2018 at 20:48

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RAJKUMAR PADILAM Over a year ago

I get the below error when i use the sql: ERROR: operator does not exist: jsonb - unknown LINE 1: update trackable_status set metadata= metadata -'exception' ... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

RAJKUMAR PADILAM Over a year ago

I think you need to do things differently in postgres. When i create the same sample table xyz and run the query u provided in pgadmin, it gives the same above error.

klin Over a year ago

Upgrade Postgres. You should mention your server version in the question, especially is it is not the latest one.

RAJKUMAR PADILAM Over a year ago

It is 9.4.7. I have updated my post with the version. Is there any workaround for this version

Collectives™ on Stack Overflow

Delete attribute from json metadata in postgres (9.4.7)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related