Обсуждение: problem with precendence order in JSONB merge operator
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 most be for -> operator, that is like an object-oriented path operator, always higher than algebric ones.
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.
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 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.
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.
Subjective notes to contextualize (try to explain on bad-English) my "precedence order" and JSONB visions:
JSONB is the "first class" datatype for user community, we expected years (!) for it ... Need some "first class" and friendly behaviour.
In this context JSONB is not "any other" datatype, it is the bridge between relational data and flexible data...
It is the Holy Grail and the Rosetta Stone :-)
I think JSONB operators need some more attention, in semantic and usability contexts. If you want to add some friendliness and orthogonality in JSONB operators, will be natural to see
-> operator as a kind of object-oriented path operator... By other hand, of course, you can do the simplest to implement JSONB... But you do a lot (!), it was not easy to arrive here, and need only a little bit more to reach perfection ;-)
2016-03-22 18:42 GMT-03:00 David G. Johnston <david.g.johnston@gmail.com>:
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 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.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...versionPostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bitSELECT ('{"a":1,"b":2}'::jsonb - 'b'::text)::jsonb || ('{"a":1,"b":2}'::jsonb #> 'b'::text)::jsonb> SQL Error: ERROR: invalid concatenation of jsonb objectsThis 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[])" operatorSELECT pg_typeof('{"a":1,"b":{"c":2}}'::jsonb #> array['b']::text[]) --jsonbJSON, 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.
Please don't top-post.
Subjective notes to contextualize (try to explain on bad-English) my "precedence order" and JSONB visions:JSON datatype is perfect as workaround, and for many simple and less exigent applications.JSONB is the "first class" datatype for user community, we expected years (!) for it ... Need some "first class" and friendly behaviour.In this context JSONB is not "any other" datatype, it is the bridge between relational data and flexible data...It is the Holy Grail and the Rosetta Stone :-)I think JSONB operators need some more attention, in semantic and usability contexts. If you want to add some friendliness and orthogonality in JSONB operators, will be natural to see->operator as a kind of object-oriented path operator...By other hand, of course, you can do the simplest to implement JSONB... But you do a lot (!), it was not easy to arrive here, and need only a little bit more to reach perfection ;-)
Any change you make in this area will effect every usage of that operator whether part of core or end-user defined. We have baggage that limits our ability to be perfect.
So while I'll agree with your premise I don't see (really, don't care to look for) a way to make your desire a reality. But you and smarter people than I are welcome to dive into the code and see if you can come up with something that works.
David J.