On 1st of June, Andrew Dunstan committed patch:

Rename jsonb_replace to jsonb_set and allow it to add new values
 
The function is given a fourth parameter, which defaults to true. When
this parameter is true, if the last element of the path is missing
in the original json, jsonb_set creates it in the result and assigns it
the new value. If it is false then the function does nothing unless all
elements of the path are present, including the last.
 
Based on some original code from Dmitry Dolgov, heavily modified by me.
 
Catalog version bumped.

It's been around a month, since jsonb_replace was added, and now it got renamed.

But it's good, as we have a change in functionality.

Let's see what happens.

I have this json:

$ select jsonb_pretty( '{"g":[1,2,3],"a":1}'::jsonb );
 jsonb_pretty 
--------------
 {           +
     "a": 1, +
     "g": [  +
         1,  +
         2,  +
         3   +
     ]       +
 }
(1 row)

If i'll change value under key “a", it will work regardless of fourth param:

$ select jsonb_set( '{"g":[1,2,3],"a":1}'::jsonb, ARRAY['a'], '13'::jsonb, true );
         jsonb_set         
---------------------------
 {"a": 13, "g": [1, 2, 3]}
(1 row)
 
$ select jsonb_set( '{"g":[1,2,3],"a":1}'::jsonb, ARRAY['a'], '13'::jsonb, false );
         jsonb_set         
---------------------------
 {"a": 13, "g": [1, 2, 3]}
(1 row)

But, if I'll try to set value to key “b", which doesn't exist in original json:

$ select jsonb_set( '{"g":[1,2,3],"a":1}'::jsonb, ARRAY['b'], '13'::jsonb, true );
             jsonb_set             
-----------------------------------
 {"a": 1, "b": 13, "g": [1, 2, 3]}
(1 row)
 
$ select jsonb_set( '{"g":[1,2,3],"a":1}'::jsonb, ARRAY['b'], '13'::jsonb, false );
        jsonb_set         
--------------------------
 {"a": 1, "g": [1, 2, 3]}
(1 row)

We can also observe that if I miss the fourth argument, it works as if I passed “true":

$ select jsonb_set( '{"g":[1,2,3],"a":1}'::jsonb, ARRAY['b'], '13'::jsonb );
             jsonb_set             
-----------------------------------
 {"a": 1, "b": 13, "g": [1, 2, 3]}
(1 row)

This is definitely nice addition, thanks guys.

Leave a comment