Re: Further issues with jsonb semantics, documentation - Mailing list pgsql-hackers

On 06/03/2015 10:02 PM, Peter Geoghegan wrote:
> I've noticed some more issues with the jsonb documentation, and the
> new jsonb stuff generally. I didn't set out to give Andrew feedback on
> the semantics weeks after feature freeze, but unfortunately this feels
> like another discussion that we need to have now rather than later.


Yes, I wish you had raised these issues months ago when this was 
published. That's the way the process is supposed to work.



>
> "operator jsonb - integer"
> ===================
>
> Summary: I think that this operator has a problem, but a problem that
> can easily be fixed.
>
>
> I think it was a bad idea to allow array-style removal of object
> key/value pairs. ISTM that it implies a level of stability in the
> ordering that doesn't make sense. Besides, is it really all that
> useful?


The origin of this is nested hstore. Looking at my last version of that 
patch, I see:
   SELECT 'a=>1, b=>2, c=>3'::hstore - 3;            ?column?   ------------------------     "a"=>1, "b"=>2, "c"=>3
(1row)
 

But I agree that it's not a great contribution to science, especially 
since the index will be applied to the list of elements in the somewhat 
counter-intuitive storage order we use, and we could just raise an error 
if we try to apply integer delete to an object instead of an array.





>
> "operator jsonb - text[]" (and *nested* deletion more generally)
> ===============================================
>
> Summary: I think that this operator has many problems, and should be
> scraped (although only as an operator). IMV nested deletion should
> only be handled by functions, and the way that nested deletion works
> in general should be slightly adjusted.
>
>
> The new "operator jsonb - text[]" operator is confusingly inconsistent with:
>
> A) "operator jsonb text"


What exactly is this? I have no idea what you're talking about.


>
> and:
>
> B) the established "operator hstore - text[]" operator, since that
> operator deletes all key/value pairs that have keys that match any of
> the right operand text array values. In contrast, this new operator is
> passed as its right operand an array of text elements that constitute
> a "path" (so the order in the rhs text[] operand matters). If the text
> element in the rhs text[] operand happens to be what would pass for a
> Postgres integer literal, it can be used to traverse lhs array values
> through subscripting at that nesting level.



The fact that hstore uses it that way doesn't really concern me. Since 
hstore isn't nested it doesn't make a whole lot of sense for it to mean 
anything else there. But json(b) is nested, and jsonb - path seems quite 
a reasonable treatment, something you're much more likely to want to do 
than removeing top level elements in bulk.

>
> Regarding nested deletion behavior more generally, consider this
> example of how this can work out badly:
>
> postgres=# select jsonb_delete(jsonb_set('["a"]', '{5}', '"b"'), '{5}')  ;
>   jsonb_delete
> --------------
>   ["a", "b"]
> (1 row)
>
> Here, we're adding and then deleting an array element at offset 5 (the
> string "b"). But the element is never deleted by the outer
> jsonb_delete(), because we can't rely on the element actually being
> stored at offset 5. Seems a bit fragile.


The behaviour of jsonb_set is pretty explicitly documented. If we wanted 
to do something else then we'd have to disable the special meaning given 
to negative indices, but that would mean in turn we wouldn't be able to 
prepend to an array.

>
> More importantly, consider the inconsistency with "operator jsonb
> text" ("point A" above):
>
> postgres=# select '["a"]'::jsonb  ?| '{a}'::text[]; -- historic/9.4 behavior
>   ?column?
> ----------
>   t
> (1 row)
>
> postgres=# select '["a"]'::jsonb  - '{a}'::text[]; -- new to 9.5
> operator, does not delete!
>   ?column?
> ----------
>   ["a"]
> (1 row)


You are conflating two different things here, quite pointlessly. The RH 
operand of ?| is not a path, whereas the RH operand of this - variant 
is. The fact that they are both text arrays doesn't mean that they 
should mean the same thing. And this is really the whole problem with 
the rest of your analysis.



cheers

andrew