Re: problem with precendence order in JSONB merge operator

On Tue, Mar 22, 2016 at 1:52 PM, Peter Krauss <ppkrauss(at)gmail(dot)com> wrote:

> Seems that parser not using precedence ideal order, and that casting
> obligation losts performance.
>
> The first problem is self-evident in this example:
>
> SELECT '{"x":1}'::jsonb || (('{"A":{"y":2}}'::jsonb)->'A')
> -- it is ok, expected result with (x,y)
> SELECT '{"x":1}'::jsonb || '{"A":{"y":2}}'::jsonb)->'A'
> -- non-expected result (y).
>
> Higher precedence <https://en.wikipedia.org/wiki/Order_of_operations> most
> be for -> operator, that is like an object-oriented *path* operator,
> always higher than algebric ones.
>
​There is presently no formal concept of "path operator" in PostgreSQL.
"->" is a user-defined operator, as is "||"​ and thus have equal
precedence and left associativity.

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html

Regardless, "||" is not an "algebric" [sic] operator...I'm curious what
source you are using to back your claim of operator precedence between
different so-called "operator types".

Its highly undesirable to make changes to operator precedence.

Operators are simply symbols to the parser - there is no context involved
that would allow making their precedence dynamic. So all PostgreSQL sees
is "||", not a "JSONB merge operator".

Other problem is using this operation as SQL function
>
> CREATE FUNCTION term_lib.junpack(jsonb,text) RETURNS JSONB AS $f$
> SELECT ($1-$2)::JSONB || ($1->>$2)::JSONB;
> $f$ LANGUAGE SQL IMMUTABLE;
>
> without casting produce error. Perhaps will be "more friendly" without
> cast obligation,
>
> and it is a performance problem, the abusive use of castings losts
> performance.
>
I cannot make this work...

version
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit

SELECT ('{"a":1,"b":2}'::jsonb - 'b'::text)::jsonb ||
('{"a":1,"b":2}'::jsonb #> 'b'::text)::jsonb

> ​SQL Error: ERROR: invalid concatenation of jsonb objects
​
This seems like user error but without a self-contained test case
exercising the query (the use of a function in this context should be
immaterial) I'm finding it hard to explain why. My simple case returns a
non-object with rightly cannot be appended to an object.

In isolatoin you can avoid casting the RHS of the || operator by using the
"#>(jsonb,text[])" operator

SELECT pg_typeof('{"a":1,"b":{"c":2}}'::jsonb #> array['b']::text[]) --jsonb

JSON, IME, still needs some fleshing out. Efficient usage might require
additional features but for now one needs to get very familiar with all the
various operator variants that allow the user to choose whether to return
json or text and to pick the correct one for their needs.

​David J.
​