Re: Further issues with jsonb semantics, documentation

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
(1 row)

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