Re: Further issues with jsonb semantics, documentation

On Fri, Jun 5, 2015 at 8:32 , Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:
> Andrew Dunstan wrote:
>
>>  'some jsonb value' - '{foo,bar}' is already ambiguous  - the RH
>> operand
>>  could be a single text datum or a text array.
>
> Hmm, but that's not in 9.4, so we can still tweak it if necessary.
>
> Consider this jsonb datum.  Nobody in their right mind would have a
> key
> that looks like a path, I hear you say; yet I'm sure this is going to
> happen.
>
> alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}, "{c,a}":
> "uh"}' ;
>                         jsonb
> ------------------------------------------------------
>  {"a": "1", "b": "2", "c": {"a": "2"}, "{c,a}": "uh"}
> (1 fila)
>
> This seems pretty surprising to me:
>
> -- here, the -(jsonb,text) operator is silently chosen, even though
> the
> -- right operand looks like an array.  And we do the wrong thing.
> alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
> '{c,a}';
>                ?column?
> ---------------------------------------
>  {"a": "1", "b": "2", "c": {"a": "2"}}
> (1 fila)
>
> -- here, the -(jsonb,text[]) operator is chosen
> alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
> _text '{c,a}';
>            ?column?
> -------------------------------
>  {"a": "1", "b": "2", "c": {}}
> (1 fila)
>
> But this seems worse to me, because we silently do nothing:
>
> alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
> '{c,a}';
>                ?column?
> ---------------------------------------
>  {"a": "1", "b": "2", "c": {"a": "2"}}
> (1 fila)
>
>
> I think the first operator can be qualified as dangerous.  If you
> delete
> that one, then it's fine because you can't do that query anymore
> because
> of the conflict with -(jsonb, int).
>
> alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
> '{c,a}';
> ERROR:  operator is not unique: jsonb - unknown
> LÍNEA 1: ...elect jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
> '{c,a}'...
>                                                               ^
> SUGERENCIA:  Could not choose a best candidate operator. You might
> need to add explicit type casts.


That's a good point, and it won't get any better if/when we add the
json point support in 9.6 since the syntax would be something like
select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' - '/c/a'; and we
will again silently do nothing. That's going to cause bugs in
applications using this.

-- Petr Jelinek                  http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services