在 postgresql 中，如何在 jsonb 键上返回布尔值而不是字符串

只需将文本转换为布尔值:

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)

请注意，您还可以将其他json文本值强制转换为布尔值，例如:

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)

了解布尔类型in the documentation.的有效文本

使现代化

Postgres 11将JSONB标量的强制转换添加到数值和布尔数据类型.此查询仅适用于regular个布尔JSONB标量(即true或false):

select id, data, (data->'is_boolean')::boolean as is_boolean
from jsonb_test
where (data->'is_boolean')::boolean