Re: jsonb concatenate operator's semantics seem questionable

On Mon, May 18, 2015 at 8:41 AM, Ryan Pedela <rpedela@datalanche.com> wrote:

On Sun, May 17, 2015 at 9:41 PM, Josh Berkus <josh@agliodbs.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