Re: jsonb concatenate operator's semantics seem questionable - Mailing list pgsql-hackers

On 05/20/2015 02:11 AM, Peter Geoghegan wrote:
> On Tue, May 19, 2015 at 10:43 PM, Petr Jelinek <petr@2ndquadrant.com> wrote:
>> I am of strong opinion that concat should be shallow by default. Again it's
>> how jquery works by default, it's how python's dict.update works and you can
>> find this behavior in other languages as well when dealing with nested
>> hashes. It's also how json would behave if you'd just did string
>> concatenation (removing the outermost curly brackets) and parse it to json
>> afterwards.
> As I said, that argument might be a good one if you were able to
> subscript jsonb and have the update affect one particular subdocument.
> You're not, though -- updating jsonb usually requires you to write an
> SQL expression that evaluates to the final jsonb document that you'd
> like to update a record to contain.
>
>> I think this whole discussion shows primarily that it's by far not
>> universally agreed if concatenation of json should be shallow or deep by
>> default and AFAICS this is true even in javascript world so we don't really
>> have where to look for precedents.
>>
>> Given the above I would vote to just provide the function and leave out the
>> || operator for now.
> I've said my piece; I think it's a mistake to use an operator that has
> a certain association, the association that the concatenate operate
> got from hstore. || is the operator broadly useful for updates in
> people's minds. I think this *positioning* of the operator is a
> mistake. I'll leave it at that.
>


OK, I'm going to suggest a way out of this. ISTM the real trouble is 
that you're wanting to shoehorn a meaning onto || which many people 
don't think it should have. || doesn't mean "update" to me, it means 
"concatenate", which in the json context means
    '{ items1}' || '{items2}' = '{items1, items2}'

That's 100% consistent not only with hstore but with the use of this 
operator for strings and arrays. The fact that it's used as the way to 
update hstore is a byproduct of the way hstore works rather than a 
fundamental part of the meaning of ||. If hstore's rule were "first one 
wins" instead of "last one wins" we'd have to use something else.

But leaving that aside, your real gripe is that we don't currently have 
any way of adding a value somewhere nested inside json.

So Dmitry, at my suggestion, has come up with a way of doing that, by 
adding a parameter to jsonb_replace(). If this parameter is set to true 
(it defaults to false) and the key or array element pointed to by the 
last element of the path doesn't exist, it gets created.

Examples:
   andrew=# select   jsonb_replace('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":"bar"}',   true);
         jsonb_replace   --------------------------------------------------------------     {"a": 1, "b": [0, 1, 2],
"c":{"d": 4, "e": {"foo": "bar"}}}   (1 row)
 
   andrew=# select   jsonb_replace('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":"bar"}',   false);
jsonb_replace   -----------------------------------------     {"a": 1, "b": [0, 1, 2], "c": {"d": 4}}   (1 row)
 
   andrew=# select   jsonb_replace('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":"bar"}');
jsonb_replace  -----------------------------------------     {"a": 1, "b": [0, 1, 2], "c": {"d": 4}}   (1 row)
 


This seems to me a much more straightforward way of adding a value 
inside a jsonb than any operator can offer.

This is actually a tiny change - less than 200 lines - and given the 
evident angst over this issue, I'm prepared to incorporate it. I'm still 
working on the array piece, will have it done later today, but the 
object field piece just works.

If we do we might want to reconsider the name of jsonb_replace - maybe 
call it jsonb_set.

So, can we bend the rules just a tad to do this and (I hope) make a lot 
of people a lot happier?

cheers

andrew