In postgresql, how can I return a boolean value instead of string on a jsonb key?
25,308
Simply cast a text to boolean:
create table jsonb_test (id int, data jsonb);
insert into jsonb_test values
(1, '{"is_boolean" : true}'),
(2, '{"is_boolean" : false}');
select id, data, (data->>'is_boolean')::boolean as is_boolean
from jsonb_test
where (data->>'is_boolean')::boolean
id | data | is_boolean
----+------------------------+------------
1 | {"is_boolean": true} | t
(1 row)
Note that you can also cast other json text values to boolean, examples:
insert into jsonb_test values
(3, '{"is_boolean" : "true"}'),
(4, '{"is_boolean" : "false"}'),
(5, '{"is_boolean" : "t"}'),
(6, '{"is_boolean" : "f"}'),
(7, '{"is_boolean" : "on"}'),
(8, '{"is_boolean" : "off"}');
select id, data, (data->>'is_boolean')::boolean as is_boolean
from jsonb_test
where (data->>'is_boolean')::boolean
id | data | is_boolean
----+------------------------+------------
1 | {"is_boolean": true} | t
3 | {"is_boolean": "true"} | t
5 | {"is_boolean": "t"} | t
7 | {"is_boolean": "on"} | t
(4 rows)
Read about valid literals for boolean type in the documentation.
Update
Postgres 11 adds casts from JSONB scalars to numeric and boolean data types. This query will work only for regular boolean JSONB scalars (i.e. true
or false
):
select id, data, (data->'is_boolean')::boolean as is_boolean
from jsonb_test
where (data->'is_boolean')::boolean
Related videos on Youtube
Author by
dipole_moment
Dev at a variety of startups around the greater NY area. Tennis & Parkour enthusiast.
Updated on June 19, 2020Comments
-
dipole_moment almost 4 years
In the query below, $isComplete and $isValid are returned as a string. However, they are saved as boolean values. How can I get the boolean representation of these fields to be returned?
query = "SELECT data #>> '{id}' AS id, data #>> '{name}' AS name, data #>> '{curator}' AS curator, data #> '{$isValid}' as \"$isValid\", data #> '{customer}' as customer, data #> '{$createdTS}' as \"$createdTS\", data #> '{$updatedTS}' as \"$updatedTS\", data #> '{$isComplete}' as \"$isComplete\", (count(keys))::numeric as \"numProducts\" FROM appointment_intakes, LATERAL jsonb_object_keys(data #> '{products}') keys GROUP BY id"