How do I convert a JSONB value to boolean in Postgresql?

Question

What is the best way to convert a boolean field from a JSONB object into a normal bool type in Postgres?

For example I have a JSON object {"foo": true}, which I can use in Postgresql. E.g.: select ('{"foo": true}'::jsonb); which gives me something of type JSONB.

Now I want to extract the foo field as a boolean. If I do:

select ('{"foo": true}'::jsonb)->'foo';

I get back something of type JSONB.

But, I can't cast JSONB to boolean. If instead of I do:

select ('{"foo": true}'::jsonb)->>'foo';

I will get back something of type string. From that I can convert to a boolean. (E.g:)

select (('{"foo": true}'::jsonb)->>'foo')::bool;

But that seems a bit icky going from internal representation to string, and then back to another internal representation.

Is there any to go directly to a bool?

My other current best work around seems to be:

select (('{"foo": true}'::jsonb)->'foo') = 'true'::jsonb;

but that also seems a bit wrong.

Answer 1

You can use json_to_record:

select foo
from json_to_record('{"foo": true}') as x(foo bool);

I am not sure if that really saves you any internal casts, but it's the closest thing I know of in 9.4