Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why is JSONB field automatically cast as TEXT?

Why is JSONB field automatically cast as TEXT?

From
Ben Uphoff
Date:

First post here at PostgreSQL; please forgive any etiquette mistakes…

 

I have a query that extracts a field from a JSONB column, e.g.:

 

SELECT (((mytable.ajsonbcolumn -> ‘somedata’) -> ‘nested’) ->> ‘first_name’) AS fname FROM mytable

 

When I save it into a view, PostgreSQL transforms it thusly:

 

SELECT (((mytable.ajsonbcolumn -> ‘somedata’::text) -> ‘nested’::text) ->> ‘first_name’::text) AS fname FROM mytable

 

(note the ::text casts).

 

Why does it do this? It seems unnecessary and pollutes my SQL with a ton of extra text.

 

Thanks for your thoughts. -Ben

 

******************* PLEASE NOTE ******************* This E-Mail/telefax message and any documents accompanying this transmission may contain information that is privileged, confidential, and/or exempt from disclosure under applicable law and is intended solely for the addressee(s) named above. If you are not the intended addressee/recipient, you are hereby notified that any use of, disclosure, copying, distribution, or reliance on the contents of this E-Mail/telefax information is strictly prohibited and may result in legal action against you. Please reply to the sender advising of the error in transmission and immediately delete/destroy the message and any accompanying documents. Thank you.

Re: Why is JSONB field automatically cast as TEXT?

From
"David G. Johnston"
Date:
On Monday, September 17, 2018, Ben Uphoff <buphoff@villagemd.com> wrote:

SELECT (((mytable.ajsonbcolumn -> ‘somedata’::text) -> ‘nested’::text) ->> ‘first_name’::text) AS fname FROM mytable

It’s casting the untyped literal constants (somedata, neated, first_name) to text because everything must be typed.  It is not casting the first or intermediate jsonb results to text.  The final output is text because of the ->> operator.

:: binds more tightly than the other operators.

Jsonb->('somedata'::text)

David J.