Re: jsonb concatenate operator's semantics seem questionable

On Mon, May 18, 2015 at 8:41 AM, Ryan Pedela <rpedela(at)datalanche(dot)com> wrote:

> On Sun, May 17, 2015 at 9:41 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> Is there a particular reason why "+" makes more sense as "shallow
>> concatination" and "||" makes more sense as "deep concatination"? Like,
>> something in JS or other client languages which would make that
>> preference make more sense to users?
>>
>
> As someone who uses JSON day-to-day in Javascript and Python, I personally
> don't think || or + matters much. Python uses json.loads() for JSON concat
> and you have use a 3rd-party library in Javascript if you want that
> functionality such as JQuery.extends(). I agree with Peter that we need
> deep concatenation, but I don't think there is any standard for the
> operator. I think the word "shallow" should be added to the docs though.
>
> What is far more important than shallow or deep concatenation for the
> document database use case is being able to delete or replace/update a
> specific, nested path in the JSON object. It looks like that is possible
> with the minus operator and jsonb_replace(). This is great, however it took
> me awhile to figure out the path syntax. I think adding a paragraph to the
> docs explaining the path syntax would help.
>

Having looked at this more, I think I understand the problem Peter has
identified and it is a significant usability problem in my opinion. I think
the word "concatenate" has confused me because I think of it as a
higher-level operation when I want to merge two, large JSON objects which
isn't a very common operation, at least for me. What is absolutely required
for the document database use case is the following:

1. Get element at any arbitrary path. ( #> operator )
2. Delete any arbitrary path. ( minus operator )
3. Replace/update element at any arbitrary path. ( jsonb_replace )
4. Add element to any arbitrary path. ( ? )

It is #4 that does not seem to exist unless jsonb_replace() creates the
specified path if it does not exist. Does it? I am not currently at my desk
to test it myself.

If not, deep concatenation would solve this problem, but I can also see
another solution. Use + for shallow concatenation since it really means
"add element to top-level path" as Peter suggests. Then add another
function: jsonb_add( target jsonb, path text[], new jsonb ) to add element
at any arbitrary path. Then leave || for deep concatenation in 9.6 or
whenever.

If jsonb_replace() satisfies #4 then I think everything is fine. Without #4
however, jsonb would remain an incomplete document database solution in my
opinion.

Thanks,
Ryan Pedela