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

Andrew Dunstan wrote:
> 
> On 06/04/2015 03:16 PM, Alvaro Herrera wrote:
> >I'm just skimming here, but if a jsonb_path type is being proposed,
> >perhaps it would be better not to have operators that take text or
> >text[] as second argument.  We can provide that functionality with just
> >functions.  For example, it will be confusing to have
> >
> >jsonb 'some json value' - '{foo,bar}'
> >
> >operate too differently from
> >
> >jsonb 'some json value' - json_path '{foo,bar}'
> >
> >And it will be a nasty regression to have 9.5 allow
> >jsonb 'some json value' - '{foo,bar}'
> >and then have 9.6 error out with "ambiguous operator" when the json_path
> >thing is added.
> 
> The boat has sailed on this. We have had the #> and #>> operators since 9.3,
> i.e. even before we got the operators that Peter wants us to adopt the usage
> from, and their right hand operands are text arrays with the same path
> semantics.

Well, some boats sailed, but maybe those were different boats.  I don't
think we should shut discussion off only because we made some choice or
other in the past.  Since we haven't released yet, we can base decisions
on what's the most useful API for users, rather on what got committed in
the initial patch.

> '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.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services