Update nested key with postgres json field in Rails

Question

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.

Possible duplicate of How do I modify fields inside the new PostgreSQL JSON datatype? — pozs
– pozs, Commented Aug 29, 2016 at 12:43
The question concerns particularly replacing a json key, so it's not a duplicate. — klin
– klin, Commented Aug 29, 2016 at 12:54
@klin The only additional information above the linked question is that in PostgreSQL, there is no direct replace operation: to replace a key-value pair, first the old key needs to be deleted & the new needs to be set. But because OP did figure this out already (I want to add new key and then delete the old one), I think it is already answered. But I agree, this question is in a somewhat gray zone (regarding duplication). — pozs
– pozs, Commented Aug 29, 2016 at 15:57

klin · Accepted Answer · 2016-08-29 13:31:07Z

5

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' || jsonb_build_object('book_new', data->'boxes'->'book')
    )
where data ->> 'boxes' like '%book%'
returning *;

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

edited Aug 29, 2016 at 13:31

answered Aug 29, 2016 at 12:52

klin

123k15 gold badges240 silver badges262 bronze badges

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

9 Comments

user181452 Over a year ago

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

user181452 Over a year ago

Thanks it works but I had to add ::text to the 'book' and ::jsonb to (data->'boxes')

user181452 Over a year ago

please update your answer to have these two and it will be perfect, so may other people can benefit from it, many thanks again!

klin Over a year ago

Ok, I've tested it with data of type jsonb.

klin Over a year ago

I'm a bit confused, the solution works well on the data in question.

|

Collectives™ on Stack Overflow

Update nested key with postgres json field in Rails

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related