Sunday, May 31, 2015

Goodbye jsonb_replace, hello jsonb_set

After a little controversy on the PostgreSQL Hackers mailing list, I got the idea loud and clear that some users weren't happy with the new jsonb features, and in particular the fact that they weren't getting a way to add values to structures nested well inside the jsonb object. Eventually I came up with the idea that we should do this via an enhanced jsonb_replace function. This function gained an additional boolean parameter, defaulting to true, and when it is true the function will not only replace existing items but if the last element of the path designates an object key or array element that is not present, it will be created with the new value. Since this is no longer just replacement of existing values, the function has been renamed jsonb_set. These changes are based in some original code from Dmitry Dolgov, heavily modified by be, and they have just been committed for inclusion in release 9.5. Here are a few examples:
andrew=# select jsonb_set('[]','{0}','"a value"');
 ["a value"]

andrew=# select jsonb_set('{}','{x}','"a value"',false);

andrew=# select jsonb_set('{"f1":{"f2":{"y":1}}}','{f1,f2,x}','"a value"');
 {"f1": {"f2": {"x": "a value", "y": 1}}}

Although this is actually a fairly small change, in terms of code, its impact could be quite big. I believe it will add significantly to our compelling JSON story.


  1. Why don't you use thi JSON patch standard : ?

  2. I've made this extension :

  3. In what sense is this a standard? What standards body has published it? One of the things that bedevils the json/javascript world is a proliferation of a million ways of doing the same thing. Just see recent complaints about the multitude of Javascript application frameworks.

    Converting jsonb to json to apply an operation and then converting it back to jsonb is a recipe for serious inefficiency, so I'm not terribly impressed with your extension (which also relies on having plv8 available and a module loaded).

  4. This is a standard because there is a consensus, and a decent number of implementation(not only javascript), as you can see on the webpage above.

    My extension is just a POC, but maybe it could be a good idea to have this inside PostgreSQL for jsonb.

    1. That's not what makes something a standard, that just makes it common. Nevertheless, I see that there is an RFC for it.

      Feel free to submit a patch if you want this in Postgres.

  5. A better answer - it's an RFC from the IETF:

    1. I noticed that. One thing I don't like about it is making multiple passes over the structure, at least in a naive implementation. Copy and move operations might be interesting to manage, too.

  6. Which also uses this JSON Pointer:
    *sigh* feels like XPath/XQuery all over again

  7. Dear Andrew,

    I use pl/pgSQL for preparing report.
    Inside this function I select into jsonb object about 50 000 records with 20 fields each from table.
    Then I loop thru jsonb array and do some manipulations in jsonb object

    It is looks like
    i1 - integer
    jb1 - jsonb array with ~50000 records

    FOR i1 IN 0..jsonb_array_length(jb1) - 1 LOOP

    jb1 := jsonb_set(jb1, ('{'||i1||',destination_number}')::text[], to_jsonb('hello'::text));

    i1 := i1 + 1;


    As you can see, each loop jsonb_set return new jsonb object.

    How about perfomance in this case?
    May better add new function which will update exist jsonb instead create new one?