In postgresql, how can I return a boolean value instead of string on a jsonb key?

Question

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" 

Answer 1

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