user181452 user181452 - 3 months ago 38
JSON Question

Update nested key with postgres json field in Rails

I've been trying to update the following :

{"boxes": {"book": 2, "moving": 2}, "goods": {}}


to :

{"boxes": {"book_new": 2, "moving": 2}, "goods": {}}


without using a regular expression or doing this in ruby. but it seems it's a bit tricky.
I want to add new key and then delete the old one, I'm not familiar with the syntax and I couldn't find much on the internet.

I was able to add a new element to the data but not to the nested boxes!!
like that:

Update moves SET data = data::jsonb || '{"bookasas": 2}' WHERE data ->> 'boxes' LIKE '%book%';


Any help is appreciated.

Answer

There is no function to replace json key, so you should delete the old object and add new one:

update moves 
set data = jsonb_set(
    data::jsonb,
    array['boxes'],
    (data->'boxes')::jsonb - 'book' || '{"book_new": 2}'
    )
where data ->> 'boxes' like '%book%'
returning *;

                         data                         
------------------------------------------------------
 {"boxes": {"moving": 2, "book_new": 2}, "goods": {}}
(1 row)