2

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.

Boris Schegolev
  • 3,601
  • 5
  • 21
  • 34
user181452
  • 545
  • 10
  • 25
  • 1
    Possible duplicate of [How do I modify fields inside the new PostgreSQL JSON datatype?](http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) – pozs Aug 29 '16 at 12:43
  • The question concerns particularly replacing a json key, so it's not a duplicate. – klin Aug 29 '16 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 Aug 29 '16 at 15:57

1 Answers1

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)
klin
  • 112,967
  • 15
  • 204
  • 232
  • I'm getting `HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.` – user181452 Aug 29 '16 at 12:59
  • Thanks it works but I had to add `::text` to the 'book' and `::jsonb` to `(data->'boxes')` – user181452 Aug 29 '16 at 13:04
  • please update your answer to have these two and it will be perfect, so may other people can benefit from it, many thanks again! – user181452 Aug 29 '16 at 13:04
  • Ok, I've tested it with `data` of type jsonb. – klin Aug 29 '16 at 13:06
  • oh, I've just noticed by doing that, I'm losing the value, because you are assigning 2 always to the new value! – user181452 Aug 29 '16 at 13:26
  • No, No, this solution is concatenating the data to itself and working randomly `{"boxes": {"barry": 0, "boxes": {"picture": 0, "book_new": 2, "clothing": 0, "sdssdsdds": 2}, "goods": {}, "picture": 0, "book_new": 0, "bookasas": 2, "sdssdsdds": 2}, "goods": {}, "bookasas": 2} ` – user181452 Aug 29 '16 at 13:36
  • I tried the old one, first add new element and then delete the old one `data::jsonb || '{"book_new": (data->'boxes')::jsonb->>'book'}' || (data->'boxes')::jsonb - 'book'::text ` but getting the value from the element is wrong. – user181452 Aug 29 '16 at 13:39
  • 1
    I'm a bit confused, the solution works well on the data in question. – klin Aug 29 '16 at 13:45
  • I'm sorry, my mistake, my db is so messed up with testing, last solution works correctly (y) – user181452 Aug 29 '16 at 13:50