Remove key from multiple nested jsonb objects with unknown keys in PostgreSQL

Question

I have a table with a column of jsonb objects that look similar, but have different top level keys in each row. Each record looks something like this, but with different top level keys:

{
   "10": {
      "key": "value",
      "toDelete": "value"
   },
   "42": {
      "key": "value",
      "toDelete": "value"
   },...
}

I need to do an update to remove the toDelete key/value from each object in each record in every row. It's easy enough to remove each one manually using the #- operator, but there could be hundreds or even thousands of top level keys in each record, so I need some sort of dynamic solution. I tried aggregating all the paths to delete into an array and removing all of them at once using column #- array where the array looks like {{10, toDelete},{42,toDelete}...} but that didn't do the trick.

If you are storing thousands of keys in jsonb, I think you are abusing postgresql. Perhaps you should consider extracting your data from json and store it in real SQL table(s) instead. — mvp
– mvp, Commented Mar 6, 2019 at 20:39

klin · Accepted Answer · 2019-03-06 23:36:48Z

2

The structure of the json column is an anti-pattern, I fully agree with the comment by @mvp:

... you should consider extracting your data from json and store it in real SQL table(s) instead.

If you are forced to play with the original data, use the function:

create or replace function remove_nested_object(obj jsonb, key_to_remove text)
returns jsonb language sql immutable as $$
    select jsonb_object_agg(key, value- key_to_remove)
    from jsonb_each(obj)
$$;

update my_table
set json_column = remove_nested_object(json_column, 'toDelete')
where json_column::text like '%"toDelete":%';

answered Mar 6, 2019 at 23:36

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Colin Over a year ago

I got a similar answer using a plpgsql stored procedure to build a query string that removes all of them. It's super slow though. This works way better. Thank you!

alexbogush · Accepted Answer · 2022-11-07 22:33:53Z

Using @klin 's function I got this error

ERROR: cannot delete from scalar CONTEXT: SQL function "remove_nested_object" statement 1 SQL state: 22023

because I tried to make it work recursively. I ended up with this function that deletes an unwanted key on any level of json hierarchy. It also deletes several keys for the sake of economy and convenience

CREATE OR REPLACE FUNCTION public.remove_nested_object(
    obj jsonb,
    keys_to_remove text[])
    RETURNS jsonb
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL SAFE 
AS $BODY$
declare
    returned_object jsonb;
    key_to_remove text;
begin
    returned_object := obj;

    case
        when jsonb_typeof(obj) = 'object' then
            foreach key_to_remove in array keys_to_remove
            loop
                returned_object := returned_object - key_to_remove;
            end loop;
            return jsonb_object_agg(key, remove_nested_object(value, keys_to_remove)) from jsonb_each(returned_object);
        when jsonb_typeof(obj) = 'array' then 
            return (select jsonb_agg(remove_nested_object(item, keys_to_remove)) from jsonb_array_elements(obj) item);
        else return obj;
    end case;
end;
$BODY$;

Example of usage:

select remove_nested_object(yourJsonB, '{field1,field2,field3}')

Collectives™ on Stack Overflow

Remove key from multiple nested jsonb objects with unknown keys in PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related