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

Question

In the request below, $ isComplete and $ isValid are returned as a string. However, they are saved as logical values. How can I get a logical view of these fields to return?

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

Just enter the text in 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 use other json text values ​​for 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 a boolean type in the documentation.