PostgreSQL / 系统课时

处理类型转换与缺失值

处理类型转换与缺失值

从 JSONB 提取出的值常常还要转换成数值、布尔值或普通文本参与过滤。真正容易出错的是缺失键、JSON null 与 SQL NULL 混在一起时的行为。本篇用同一组测试数据拆开这些边界,并给出先判断状态和类型、再执行转换的安全写法。

学习目标

  • 区分缺失键、JSON null 与 SQL NULL
  • 安全地把 JSONB 中的数值和布尔值转换为 SQL 类型
  • 避免 NULL 三值逻辑和非法类型转换导致误判或查询失败

建立包含边界值的数据

先准备四条记录,覆盖正常数值、JSON null、缺失键,以及无法转换成整数的字符串。后面的查询都复用这组数据。

sql
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;

预期结果:

text
 id | score_jsonb | score_text
----+-------------+------------
  1 | 42          | 42
  2 | null        |
  3 |             |
  4 | "unknown"   | unknown

payload -> 'score' 保留 JSONB 类型:键存在且值为 JSON null 时返回 JSONB 值 null,键不存在时返回 SQL NULL。payload ->> 'score' 提取文本时,这两种情况都会得到 SQL NULL,因此只看 ->> 已无法区分“明确提供 null”和“没有这个键”。

区分缺失键与 JSON null

判断键是否存在可以使用 ?;判断存在的值是否为 JSON null,则保留 -> 的 JSONB 结果再比较。

sql
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;

预期结果:

text
 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" 时会失败,整条语句也会中止。

sql
SELECT id, (payload ->> 'score')::integer AS score
FROM event_demo
ORDER BY id;

预期结果:

text
ERROR:  invalid input syntax for type integer: "unknown"

更稳妥的方式是先检查 JSONB 类型,只对 JSON 数字执行转换。jsonb_typeof 对 JSON 数字返回 number,对 JSON null 返回 null,对缺失键返回 SQL NULL。

sql
SELECT
    id,
    CASE
        WHEN jsonb_typeof(payload -> 'score') = 'number'
        THEN (payload ->> 'score')::integer
        ELSE NULL
    END AS score
FROM event_demo
ORDER BY id;

预期结果:

text
 id | score
----+------
  1 |   42
  2 |
  3 |
  4 |

这种写法不会把 JSON 字符串 "42" 当成数字。如果业务明确允许数字字符串,应先用独立的格式规则确认文本可转换,再执行类型转换,而不是对所有文本直接强制转换。

正确处理布尔值

布尔值也应先检查 JSON 类型。PostgreSQL 可以把文本 true 转成 SQL boolean,但如果业务要求字段必须以真正的 JSON 布尔值存储,就应先排除字符串形式。

sql
SELECT
    id,
    CASE
        WHEN jsonb_typeof(payload -> 'active') = 'boolean'
        THEN (payload ->> 'active')::boolean
        ELSE NULL
    END AS active
FROM event_demo
ORDER BY id;

预期结果:

text
 id | active
----+--------
  1 | t
  2 | f
  3 | t
  4 |

第四行被明确排除,因为它保存的是 JSON 字符串 "true",而不是 JSON 布尔值 true。先约束 JSON 类型,可以避免格式不一致的数据悄悄进入后续过滤和统计。

在过滤条件中处理 NULL

SQL NULL 使用三值逻辑。比较表达式遇到 NULL 通常得到 UNKNOWN,因此不会通过 WHERE。如果业务允许把缺失值或 JSON null 当作默认值,可以在安全转换之后使用 COALESCE。

sql
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;

预期结果:

text
 id
----
  1

当查询必须区分“没有提供”“明确提供 null”和“提供了合法值”三种状态时,应保留 -> 的结果并结合 ? 判断。只有业务语义允许合并这些状态时,才在最后一步转成文本或使用 COALESCE 提供默认值。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

处理类型转换与缺失值

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通