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"');
jsonb_set
-------------
["a value"]
andrew=# select jsonb_set('{}','{x}','"a value"',false);
jsonb_set
-----------
{}
andrew=# select jsonb_set('{"f1":{"f2":{"y":1}}}','{f1,f2,x}','"a value"');
jsonb_set
------------------------------------------
{"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.
Why don't you use thi JSON patch standard : http://jsonpatch.com/ ?
ReplyDeleteI've made this extension : https://github.com/slardiere/pg_jsonpatch
ReplyDeleteIn 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.
ReplyDeleteConverting 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).
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.
ReplyDeleteMy extension is just a POC, but maybe it could be a good idea to have this inside PostgreSQL for jsonb.
That's not what makes something a standard, that just makes it common. Nevertheless, I see that there is an RFC for it.
DeleteFeel free to submit a patch if you want this in Postgres.
A better answer - it's an RFC from the IETF: http://tools.ietf.org/html/rfc6902
ReplyDeleteI 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.
DeleteWhich also uses this JSON Pointer: http://tools.ietf.org/html/rfc6901
ReplyDelete*sigh* feels like XPath/XQuery all over again
Dear Andrew,
ReplyDeleteI 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;
END LOOP;
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?