精通 PostgreSQL JSON 操作的 10 个实用技巧 – wiki基地

作者： /

精通 PostgreSQL JSON 操作的 10 个实用技巧：从入门到高级

在现代应用开发中，JSON (JavaScript Object Notation) 已成为数据交换和存储的事实标准。它的灵活性和无模式（Schema-less）特性，使其在处理半结构化数据时具有无与伦比的优势。许多开发者可能因此转向 NoSQL 数据库，但一个常常被忽视的事实是：关系型数据库的巨擘 PostgreSQL，提供了极其强大和高效的原生 JSON 支持，使其成为一个强大的“混合型”数据库。

正确地利用 PostgreSQL 的 JSON 功能，不仅可以简化应用程序逻辑，还能在保持关系型数据完整性的同时，获得 NoSQL 的灵活性。然而，其丰富的操作符和函数也可能让初学者感到困惑。本文将深入探讨 10 个实用技巧，带您从入门到精通，彻底释放 PostgreSQL 处理 JSON 数据的潜力。

在开始之前，我们先创建一个贯穿全文的示例表：

“`sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
profile JSONB,
created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO users (name, profile) VALUES
(‘Alice’, ‘{
“email”: “[email protected]”,
“active”: true,
“logins”: 99,
“settings”: {“theme”: “dark”, “notifications”: [“email”, “sms”]},
“projects”: [
{“name”: “Project Alpha”, “year”: 2022},
{“name”: “Project Beta”, “year”: 2023}
]
}’),
(‘Bob’, ‘{
“email”: “[email protected]”,
“active”: false,
“logins”: 15,
“settings”: {“theme”: “light”, “notifications”: [“email”]},
“tags”: [“developer”, “go”]
}’),
(‘Charlie’, ‘{
“email”: “[email protected]”,
“active”: true,
“logins”: 250,
“tags”: [“designer”, “ui/ux”]
}’);
“`

技巧一：明智选择 json 与 jsonb——一切的基础

PostgreSQL 提供了两种存储 JSON 的数据类型：json 和 jsonb。虽然名字相似，但其内部工作方式和性能特征却截然不同。做出正确的选择是高效操作 JSON 的第一步。

  • json 类型：

    • 存储方式：以原始文本格式存储。它会完整保留输入 JSON 的所有细节，包括空格、键的顺序以及重复的键。
    • 优点：插入速度稍快，因为它不需要解析和转换。
    • 缺点：查询速度慢。每次查询都需要重新解析整个 JSON 文本。此外，它不支持高级索引（如 GIN 索引）。

  • jsonb 类型：

    • 存储方式：以分解后的二进制格式（Binary）存储。它会解析输入的 JSON，去除不必要的空格，不保证键的顺序，并且如果存在重复的键，只会保留最后一个。
    • 优点：查询效率极高。由于数据是预先解析和结构化的，PostgreSQL 可以直接访问内部元素而无需重新解析。最重要的是，jsonb 支持强大的 GIN 索引，这对于大规模数据集的查询性能是决定性的。
    • 缺点：插入时需要额外的解析和转换开销，因此插入速度比 json 稍慢。

核心建议：除非你有非常特殊的需求（例如，需要审计和保留原始 JSON 文本的完整性），否则请始终使用 jsonb。性能优势和索引支持在绝大多数应用场景中都远比保留原始格式重要。我们的示例表也理智地选择了 jsonb。

技巧二：掌握基础提取操作符 -> 和 ->>

这是最常用、最基础的 JSON 查询操作符，用于从 JSON 对象或数组中提取数据。

  • -> (箭头)：按键或索引提取 JSON 字段，返回的结果是 json 或 jsonb 类型。

    • '{"a": 1, "b": "hello"}'::jsonb -> 'a' 结果是 1 (jsonb 类型)
    • '["A", "B", "C"]'::jsonb -> 1 结果是 "B" (jsonb 类型, 数组索引从0开始)

  • ->> (双箭头)：按键或索引提取 JSON 字段，返回的结果是 text 类型。

    • '{"a": 1, "b": "hello"}'::jsonb ->> 'a' 结果是 '1' (text 类型)
    • '["A", "B", "C"]'::jsonb ->> 1 结果是 'B' (text 类型)

为什么需要区分？

当你需要将提取的值用于比较、拼接或任何需要文本类型的操作时，->> 非常方便。当你需要对提取出的子对象或数组进行进一步的 JSON 操作时，应该使用 ->。

示例：
“`sql
— 查找所有邮箱地址 (返回 text)
SELECT name, profile ->> ’email’ AS email FROM users;

— 查找所有用户的设置 (返回 jsonb，以便可以进一步查询)
SELECT name, profile -> ‘settings’ AS settings FROM users;

— 链式调用：先提取 settings 对象，再提取 theme 文本
SELECT name, profile -> ‘settings’ ->> ‘theme’ AS theme FROM users;
“`

技巧三：使用路径提取 #> 和 #>> 深入嵌套结构

当 JSON 结构层级很深时，使用多个 -> 进行链式调用会显得很冗长。此时，路径提取操作符就派上了用场。它们接受一个文本数组作为路径。

  • #>：按路径提取，返回 jsonb 类型。
  • #>>：按路径提取，返回 text 类型。

示例：
假设我们要获取 Alice 的第一个项目的名称。

传统链式方法：
sql
SELECT profile -> 'projects' -> 0 ->> 'name' AS project_name
FROM users WHERE name = 'Alice';

路径提取方法：
sql
SELECT profile #>> '{projects,0,name}' AS project_name
FROM users WHERE name = 'Alice';
结果都是 'Project Alpha'。显然，路径提取的写法更简洁，尤其是在路径更长或动态构建路径时。

技巧四：存在性与包含性查询 (?, ?|, ?&, @>)

筛选数据是数据库的核心任务。PostgreSQL 为 jsonb 提供了丰富的操作符来检查特定键或值的存在。

  • ? (问号)：检查顶层是否存在指定的 key。
    sql
    -- 查找 profile 中包含 'tags' 键的用户
    SELECT name, profile FROM users WHERE profile ? 'tags';
    -- 结果会返回 Bob 和 Charlie

  • ?| (问号或)：检查顶层是否存在数组中任意一个 key。
    sql
    -- 查找 profile 中包含 'tags' 或 'settings' 的用户
    SELECT name, profile FROM users WHERE profile ?| array['tags', 'settings'];
    -- 结果会返回所有用户

  • ?& (问号与)：检查顶层是否存在数组中所有的 key。
    sql
    -- 查找 profile 中同时包含 'active' 和 'logins' 的用户
    SELECT name, profile FROM users WHERE profile ?& array['active', 'logins'];
    -- 结果会返回所有用户

  • @> (包含)：这是最强大、最常用的一个。它检查左边的 jsonb 值是否“包含”右边的 jsonb 值。包含意味着右边 jsonb 中的所有键值对，在左边的 jsonb 中都能找到。这可以用于深度嵌套的匹配。

    “`sql
    — 查找所有活跃用户 (active: true)
    SELECT name FROM users WHERE profile @> ‘{“active”: true}’;
    — 结果: Alice, Charlie

    — 查找使用暗黑主题的用户 (深度嵌套查询)
    SELECT name FROM users WHERE profile @> ‘{“settings”: {“theme”: “dark”}}’;
    — 结果: Alice

    — 查找标签中包含 “developer” 的用户
    — 注意：对于数组，@> 检查的是数组元素是否被包含
    SELECT name FROM users WHERE profile @> ‘{“tags”: [“developer”]}’;
    — 结果: Bob
    “`

@> 是构建 GIN 索引后性能最高的查询方式之一，务必熟练掌握。

技巧五：释放 GIN 索引的威力，让查询速度起飞

如果没有索引，对 jsonb 列的查询（特别是使用 @>）会触发全表扫描，数据量大时性能会急剧下降。GIN (Generalized Inverted Index，广义倒排索引) 是 jsonb 的最佳搭档。

GIN 索引会为 JSON 文档中的每个键和值（或键值对）创建一个索引项，从而可以极快地定位到包含特定键或值的文档。

创建 GIN 索引：
sql
-- 为 profile 列的所有键值创建一个默认的 GIN 索引
CREATE INDEX idx_gin_users_profile ON users USING GIN (profile);
这个索引会加速 ?, ?|, ?&, 和 @> 等操作符。

更高效的 jsonb_path_ops：
默认的 jsonb_ops GIN 索引会为所有键和值创建索引，索引体积较大。如果你的查询主要集中在使用 @> 操作符，可以使用 jsonb_path_ops，它只索引值的路径，索引更小，性能更高。

sql
-- 创建一个针对 @> 操作符优化的 GIN 索引
CREATE INDEX idx_gin_users_profile_path_ops ON users USING GIN (profile jsonb_path_ops);

性能对比（概念性）：
“`sql
EXPLAIN ANALYZE SELECT name FROM users WHERE profile @> ‘{“city”: “New York”}’;

— 没有索引时: Seq Scan on users (全表扫描)
— 有 GIN 索引时: Bitmap Heap Scan on users using idx_gin_users_profile (索引扫描)
``
在百万级数据量的表上，两者的查询时间可能相差几个数量级（从秒级到毫秒级）。**为jsonb` 列添加 GIN 索引，是从“能用”到“好用”的关键一步。**

技巧六：原地更新 JSON (jsonb_set, jsonb_insert, ||)

数据库操作不只有查询，修改数据同样重要。PostgreSQL 提供了强大的函数来原地修改 jsonb 文档，而无需在应用层读取、修改再写回整个 JSON。

  • jsonb_set(target, path, new_value, [create_missing])：在指定路径上更新或插入值。

    • target: 目标 jsonb 对象。
    • path: 路径数组，如 '{settings,theme}'。
    • new_value: 要设置的新值。
    • create_missing (可选, boolean, 默认true): 如果路径不存在，是否创建它。

    示例：将 Alice 的主题更新为 ‘light’。
    sql
    UPDATE users
    SET profile = jsonb_set(profile, '{settings,theme}', '"light"')
    WHERE name = 'Alice';
    注意：new_value 必须是有效的 jsonb 值，所以字符串要用双引号包起来再用单引号包成 SQL 字符串，即 '"light"'。

  • || (合并操作符)：合并两个 jsonb 对象。如果存在相同的键，右边对象的值会覆盖左边对象的值。
    示例：为 Bob 添加登录次数和新设置。
    sql
    UPDATE users
    SET profile = profile || '{"logins": 20, "settings": {"notifications": ["email", "push"]}}'::jsonb
    WHERE name = 'Bob';
    -- Bob 的 logins 会变成 20，settings 对象被完全替换。

  • - (删除操作符)：从 jsonb 对象中删除一个顶层键，或从数组中删除一个元素。
    sql
    -- 删除 Charlie 的 tags
    UPDATE users
    SET profile = profile - 'tags'
    WHERE name = 'Charlie';

技巧七：玩转 JSON 数组 (jsonb_array_elements, jsonb_array_length, etc.)

处理 JSON 数组是一个非常常见的需求，例如展开数组进行分析或聚合。

  • jsonb_array_length(jsonb_array)：返回数组的长度。
    sql
    -- 查询 Alice 有多少个项目
    SELECT jsonb_array_length(profile -> 'projects') FROM users WHERE name = 'Alice';
    -- 结果: 2

  • jsonb_array_elements(jsonb_array)：将 JSON 数组的每个元素展开为一行 jsonb 值。这是一个集合返回函数，通常与 LATERAL JOIN 结合使用。
    示例：列出 Alice 的所有项目详情。
    sql
    SELECT project
    FROM users,
    jsonb_array_elements(profile -> 'projects') AS project
    WHERE name = 'Alice';
    -- 结果是两行，每行都是一个 project 的 jsonb 对象

  • jsonb_array_elements_text(jsonb_array)：功能同上，但返回的是 text 类型。

高级用法：展开数组后，可以像操作普通表一样进行过滤和聚合。
sql
-- 查找所有在 2023 年之后（含）开始的项目
SELECT u.name, p.project ->> 'name' AS project_name
FROM users u,
LATERAL jsonb_array_elements(u.profile -> 'projects') AS p(project)
WHERE (p.project ->> 'year')::integer >= 2023;

技巧八：JSON 到关系型数据的转换 (jsonb_to_record, jsonb_to_recordset)

有时，你需要将 JSON 对象的字段直接转换为关系型的列，以便进行更复杂的连接或分析。

  • jsonb_to_record(jsonb_object)：将一个顶层 JSON 对象转换为一条记录（一行）。你需要在 AS 子句中定义列的名称和类型。
    示例：将用户的部分 profile 提取为列。
    sql
    SELECT r.*
    FROM users,
    jsonb_to_record(profile) AS r(email text, active boolean, logins int)
    WHERE name = 'Alice';

  • jsonb_to_recordset(jsonb_array)：将一个 JSON 数组（数组中的每个元素都是对象）转换为一个记录集（多行）。
    示例：将 Alice 的项目列表转换为一个表。
    sql
    SELECT p.*
    FROM users,
    jsonb_to_recordset(profile -> 'projects') AS p(name text, year int)
    WHERE name = 'Alice';
    -- 结果会是一个两行两列的表：name | year
    这个功能在需要将 JSON 数据与其它关系型表进行 JOIN 操作时极其有用。

技巧九：关系型数据到 JSON 的聚合 (jsonb_build_object, jsonb_agg)

反向操作同样重要。我们经常需要将关系型表的数据聚合成 JSON 格式，以便于 API 返回或存储。

  • jsonb_build_object(key1, value1, key2, value2, ...)：构建一个 JSON 对象。
  • jsonb_agg(expression)：一个聚合函数，将一个分组内的所有值聚合成一个 JSON 数组。

示例：假设我们还有一个 orders 表。
sql
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
item VARCHAR(50),
price NUMERIC(10, 2)
);
INSERT INTO orders (user_id, item, price) VALUES
(1, 'Laptop', 1200.00), (1, 'Mouse', 25.50), (2, 'Keyboard', 75.00);

现在，我们想查询每个用户，并将其所有订单信息作为一个 JSON 数组嵌套返回。

sql
SELECT
u.name,
u.profile ->> 'email' AS email,
(
SELECT jsonb_agg(
jsonb_build_object('item', o.item, 'price', o.price)
)
FROM orders o
WHERE o.user_id = u.id
) AS user_orders
FROM users u;
这个查询的威力在于，它用一条 SQL 就生成了结构化的嵌套 JSON，避免了在应用层进行多次查询和数据组装的“N+1”问题。

技巧十：利用 SQL/JSON Path 语言进行高级查询 (jsonb_path_query)

从 PostgreSQL 12 开始，引入了对 SQL/JSON Path 语言的支持，这是一种功能更强大的 JSON 查询语言标准。它提供了比传统操作符更精细的控制能力。

语法：jsonb_path_query(target, path)

Path 语言的语法非常丰富，例如：
* $：代表根对象。
* .key：访问对象的键。
* [*]：访问数组的所有元素。
* [index]：访问数组的特定索引。
* ? (filter expression)：进行过滤。

示例：查找 Alice 所有在 2022 年的项目。
sql
SELECT jsonb_path_query(
profile,
'$.projects[*] ? (@.year == 2022)'
) AS project_2022
FROM users
WHERE name = 'Alice';
这条语句的 Path 解释如下：
1. $：从根开始。
2. .projects：进入 projects 键。
3. [*]：遍历数组中的所有元素。
4. ? (...)：应用一个过滤器。
5. @.year == 2022：过滤条件，当前元素的 year 属性等于 2022。

SQL/JSON Path 语言的学习曲线稍陡峭，但它提供了无与伦比的查询灵活性，特别是在处理复杂过滤逻辑时，能够保持 SQL 的声明性，避免复杂的函数嵌套。

结论

PostgreSQL 不仅仅是一个能存储 JSON 的关系型数据库，它是一个真正理解并能高效操作 JSON 的数据平台。通过掌握本文介绍的 10 个技巧——从 jsonb 的明智选择，到基础和路径提取，再到决定性能的 GIN 索引，以及原地更新、数组操作、双向转换和高级的 SQL/JSON Path——你将能够构建出既拥有关系型数据一致性，又兼具 NoSQL 灵活性的强大、高效且可维护的应用程序。

不要再将 PostgreSQL 仅仅视为结构化数据的保险箱。拥抱它的 JSONB 能力，你会发现一个全新的、更加强大的数据处理世界。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部