PostgreSQL JSONB 全面指南：从入门到精通 – wiki基地

作者： /

PostgreSQL JSONB 全面指南：从入门到精通

引言：为什么选择 PostgreSQL JSONB？

在现代应用开发中，数据模式的灵活性变得前所未有的重要。传统的关系型数据库以其严格的模式、事务完整性和强大的查询能力而著称，但面对半结构化数据（如配置文件、日志、API 响应）时，常常显得力不从心。另一方面，NoSQL 数据库（如 MongoDB）因其无模式（Schema-less）的特性而崛起，为开发者提供了极大的灵ibility。

PostgreSQL 巧妙地融合了这两个世界的优点，通过其强大的 JSONB 数据类型，为开发者提供了一个在同一数据库中同时享受关系型数据完整性和 NoSQL 式灵活性的解决方案。JSONB 不仅仅是简单地将 JSON 文本存储在数据库字段中，它是一个经过高度优化的二进制格式，支持复杂的查询、强大的索引，性能卓越。

本指南将带您深入探索 PostgreSQL JSONB 的世界，从最基础的概念和操作开始，逐步深入到高级查询、函数、性能优化（特别是索引策略），以及最终的最佳实践和设计模式。无论您是初学者还是有一定经验的开发者，本文都将为您提供一个系统而全面的知识框架。

第一部分：JSONB 基础 – 一切的开始

在深入操作之前，我们必须理解 JSONB 的核心——它与普通的 JSON 类型有何不同。

1.1 JSON vs. JSONB：关键区别

PostgreSQL 实际上提供了两种原生的 JSON 类型：json 和 jsonb。

  • json 类型：

    • 存储方式：以 文本（Text） 形式完全按照输入的样子存储。
    • 特性：保留原始 JSON 的所有细节，包括空格、键的顺序以及重复的键。
    • 性能：每次查询时，都需要重新解析整个 JSON 文本，因此处理速度较慢。
    • 优点：插入速度非常快，因为它不需要任何处理。

  • jsonb 类型：

    • 存储方式：以 二进制（Binary） 格式存储，是一种分解、解析后的表现形式。
    • 特性：不保留多余的空格和键的顺序。如果输入中有重复的键，它只会保留 最后一个。
    • 性能：由于数据是预解析的，查询和处理速度远快于 json 类型。更重要的是，jsonb 支持 索引，这是实现高性能查询的关键。
    • 缺点：插入时需要额外的转换开销，比 json 类型稍慢。

结论：除非有特殊需求要保留 JSON 原始的文本格式（例如，审计日志），否则在所有情况下都 强烈推荐使用 JSONB。它的查询性能和索引能力是其压倒性的优势。

1.2 创建包含 JSONB 列的表

创建一个带有 JSONB 列的表非常简单，语法与其他数据类型无异。让我们创建一个 products 表来贯穿本文的示例。

“`sql
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
tags TEXT[],
details JSONB
);

— 插入一些示例数据
INSERT INTO products (name, tags, details) VALUES
(‘Laptop Pro’, ARRAY[‘electronics’, ‘computer’], ‘{“brand”: “TechCorp”, “price”: 1200, “specs”: {“cpu”: “i7”, “ram”: 16, “storage”: “512GB SSD”}, “available”: true, “models”: [“silver”, “space gray”]}’),
(‘Wireless Mouse’, ARRAY[‘electronics’, ‘accessory’], ‘{“brand”: “ClickFast”, “price”: 50, “wireless”: true, “battery”: “AA”, “available”: true}’),
(‘Mechanical Keyboard’, ARRAY[‘electronics’, ‘accessory’], ‘{“brand”: “TypeWell”, “price”: 150, “specs”: {“switches”: “blue”, “layout”: “104-key”}, “available”: false, “backlight”: “RGB”}’),
(‘4K Monitor’, ARRAY[‘electronics’, ‘display’], ‘{“brand”: “TechCorp”, “price”: 450, “resolution”: “3840×2160”, “ports”: [“HDMI”, “DisplayPort”, “USB-C”]}’);
“`

现在，我们有了一个包含产品核心信息（如 id, name）和灵活的 details（使用 JSONB）的表。

第二部分：JSONB 数据操作 – 增删改查（CRUD）

掌握了基础，接下来我们学习如何对 JSONB 数据进行核心操作。

2.1 查询（Querying）：提取数据

这是 JSONB 最常用、最强大的功能。PostgreSQL 提供了一系列操作符来访问 JSONB 内部的数据。

  • -> 操作符：按键获取 JSON 对象字段，返回的结果是 jsonb 类型。
  • ->> 操作符：按键获取 JSON 对象字段，返回的结果是 text 类型。这在 WHERE 子句中进行值比较时至关重要。
  • #> 操作符：按路径（path）获取 JSON 对象字段，返回 jsonb。路径用一个文本数组表示。
  • #>> 操作符：按路径获取 JSON 对象字段，返回 text。

示例：

“`sql
— 1. 获取所有产品的品牌 (返回 text)
SELECT name, details->>’brand’ AS brand FROM products;
— 结果:
— name | brand
— ———————+———–
— Laptop Pro | TechCorp
— Wireless Mouse | ClickFast
— Mechanical Keyboard | TypeWell
— 4K Monitor | TechCorp

— 2. 获取价格大于 100 的产品
— 注意：->>’price’ 返回的是 text，需要显式转换为数值类型
SELECT name, details->>’price’ FROM products WHERE (details->>’price’)::numeric > 100;
— 结果:
— name | price
— ———————+——-
— Laptop Pro | 1200
— Mechanical Keyboard | 150
— 4K Monitor | 450

— 3. 获取笔记本电脑的 CPU 型号 (使用路径)
— details -> ‘specs’ -> ‘cpu’
SELECT name, details#>>'{specs,cpu}’ AS cpu FROM products WHERE name = ‘Laptop Pro’;
— 结果:
— name | cpu
— ————+—–
— Laptop Pro | i7

— 4. 获取笔记本电脑的第一个可用型号 (访问数组)
— 数组索引从 0 开始
SELECT name, details->’models’->>0 AS first_model FROM products WHERE name = ‘Laptop Pro’;
— 结果:
— name | first_model
— ————+————-
— Laptop Pro | silver
“`

2.2 更新（Updating）：修改数据

更新 JSONB 数据通常使用 jsonb_set 函数或 || 连接操作符。

  • jsonb_set(target, path, new_value, [create_if_missing]): 在指定路径上设置新值。

    • target: 目标 JSONB 对象。
    • path: text[] 类型的路径。
    • new_value: 要设置的新值（jsonb 类型）。
    • create_if_missing (可选, boolean, 默认为 true): 如果路径不存在，是否创建它。

  • || 连接操作符: 合并两个 JSONB 对象。如果存在相同的键，右侧对象的值会覆盖左侧的。

  • - 删除操作符: 从 JSONB 对象中删除一个顶层键。

  • #- 删除操作符: 按路径删除一个键值对。

示例：

“`sql
— 1. 更新笔记本电脑的价格为 1150
UPDATE products
SET details = jsonb_set(details, ‘{price}’, ‘1150’)
WHERE name = ‘Laptop Pro’;

— 2. 为笔记本电脑添加一个新的 ‘warranty’ 字段
UPDATE products
SET details = jsonb_set(details, ‘{warranty}’, ‘”2 years”‘)
WHERE name = ‘Laptop Pro’;

— 3. 使用 || 操作符同时更新品牌和添加库存状态
UPDATE products
SET details = details || ‘{“brand”: “TechCorp Inc.”, “in_stock”: 50}’::jsonb
WHERE name = ‘Laptop Pro’;
— 更新后，brand 变为 “TechCorp Inc.”，并新增了 “in_stock”: 50

— 4. 删除无线鼠标的 ‘battery’ 字段
UPDATE products
SET details = details – ‘battery’
WHERE name = ‘Wireless Mouse’;

— 5. 删除机械键盘的 ‘layout’ 字段（深层路径）
UPDATE products
SET details = details #- ‘{specs,layout}’
WHERE name = ‘Mechanical Keyboard’;
“`

第三部分：JSONB 查询进阶与函数

除了基本的提取和更新，JSONB 还支持更复杂的查询逻辑，如存在性、包含性检查，并提供了一系列强大的函数来处理和转换数据。

3.1 存在性与包含性操作符

这些操作符是 JSONB 索引（特别是 GIN 索引）能够高效工作的核心。

  • ? (存在): 检查一个 text 字符串是否存在于 JSONB 对象的顶层键中。
  • ?| (存在任意): 检查一个 text 数组中的任意一个键是否存在。
  • ?& (存在所有): 检查一个 text 数组中的所有键是否存在。
  • @> (包含): 检查左侧的 JSONB 值是否包含右侧的 JSONB 值。这是 最重要、最常用 的查询操作符。它可以检查键的存在、键值对的匹配、数组元素的包含等。
  • <@ (被包含于): 与 @> 相反，检查左侧的 JSONB 值是否被右侧的 JSONB 值包含。

示例：

“`sql
— 1. 查找所有包含 ‘backlight’ 键的产品
SELECT name, details FROM products WHERE details ? ‘backlight’;
— 结果: Mechanical Keyboard

— 2. 查找所有品牌为 “TechCorp” 的产品
— 注意右侧的值必须是合法的 JSONB
SELECT name FROM products WHERE details @> ‘{“brand”: “TechCorp”}’;
— 结果: Laptop Pro, 4K Monitor

— 3. 查找所有可用 (available: true) 且品牌为 “TechCorp” 的产品
SELECT name FROM products WHERE details @> ‘{“brand”: “TechCorp”, “available”: true}’;
— 结果: Laptop Pro

— 4. 查找规格中包含 “cpu” 键的产品
SELECT name FROM products WHERE details @> ‘{“specs”: {“cpu”: “i7”}}’; — 错误的方式
— 正确的方式是检查 ‘specs’ 对象是否包含 ‘cpu’ 键
SELECT name FROM products WHERE details->’specs’ ? ‘cpu’;
— 或者使用路径存在性检查（更高级，需要扩展）

— 5. 查找端口中包含 “HDMI” 的显示器
SELECT name FROM products WHERE details->’ports’ @> ‘[“HDMI”]’;
— 结果: 4K Monitor
“`

3.2 常用的 JSONB 函数

PostgreSQL 提供了一套丰富的函数来解构、聚合和转换 JSONB 数据。

  • jsonb_each(jsonb) / jsonb_each_text(jsonb): 将一个 JSONB 对象的顶层键值对展开成 key/value 行。
  • jsonb_object_keys(jsonb): 返回一个 JSONB 对象的所有顶层键。
  • jsonb_array_elements(jsonb) / jsonb_array_elements_text(jsonb): 将一个 JSONB 数组的每个元素展开成一行。
  • jsonb_typeof(jsonb): 返回 JSONB 值的顶层类型（object, array, string, number, boolean, null）。
  • jsonb_strip_nulls(jsonb): 递归地从 JSONB 对象中移除所有 null 值的键。
  • jsonb_pretty(jsonb): 将 JSONB 值格式化为带缩进的文本，方便阅读。

示例：

“`sql
— 1. 展开笔记本电脑的顶层详情
SELECT (jsonb_each_text(details)).* FROM products WHERE name = ‘Laptop Pro’;
— 结果会是多行，每行一个 key 和 value

— 2. 获取所有产品详情中的顶层键
SELECT DISTINCT jsonb_object_keys(details) FROM products;

— 3. 列出 4K 显示器的所有端口
SELECT jsonb_array_elements_text(details->’ports’) AS port FROM products WHERE name = ‘4K Monitor’;
— 结果:
— port

— HDMI
— DisplayPort
— USB-C
“`

第四部分：性能优化 – JSONB 索引

没有索引的 JSONB 查询在大数据量下会进行全表扫描，性能会急剧下降。正确使用索引是精通 JSONB 的关键。JSONB 主要支持两种类型的索引：GIN 和 B-Tree。

4.1 GIN (Generalized Inverted Index) 索引

GIN 索引是 JSONB 的 默认和首选索引。它会为 JSONB 文档中的每一个键和值都创建索引条目。这使得它非常适合用于检查存在性和包含性。

GIN 索引支持的操作符：@>, <@, ?, ?|, ?&。

创建 GIN 索引：

sql
-- 为 details 列创建一个标准的 GIN 索引
CREATE INDEX idx_gin_products_details ON products USING GIN (details);

创建了这个索引后，前面示例中所有使用 @> 或 ? 的查询都会自动利用该索引，速度得到极大提升。

sql
-- 这个查询现在会非常快
EXPLAIN ANALYZE
SELECT name FROM products WHERE details @> '{"brand": "TechCorp"}';
在执行计划中，你会看到 Bitmap Index Scan on idx_gin_products_details，表明索引正在被使用。

GIN 索引的 jsonb_path_ops 优化：
默认的 GIN 索引会索引所有的键和值。如果你只关心 @> 包含性查询，并且不关心键的存在性查询（?），你可以使用 jsonb_path_ops 操作符类。这种索引更小，更新更快。

sql
CREATE INDEX idx_gin_path_ops_products_details ON products USING GIN (details jsonb_path_ops);
这个索引只支持 @> 和 <@ 操作符，但对于纯包含性查询，性能可能更好。

4.2 B-Tree 索引

B-Tree 索引是标准的关系型数据库索引，适用于等值比较、范围查询和排序。你 不能直接在 JSONB 列上创建 B-Tree 索引，但可以 在从 JSONB 中提取出的特定值的表达式上创建。

适用场景：当你需要频繁地对 JSONB 中某个 固定路径 的值进行排序、范围查询或等值比较时。

创建 B-Tree 索引：

假设我们经常需要根据价格进行排序或筛选。

sql
-- 创建一个针对 price 字段的 B-Tree 索引
-- 注意：必须使用 ->> 提取为 text，并转换为正确的类型
CREATE INDEX idx_btree_products_price ON products USING BTREE (((details->>'price')::numeric));
重要提示：
1. 表达式必须用 双括号 ((...)) 包围。
2. 必须使用 ->> 操作符获取 text 值，然后使用 :: 将其转换为适当的类型（如 numeric, int, date 等）才能进行正确的排序和范围比较。

有了这个索引，以下查询将会变得非常高效：

“`sql
— 范围查询
SELECT name, details->>’price’ FROM products WHERE (details->>’price’)::numeric > 100 AND (details->>’price’)::numeric < 500;

— 排序
SELECT name, details->>’price’ FROM products ORDER BY (details->>’price’)::numeric DESC;
“`

4.3 索引选择策略总结

  • 通用查询（我不知道会查什么键）：使用默认的 GIN 索引。这是最灵活的选择。
  • 只做包含性检查 (@>)：使用带 jsonb_path_ops 的 GIN 索引。
  • 对特定字段进行排序、范围查询或精确匹配：在该字段的表达式上创建 B-Tree 索引。
  • 可以同时使用：你可以为一个 JSONB 列同时创建一个 GIN 索引和一个或多个 B-Tree 表达式索引，以应对不同的查询需求。

第五部分：最佳实践与设计模式

技术只是工具，如何巧妙地使用它才是艺术。

5.1 混合数据模型（Hybrid Model）

这是使用 JSONB 最重要的设计模式。不要把所有东西都塞进一个 JSONB 字段。应该将你的数据模型设计为关系型和文档型的混合体。

  • 核心、结构化的、经常用于 JOIN 或过滤的字段，应该作为表中的 原生列。例如 id, created_at, user_id, status 等。这些字段使用原生列性能最好，并且可以强制实施外键等约束。
  • 非核心的、模式不固定的、可选的或描述性的数据，则非常适合放入 JSONB 列。例如 settings, metadata, extra_attributes 等。

在我们的 products 示例中，id 和 name 是原生列，details 是 JSONB 列，这就是一个很好的混合模型。

5.2 保持 JSON 结构扁平化

尽量避免创建深度嵌套的 JSON 结构。深层嵌套的 JSON 更难查询和更新。

  • 不良设计：{"specs": {"performance": {"cpu": "i9"}}}
  • 优化设计：{"spec_cpu": "i9", "spec_ram": 32}

扁平化的结构使得使用 B-Tree 索引和 ->> 操作符更加简单直接。

5.3 利用 CHECK 约束强制模式

虽然 JSONB 的优点是灵活性，但有时我们仍然希望保证某些关键字段的存在。你可以使用 CHECK 约束来实现这一点。

sql
ALTER TABLE products
ADD CONSTRAINT check_details_required_fields
CHECK (details ? 'brand' AND details ? 'price');
这个约束会确保每次插入或更新 products 表时，details 字段必须包含 brand 和 price 这两个顶层键。

5.4 避免在 JSONB 中存储大型数组

如果你需要在 JSONB 数组中的对象上进行频繁查询，这通常是一个反模式。例如，一个产品有多个 reviews：

json
{
"reviews": [
{"user": "Alice", "rating": 5, "comment": "Great!"},
{"user": "Bob", "rating": 4, "comment": "Good value."}
]
}
要查找所有 Bob 的评论或所有评分为5星的评论，将非常低效。在这种情况下，更好的设计是创建一个独立的 reviews 表，并使用外键关联到 products 表。这正是关系型数据库的强项。

sql
CREATE TABLE reviews (
id SERIAL PRIMARY KEY,
product_id INT REFERENCES products(id),
user_name VARCHAR(100),
rating INT,
comment TEXT
);

结论

PostgreSQL JSONB 是一个极其强大的功能，它成功地将 NoSQL 的灵活性和文档存储能力无缝集成到世界领先的关系型数据库中。通过掌握其基础操作、高级查询、函数，特别是深刻理解 GIN 和 B-Tree 索引的原理与适用场景，你就可以构建出既健壮又灵活的高性能应用。

记住，JSONB 不是取代关系型设计的银弹，而是增强它的利器。通过采用混合数据模型，将结构化数据放在原生列中，将半结构化数据放在 JSONB 中，你可以真正地两全其美，从容应对未来多变的数据需求。现在，你已经具备了从入门到精通 JSONB 的所有知识，是时候在你的下一个项目中大展身手了。

发表评论

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

滚动至顶部