How to convert JSONB value to boolean in Postgresql?

Question

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

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

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

select ('{"foo": true}'::jsonb)->'foo'; I am returning something like JSONB.

But I can not use JSONB for inference. If instead:

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

I will return to the type string. From this I can convert to logical. (For instance:)

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

But it seems a little icky going from the inner view to the line, and then back to another inner view.

Is it possible to go directly to bool?

My other current best work around:

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

but it also seems a little wrong.

Answer 1

You can use json_to_record :

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

I'm not sure if this will really save you from any internal throws, but this is the closest thing that I know about in 9.4