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"
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