Re: json ->> operator precedence

On Wed, Aug 5, 2015 at 5:02 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> 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