Search Postgresql Archives

Re: json ->> operator precedence

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Aug 5, 2015 at 5:02 AM, Geoff Winkless <pgsqladmin@xxxxxxxx> wrote:
An interesting quirk:

select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no' END;
 case
------
 yes

According to the precedence table http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would expect ->> to come under "all other native and user-defined operators", which would imply that this command should be testing whether 'a' IS NULL and applying the result (false) to the json operator - at which point we have 

# SELECT CASE WHEN '{"a":null}'::jsonb->>false THEN 'yes' ELSE 'no' END;

and since

# SELECT '{"a":null}'::jsonb->>false;

returns NULL, the query is effectively:

# SELECT CASE WHEN NULL THEN 'yes' ELSE 'no' END;

which returns 'no'.

So the only way that we should get 'yes' is if the ->> has higher precedence than 'IS NULL'.

OK, so be it; except if we assume that the reason is because the lex analyzer sees '-' and assumes higher precedence than 'IS NULL' then you would expect

SELECT '{"a":10}'::jsonb->>'a' - 5;

to return '5' - since left-to-right precedence would make ->> run before the subtraction; however I get:

ERROR:  invalid input syntax for integer: "a"
LINE 1:
​​
select '{"a":10}'::jsonb->>'a' - 5;
 

So what precedence level is ->> actually running at?

Or am I missing something?

​​

​Looks correct to me. As I understand it the ::jsonb is NOT an operator! It is a syntactic construct for a CAST(). An equivalent which might make more sense is:

select CASE WHEN CAST('{"a":null}' AS JSONB)->>'a' IS NULL THEN 'yes' ELSE 'no' END;​​

Oh, an CAST() may look like a function call, but it is also a syntactic element. I.e. there is not a function called "CAST". 


Cheers

Geoff



--

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux