json ->> operator precedence

From: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: json ->> operator precedence
Date: 2015-08-05 10:02:38
Message-ID: CAEzk6fe-oPwmShJ6vf5h5POGV7j_ucaLv1evkV16+C8=86dyBQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Cheers

Geoff

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John McKown 2015-08-05 13:35:35 Re: json ->> operator precedence
Previous Message Johann Spies 2015-08-05 07:38:28 idle processes