PostgreSQL / 系统课时
处理类型转换与缺失值
处理类型转换与缺失值
从 JSONB 提取出的值常常还要转换成数值、布尔值或普通文本参与过滤。真正容易出错的是缺失键、JSON null 与 SQL NULL 混在一起时的行为。本篇用同一组测试数据拆开这些边界,并给出先判断状态和类型、再执行转换的安全写法。
学习目标
- 区分缺失键、JSON
null与 SQL NULL - 安全地把 JSONB 中的数值和布尔值转换为 SQL 类型
- 避免 NULL 三值逻辑和非法类型转换导致误判或查询失败
建立包含边界值的数据
先准备四条记录,覆盖正常数值、JSON null、缺失键,以及无法转换成整数的字符串。后面的查询都复用这组数据。
DROP TABLE IF EXISTS event_demo;
CREATE TABLE event_demo (
id integer PRIMARY KEY,
payload jsonb NOT NULL
);
INSERT INTO event_demo (id, payload) VALUES
(1, '{"score":42,"active":true}'),
(2, '{"score":null,"active":false}'),
(3, '{"active":true}'),
(4, '{"score":"unknown","active":"true"}');
SELECT
id,
payload -> 'score' AS score_jsonb,
payload ->> 'score' AS score_text
FROM event_demo
ORDER BY id;预期结果:
id | score_jsonb | score_text
----+-------------+------------
1 | 42 | 42
2 | null |
3 | |
4 | "unknown" | unknownpayload -> 'score' 保留 JSONB 类型:键存在且值为 JSON null 时返回 JSONB 值 null,键不存在时返回 SQL NULL。payload ->> 'score' 提取文本时,这两种情况都会得到 SQL NULL,因此只看 ->> 已无法区分“明确提供 null”和“没有这个键”。
区分缺失键与 JSON null
判断键是否存在可以使用 ?;判断存在的值是否为 JSON null,则保留 -> 的 JSONB 结果再比较。
SELECT
id,
payload ? 'score' AS has_score,
payload -> 'score' = 'null'::jsonb AS score_is_json_null,
payload -> 'score' IS NULL AS score_is_sql_null
FROM event_demo
ORDER BY id;预期结果:
id | has_score | score_is_json_null | score_is_sql_null
----+-----------+--------------------+-------------------
1 | t | f | f
2 | t | t | f
3 | f | | t
4 | t | f | f第三行的 score_is_json_null 为空白,是因为缺失键使 payload -> 'score' 返回 SQL NULL,而 SQL NULL 与普通值执行 = 比较得到 UNKNOWN。判断缺失值应使用 ? 或 IS NULL,不能使用 = NULL。
在转换前先约束类型
直接把提取出的任意文本强制转换成整数并不安全。下面的查询处理到 "unknown" 时会失败,整条语句也会中止。
SELECT id, (payload ->> 'score')::integer AS score
FROM event_demo
ORDER BY id;预期结果:
ERROR: invalid input syntax for type integer: "unknown"更稳妥的方式是先检查 JSONB 类型,只对 JSON 数字执行转换。jsonb_typeof 对 JSON 数字返回 number,对 JSON null 返回 null,对缺失键返回 SQL NULL。
SELECT
id,
CASE
WHEN jsonb_typeof(payload -> 'score') = 'number'
THEN (payload ->> 'score')::integer
ELSE NULL
END AS score
FROM event_demo
ORDER BY id;预期结果:
id | score
----+------
1 | 42
2 |
3 |
4 |这种写法不会把 JSON 字符串 "42" 当成数字。如果业务明确允许数字字符串,应先用独立的格式规则确认文本可转换,再执行类型转换,而不是对所有文本直接强制转换。
正确处理布尔值
布尔值也应先检查 JSON 类型。PostgreSQL 可以把文本 true 转成 SQL boolean,但如果业务要求字段必须以真正的 JSON 布尔值存储,就应先排除字符串形式。
SELECT
id,
CASE
WHEN jsonb_typeof(payload -> 'active') = 'boolean'
THEN (payload ->> 'active')::boolean
ELSE NULL
END AS active
FROM event_demo
ORDER BY id;预期结果:
id | active
----+--------
1 | t
2 | f
3 | t
4 |第四行被明确排除,因为它保存的是 JSON 字符串 "true",而不是 JSON 布尔值 true。先约束 JSON 类型,可以避免格式不一致的数据悄悄进入后续过滤和统计。
在过滤条件中处理 NULL
SQL NULL 使用三值逻辑。比较表达式遇到 NULL 通常得到 UNKNOWN,因此不会通过 WHERE。如果业务允许把缺失值或 JSON null 当作默认值,可以在安全转换之后使用 COALESCE。
SELECT id
FROM event_demo
WHERE COALESCE(
CASE
WHEN jsonb_typeof(payload -> 'score') = 'number'
THEN (payload ->> 'score')::integer
ELSE NULL
END,
0
) > 10
ORDER BY id;预期结果:
id
----
1当查询必须区分“没有提供”“明确提供 null”和“提供了合法值”三种状态时,应保留 -> 的结果并结合 ? 判断。只有业务语义允许合并这些状态时,才在最后一步转成文本或使用 COALESCE 提供默认值。