在 postgresql 中，如何在 jsonb 键上返回布尔值而不是字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33041617/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In postgresql, how can I return a boolean value instead of string on a jsonb key?
提问by dipole_moment
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?
在下面的查询中，$isComplete 和 $isValid 作为字符串返回。但是，它们被保存为布尔值。如何获取要返回的这些字段的布尔表示？
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"
回答by klin
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:
请注意，您还可以将其他 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)
Read about valid literals for boolean type in the documentation.
Update
更新
Postgres 11adds casts from JSONB scalars to numeric and boolean data types. This query will work only for regularboolean JSONB scalars (i.e. trueor false):
Postgres 11添加了从 JSONB 标量到数字和布尔数据类型的转换。此查询仅适用于常规布尔 JSONB 标量（即true或false）：
select id, data, (data->'is_boolean')::boolean as is_boolean
from jsonb_test
where (data->'is_boolean')::boolean
