---

PostgreSQL JSON 入门指南：掌握灵活的数据结构

在现代应用开发中，JSON (JavaScript Object Notation) 已成为一种无处不在的数据交换格式。其简洁、易读、灵活的特性使得它在前后端通信、配置文件、存储半结构化数据等方面大放异彩。传统的关系型数据库以其结构化、强一致性的优势主导了数据存储领域，但在处理那些结构多变、字段不固定或需要嵌套表示的数据时，有时会显得不够灵活。

PostgreSQL 作为一款功能强大的开源关系型数据库，很早就认识到了 JSON 的重要性，并提供了业界领先的 JSON 数据类型和操作功能。这使得 PostgreSQL 能够有效地存储和查询 JSON 数据，为开发者提供了在结构化和半结构化数据之间自由切换的能力，极大地提升了应用的灵活性和开发效率。

本篇文章将带你深入了解 PostgreSQL 中的 JSON 支持，从基本概念到高级查询和索引技术，帮助你掌握在 PostgreSQL 中使用 JSON 的核心技能。

1. 为什么要在 PostgreSQL 中使用 JSON？

虽然关系型数据库的核心优势在于其规范化的、结构化的数据模型，但在某些场景下引入 JSON 具有显著的优势：

• 处理半结构化数据: 许多数据天生就是半结构化的，例如用户资料（不同用户可能有不同的属性）、产品变体（不同产品有不同的规格）、日志信息、第三方 API 返回的数据等。使用 JSON 可以避免为每个可能的属性创建一个单独的列，简化表结构。
• 敏捷开发: 当需求变化频繁，数据模型需要快速调整时，使用 JSON 字段可以减少数据库 schema 的修改，降低改动成本。只需修改应用程序解析和生成 JSON 的逻辑即可。
• 简化数据模型: 对于包含大量可选字段或具有层级关系的数据，使用 JSON 可以将相关信息聚合在一个字段中，使主表结构更清晰。
• 与现代技术栈契合: JSON 是 Web 开发、微服务、NoSQL 领域常用的数据格式，PostgreSQL 对 JSON 的原生支持使得它能更好地与这些技术集成。
• 高性能查询和索引: PostgreSQL 不仅仅是存储 JSON 文本，它提供了强大的查询操作符和索引支持，使得你可以高效地查询和过滤 JSON 数据中的内容，性能远超将 JSON 存储在文本字段中然后进行字符串匹配。

当然，使用 JSON 并非万能药。对于那些结构稳定、需要频繁进行复杂关系连接、强调数据一致性和完整性的核心业务数据，传统的规范化关系模型通常仍然是更好的选择。在 PostgreSQL 中，JSON 更多地被视为一种补充，允许你在同一数据库中同时管理结构化和半结构化数据，发挥各自的优势。

2. PostgreSQL 中的 JSON 数据类型：json vs jsonb

PostgreSQL 提供了两种 JSON 数据类型：json 和 jsonb。理解它们的区别是高效使用 PostgreSQL JSON 的关键。

2.1 json 数据类型

json 数据类型存储的是输入 JSON 文本的精确副本。数据库在存储时会验证输入的文本是否是合法的 JSON 格式，但不会进行额外的处理。

• 存储方式: 存储为普通的 text 类型，只是在插入时检查格式合法性。
• 特性:
  • 保留空格、键的顺序、重复的键（尽管标准 JSON 不允许重复键，但 json 类型会保留它们）。
  • 在查询时，每次访问都需要解析 JSON 文本，性能相对较低。
• 适用场景: 主要用于那些需要保留原始输入格式的场景，例如记录外部系统发送的精确 JSON 消息，或者当你需要验证输入的 JSON 是否符合特定的结构（例如，保留特定顺序的键）。

2.2 jsonb 数据类型

jsonb 数据类型存储的是 JSON 数据的二进制表示（b 代表 binary）。数据库在存储时会解析输入的 JSON 文本，并将其转换为一种更适合处理和查询的二进制格式。

• 存储方式: 存储为一种解析后的二进制格式。
• 特性:
  • 不保留空格、键的顺序、重复的键（如果存在重复键，保留最后一个）。
  • 不支持原始 JSON 中的注释（尽管 JSON 标准本身也不支持注释，但有些人可能在传输时包含）。
  • 存储时会对数据进行去重和排序（针对对象键）。
  • 在查询时，由于数据已经被解析并存储为二进制格式，访问速度非常快。
  • 支持更多的操作符和函数，特别是用于索引（如 GIN 索引）。
• 适用场景: 这是通常推荐使用的类型。 它适用于大多数需要查询、过滤、修改 JSON 数据的场景。其高效的查询性能和丰富的操作符使得它成为处理半结构化数据的首选。

2.3 如何选择 json 或 jsonb？

绝大多数情况下，你应该选择 jsonb。

使用 json 的唯一理由是：你关心 JSON 文本的 精确原始格式（例如空格、键的顺序、重复键）。除了这种非常特殊的用例，jsonb 在存储效率、查询性能和功能丰富性方面都远超 json。特别是当你需要对 JSON 数据进行索引以提高查询性能时，jsonb 是唯一支持 GIN 索引的类型。

在本指南的后续部分，我们将主要聚焦于 jsonb 类型，因为它更实用且功能更强大。

3. 存储 JSON 数据

在 PostgreSQL 中存储 JSON 数据非常简单，只需在表定义中使用 jsonb 或 json 数据类型即可。

创建包含 JSON 字段的表:

“`sql
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
attributes JSONB — 使用 jsonb 类型存储产品属性
);

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
profile JSONB — 使用 jsonb 类型存储用户资料
);
“`

插入 JSON 数据:

你可以使用标准的 INSERT 语句插入 JSON 数据。JSON 数据必须是合法的 JSON 格式字符串。

“`sql
— 插入一条产品数据
INSERT INTO products (name, attributes) VALUES
(‘Laptop’, ‘{“brand”: “Dell”, “screen_size”: 15.6, “storage”: {“type”: “SSD”, “size_gb”: 512}, “colors”: [“black”, “silver”]}’);

— 插入一条用户数据
INSERT INTO users (username, profile) VALUES
(‘alice’, ‘{“age”: 30, “city”: “New York”, “is_premium”: true, “tags”: [“tech”, “travel”], “contact”: {“email”: “[email protected]”}}’);

— 插入另一条用户数据，包含不同字段
INSERT INTO users (username, profile) VALUES
(‘bob’, ‘{“age”: 25, “city”: “London”, “occupation”: “Engineer”}’);

— 插入包含 null 值的 JSON
INSERT INTO users (username, profile) VALUES
(‘charlie’, ‘{“age”: null, “city”: “Paris”, “is_premium”: false}’);

— 插入 JSON 数组作为顶级元素 (较少见，但合法)
INSERT INTO products (name, attributes) VALUES
(‘Colors Sample’, ‘[“red”, “green”, “blue”]’); — 尽管此表设计上可能更适合对象，但演示了插入数组
“`

PostgreSQL 会自动将输入的字符串解析并存储为 jsonb 的二进制格式。如果插入的字符串不是合法的 JSON，将会报错。

4. 查询 JSON 数据

查询是使用 JSON 数据类型的核心。PostgreSQL 提供了一系列强大的操作符和函数来访问、过滤和提取 JSON 数据中的内容。

4.1 访问 JSON 元素的操作符

PostgreSQL 提供了两种主要的 JSON 访问操作符：-> 和 ->>。理解它们的区别非常重要。

• -> 操作符:

  • 用于访问 JSON 对象中的键或 JSON 数组中的元素（通过索引）。
  • 返回类型是 jsonb（或 json），这意味着你可以继续使用 JSON 操作符链式访问更深层的数据。
  • 语法: jsonb_column -> key (访问对象) 或 jsonb_column -> index (访问数组)

• ->> 操作符:

  • 与 -> 类似，用于访问 JSON 对象中的键或 JSON 数组中的元素。
  • 返回类型是 text，这意味着提取的数据会作为文本字符串返回。这常用于最终提取值进行比较、显示或与其他文本字段连接。
  • 语法: jsonb_column ->> key (访问对象) 或 jsonb_column ->> index (访问数组)

示例：使用 -> 和 ->> 访问数据

“`sql
— 访问用户 “alice” 的 profile
SELECT profile FROM users WHERE username = ‘alice’;
— profile: {“age”: 30, “city”: “New York”, “is_premium”: true, “tags”: [“tech”, “travel”], “contact”: {“email”: “[email protected]”}}

— 使用 -> 访问 city 键 (返回 jsonb 类型)
SELECT profile -> ‘city’ FROM users WHERE username = ‘alice’;
— profile -> ‘city’: “New York” (注意带引号，因为它还是一个 jsonb 值)

— 使用 ->> 访问 city 键 (返回 text 类型)
SELECT profile ->> ‘city’ FROM users WHERE username = ‘alice’;
— profile ->> ‘city’: New York (不带引号，是纯文本)

— 访问 profile -> ‘contact’ (返回 jsonb 类型)
SELECT profile -> ‘contact’ FROM users WHERE username = ‘alice’;
— profile -> ‘contact’: {“email”: “[email protected]”}

— 访问 profile -> ‘contact’ ->> ’email’ (链式访问，最终返回 text 类型)
SELECT profile -> ‘contact’ ->> ’email’ FROM users WHERE username = ‘alice’;
— profile -> ‘contact’ ->> ’email’: [email protected]

— 访问 tags 数组的第一个元素 (索引从 0 开始)
SELECT profile -> ‘tags’ -> 0 FROM users WHERE username = ‘alice’;
— profile -> ‘tags’ -> 0: “tech” (jsonb 类型)

— 访问 tags 数组的第一个元素 (返回 text 类型)
SELECT profile -> ‘tags’ ->> 0 FROM users WHERE username = ‘alice’;
— profile -> ‘tags’ ->> 0: tech (text 类型)

— 访问 products 表中 laptop 的 storage size_gb (多层嵌套)
SELECT attributes -> ‘storage’ ->> ‘size_gb’ FROM products WHERE name = ‘Laptop’;
— attributes -> ‘storage’ ->> ‘size_gb’: 512 (text 类型)

— 注意：访问不存在的键或索引会返回 SQL NULL
SELECT profile -> ‘non_existent_key’ FROM users WHERE username = ‘alice’;
— ?column?: NULL
SELECT profile -> ‘tags’ -> 99 FROM users WHERE username = ‘alice’;
— ?column?: NULL
“`

4.2 访问嵌套元素的快捷操作符 #> 和 #>>

当需要访问多层嵌套的数据时，使用 -> 或 ->> 进行链式操作可能会变得冗长。PostgreSQL 提供了 #> 和 #>> 操作符，允许你使用一个文本数组（表示路径）来指定访问路径。

• #> 操作符:

  • 通过路径访问 JSON 数据。
  • 返回类型是 jsonb（或 json）。
  • 语法: jsonb_column #> '{path, to, key, index}'

• #>> 操作符:

  • 通过路径访问 JSON 数据。
  • 返回类型是 text。
  • 语法: jsonb_column #>> '{path, to, key, index}'

路径数组中的元素可以是字符串（对应 JSON 对象的键）或表示数组索引的数字字符串。

示例：使用 #> 和 #>> 访问数据

“`sql
— 访问 “alice” 的 contact email，使用 #>>
SELECT profile #>> ‘{contact, email}’ FROM users WHERE username = ‘alice’;
— profile #>> ‘{contact, email}’: [email protected] (text 类型)

— 访问 “alice” 的 tags 数组的第二个元素 (索引 1)，使用 #>>
SELECT profile #>> ‘{tags, 1}’ FROM users WHERE username = ‘alice’;
— profile #>> ‘{tags, 1}’: travel (text 类型)

— 访问 laptop 的 storage，使用 #> (返回 jsonb)
SELECT attributes #> ‘{storage}’ FROM products WHERE name = ‘Laptop’;
— attributes #> ‘{storage}’: {“type”: “SSD”, “size_gb”: 512} (jsonb 类型)

— 访问 laptop 的 storage size_gb，使用 #>>
SELECT attributes #>> ‘{storage, size_gb}’ FROM products WHERE name = ‘Laptop’;
— attributes #>> ‘{storage, size_gb}’: 512 (text 类型)
“`

#>> 操作符特别方便，因为它允许你用一个操作就从深层嵌套的结构中提取文本值。

4.3 过滤 (WHERE 子句)

你可以在 WHERE 子句中使用 JSON 操作符来过滤数据。通常需要将提取的 JSON 值转换为合适的 SQL 数据类型进行比较。

“`sql
— 查找城市是 ‘New York’ 的用户
SELECT username FROM users WHERE profile ->> ‘city’ = ‘New York’;
— username: alice

— 查找年龄大于 28 的用户 (需要将提取的 text 转换为 numeric 或 integer)
SELECT username FROM users WHERE (profile ->> ‘age’)::numeric > 28;
— username: alice

— 查找是高级用户 (is_premium 为 true) 的用户
SELECT username FROM users WHERE (profile ->> ‘is_premium’)::boolean IS TRUE; — 或者 = ‘true’
— username: alice

— 查找标签包含 ‘tech’ 的用户 (使用 @> 操作符，见下文)
SELECT username FROM users WHERE profile @> ‘{“tags”: [“tech”]}’;
— username: alice

— 查找存储大小大于 500 GB 的产品
SELECT name FROM products WHERE (attributes #>> ‘{storage, size_gb}’)::integer > 500;
— name: Laptop
“`

注意：在 WHERE 子句中，将提取的值转换为适当的 SQL 数据类型（如 ::numeric, ::integer, ::boolean）非常重要，以便进行数值、布尔等类型的比较，而不是简单的文本比较。

4.4 检查键/路径/包含性

PostgreSQL 为 jsonb 类型提供了一些非常有用的操作符来检查键的存在、路径是否存在或一个 JSON 文档是否包含另一个。

• ? 操作符:

  • 检查顶级 JSON 对象是否包含指定的键。
  • 语法: jsonb_column ? key (返回 boolean)

• ?| 操作符:

  • 检查顶级 JSON 对象是否包含指定数组中的 任何 键。
  • 语法: jsonb_column ?| array['key1', 'key2', ...] (返回 boolean)

• ?& 操作符:

  • 检查顶级 JSON 对象是否包含指定数组中的 所有 键。
  • 语法: jsonb_column ?& array['key1', 'key2', ...] (返回 boolean)

• @> 操作符 (Containment):

  • 检查左边的 jsonb 文档是否包含右边的 jsonb 文档。右边的文档必须是左边文档的一个子集（结构和值都匹配）。
  • 语法: jsonb_column @> jsonb_value (返回 boolean)

• <@ 操作符 (Contained by):

  • 检查左边的 jsonb 文档是否被右边的 jsonb 文档包含。与 @> 相反。
  • 语法: jsonb_column <@ jsonb_value (返回 boolean)

示例：使用检查操作符

“`sql
— 查找 profile 包含 ‘occupation’ 键的用户
SELECT username FROM users WHERE profile ? ‘occupation’;
— username: bob

— 查找 profile 包含 ‘age’ 或 ’email’ 键的用户
SELECT username FROM users WHERE profile ?| array[‘age’, ’email’];
— username: alice, bob, charlie

— 查找 profile 包含 ‘age’ 和 ‘city’ 键的用户
SELECT username FROM users WHERE profile ?& array[‘age’, ‘city’];
— username: alice, bob, charlie

— 查找 profile 包含 {‘city’: ‘New York’} 的用户 (使用 @>)
SELECT username FROM users WHERE profile @> ‘{“city”: “New York”}’;
— username: alice

— 查找 profile 包含 {‘tags’: [‘tech’]} 的用户
SELECT username FROM users WHERE profile @> ‘{“tags”: [“tech”]}’; — 注意，@> 检查的是包含整个子结构，不仅仅是单个元素
— username: alice

— 查找 profile 被 {‘age’: 30, ‘city’: ‘New York’, ‘occupation’: ‘Engineer’} 包含的用户
— Alice 的 profile 是 {‘age’: 30, ‘city’: ‘New York’, ‘is_premium’: true, ‘tags’: [‘tech’, ‘travel’], ‘contact’: {…}}
— Bob 的 profile 是 {‘age’: 25, ‘city’: ‘London’, ‘occupation’: ‘Engineer’}
SELECT username FROM users WHERE profile <@ ‘{“age”: 30, “city”: “New York”, “occupation”: “Engineer”}’;
— 没有结果，因为 Alice 的 profile 包含更多键，不被右边这个较小的结构包含。
— 如果右边是 {‘age’: 30, ‘city’: ‘New York’}，那么 Alice 会被包含：
SELECT username FROM users WHERE profile <@ ‘{“age”: 30, “city”: “New York”}’; — 注意这个查询不会返回 alice
— 重要提示： @> 和 <@ 检查的是结构和值的完整匹配。例如，{'a':1, 'b':2} @> {'a':1} 为 true，但 {'a':[1,2], 'b':3} @> {'a':[1]} 为 false，因为左边的数组包含了更多元素。要检查数组元素的包含，通常需要结合其他操作符或函数。

— 检查 tags 数组是否包含 ‘tech’ (使用 ? 或 @>)
SELECT username FROM users WHERE profile -> ‘tags’ ? ‘tech’; — 检查数组是否包含 ‘tech’ 字符串
— username: alice
SELECT username FROM users WHERE profile -> ‘tags’ @> ‘[“tech”]’; — 检查数组是否包含子数组 [“tech”]
— username: alice
“`

4.5 提取键、值或路径集合

有时候你需要从 JSON 对象中提取所有键、所有值或将其分解为多行。

• jsonb_object_keys(jsonb):

  • 提取 JSON 对象的所有顶级键。
  • 返回类型为 SETOF text (一组文本行)。
  • 语法: SELECT jsonb_object_keys(jsonb_column) FROM table ...

• jsonb_each(jsonb):

  • 将顶级 JSON 对象的键值对分解为多行，每行包含 key (text) 和 value (jsonb) 两列。
  • 返回类型为 SETOF record。
  • 语法: SELECT * FROM jsonb_each(jsonb_column) AS obj(key text, value jsonb) ... 或直接在 FROM 子句中使用 jsonb_each。

• jsonb_each_text(jsonb):

  • 与 jsonb_each 类似，但 value 列返回 text 类型。
  • 返回类型为 SETOF record (key text, value text)。
  • 语法: SELECT * FROM jsonb_each_text(jsonb_column) AS obj(key text, value text) ... 或直接在 FROM 子句中使用 jsonb_each_text。

示例：使用提取函数

“`sql
— 列出所有用户的 profile 中的顶级键
SELECT DISTINCT jsonb_object_keys(profile) FROM users;
— jsonb_object_keys: age, city, contact, is_premium, occupation, tags

— 分解 “alice” 的 profile 为键值对
SELECT key, value FROM jsonb_each(profile) AS obj WHERE obj.value IS NOT NULL AND username = ‘alice’;
— key | value
— ———–|—————————————–
— age | 30
— city | “New York”
— is_premium | true
— tags | [“tech”, “travel”]
— contact | {“email”: “[email protected]”}

— 分解 “alice” 的 profile 为文本键值对
SELECT key, value FROM jsonb_each_text(profile) AS obj WHERE username = ‘alice’;
— key | value
— ———–|————
— age | 30
— city | New York
— is_premium | true
— tags | [“tech”, “travel”] — 注意，这里 value 仍然是 JSON 文本表示
— contact | {“email”: “[email protected]”}

— 查找 profile 中包含值为 ‘Engineer’ 的键的用户
SELECT username FROM users, jsonb_each_text(profile) AS obj WHERE obj.value = ‘Engineer’;
— username: bob
“`

在 FROM 子句中使用 jsonb_each 或 jsonb_each_text 是一种强大的技术，可以将 JSON 数据 “反规范化” 到行和列中，方便进行更复杂的聚合或连接操作。

4.6 其他常用 JSON 函数

PostgreSQL 还提供了许多其他有用的 JSON 函数：

• jsonb_typeof(jsonb): 返回 JSONB 值的类型（object, array, string, number, boolean, null）。
• jsonb_array_length(jsonb): 返回顶级 JSON 数组的长度。
• jsonb_pretty(jsonb): 返回格式化（带缩进）的 JSON 文本。
• jsonb_strip_nulls(jsonb): 返回移除所有 null 值的 JSONB。
• 数据生成函数: to_jsonb(), jsonb_build_object(), jsonb_build_array(), jsonb_object() 等可以将 SQL 数据转换为 JSON。

示例：使用其他函数

“`sql
— 检查 “alice” profile 的类型
SELECT jsonb_typeof(profile) FROM users WHERE username = ‘alice’;
— jsonb_typeof: object

— 检查 “alice” profile 中 tags 数组的长度
SELECT jsonb_array_length(profile -> ‘tags’) FROM users WHERE username = ‘alice’;
— jsonb_array_length: 2

— 格式化显示 “alice” 的 profile
SELECT jsonb_pretty(profile) FROM users WHERE username = ‘alice’;
/
{
“age”: 30,
“city”: “New York”,
“tags”: [
“tech”,
“travel”
],
“contact”: {
“email”: “[email protected]”
},
“is_premium”: true
}
/

— 显示移除 null 值的 “charlie” 的 profile
SELECT jsonb_strip_nulls(profile) FROM users WHERE username = ‘charlie’;
— {“city”: “Paris”, “is_premium”: false}
“`

5. 索引 JSON 数据

为了提高对 JSON 数据进行过滤和查询的性能，特别是对于大型数据集，创建索引至关重要。PostgreSQL 为 jsonb 类型提供了强大的索引支持，主要是 GIN (Generalized Inverted Index) 索引。

5.1 GIN 索引

GIN 索引非常适合用于那些需要快速查找在大型文档集合中包含特定键、键值对或路径的文档的场景。

• jsonb_ops 运算符类:

  • 这是 GIN 索引的默认运算符类。
  • 它索引了 JSON 文档中的所有键、值、数组元素和路径。
  • 支持的操作符包括：?, ?|, ?&, @>, <@ 以及路径操作符 #> 和 #>> 的部分使用场景（当用于查找特定路径下的值时）。
  • 索引较大，构建较慢。

• jsonb_path_ops 运算符类:

  • 这是 GIN 索引的一种更紧凑的变体。
  • 它只索引了 JSON 文档中值的存在和路径信息，不索引具体的值。
  • 主要优化了 @> 操作符（查找包含特定子结构的文档）和路径操作符（#> 和 #>>）的查找。
  • 不支持 ?, ?|, ?& 等操作符的索引加速。
  • 索引较小，构建较快，查找速度可能更快（对于它优化的操作符）。

如何选择 jsonb_ops 或 jsonb_path_ops?

• 如果你主要使用 @> 操作符进行子结构包含查询，或者主要通过特定路径 (#> 或 #>>) 查找值，并且不频繁使用 ?, ?|, ?& 检查顶级键是否存在，那么 jsonb_path_ops 通常是更好的选择，因为它更小更高效。
• 如果你需要加速所有这些操作符 (?, ?|, ?&, @>, <@, #>, #>> 的查找值)，或者不确定哪些操作符最常用，那么使用默认的 jsonb_ops。

创建 GIN 索引示例:

“`sql
— 创建一个使用默认 jsonb_ops 的 GIN 索引
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);

— 创建一个使用 jsonb_path_ops 的 GIN 索引
CREATE INDEX idx_products_attributes_gin_path ON products USING GIN (attributes jsonb_path_ops);
“`

创建索引后，PostgreSQL 查询规划器在执行涉及这些操作符的查询时，会自动考虑使用这些 GIN 索引来提高性能。

5.2 B-tree 索引

如果你需要对 JSON 中提取的 特定 值进行频繁的精确匹配或范围查询（例如，按年龄、价格、状态等），并且这些值是可比较的简单类型（数字、字符串、布尔），那么可以考虑创建基于表达式的 B-tree 索引。

创建基于表达式的 B-tree 索引示例:

“`sql
— 对 profile 中的 age 字段创建 B-tree 索引 (需要将 age 转换为数字)
CREATE INDEX idx_users_profile_age ON users ((profile ->> ‘age’)::numeric);

— 对 profile 中的 city 字段创建 B-tree 索引 (按城市过滤)
CREATE INDEX idx_users_profile_city ON users ((profile ->> ‘city’));

— 对产品 attributes 中的 storage size_gb 字段创建 B-tree 索引 (需要转换为数字)
CREATE INDEX idx_products_attributes_storage_size ON products (((attributes -> ‘storage’ ->> ‘size_gb’)::integer));
“`

注意，基于表达式的索引会存储表达式的结果，并在查询时匹配 完全相同的表达式 才能使用索引。例如，WHERE (profile ->> 'age')::numeric > 28 可以使用 idx_users_profile_age 索引。

总结索引策略:

• 对于广泛的 JSON 数据内部结构查找（键、路径、包含性），使用 jsonb 上的 GIN 索引 (jsonb_ops 或 jsonb_path_ops)。
• 对于对 JSON 中 特定、固定 路径下的简单值（如数字、字符串、布尔）进行精确匹配或范围查询，使用基于表达式的 B-tree 索引。

6. 修改 JSON 数据

PostgreSQL 提供了多种函数来修改 jsonb 数据。需要注意的是，jsonb 值是不可变的，所有的修改操作实际上是返回一个新的 jsonb 值。因此，修改 JSON 数据通常涉及 UPDATE 语句和相应的 JSON 函数。

6.1 使用 || 连接操作符 (jsonb only)

对于 jsonb 类型，|| 操作符可以用于连接 JSON 对象和 JSON 数组。

• 连接对象：如果键冲突，右边的值会覆盖左边的值。
• 连接数组：将右边的数组元素附加到左边数组的末尾。

示例：使用 || 修改 JSONB

“`sql
— 给 “alice” 的 profile 添加一个 ‘occupation’ 键 (如果已存在则更新)
UPDATE users SET profile = profile || ‘{“occupation”: “Developer”}’ WHERE username = ‘alice’;
— SELECT profile FROM users WHERE username = ‘alice’;
— {“age”: 30, “city”: “New York”, “tags”: [“tech”, “travel”], “contact”: {“email”: “[email protected]”}, “occupation”: “Developer”, “is_premium”: true} — 注意键的顺序可能改变

— 给 “alice” 的 tags 数组添加一个元素 “coding”
UPDATE users SET profile = jsonb_set(profile, ‘{tags}’, (profile -> ‘tags’) || ‘[“coding”]’) WHERE username = ‘alice’;
— SELECT profile -> ‘tags’ FROM users WHERE username = ‘alice’;
— [“tech”, “travel”, “coding”]
“`

使用 || 操作符连接数组需要先提取数组，再进行连接，然后使用 jsonb_set 函数放回去，这稍微有点复杂。

6.2 使用 jsonb_set()

jsonb_set(target, path, new_value, [create_if_missing]) 是一个更通用的修改函数，允许你根据路径设置、添加或删除值。

• target: 要修改的 jsonb 值。
• path: 一个 text 数组，指定要设置或添加值的位置。
• new_value: 要设置的新值 (jsonb 类型)。
• create_if_missing (可选，默认为 true): 如果路径不存在，是否创建新的结构。如果为 false 且路径不存在，则不进行任何修改。

示例：使用 jsonb_set() 修改 JSONB

“`sql
— 更新 “alice” 的 age 为 31
UPDATE users SET profile = jsonb_set(profile, ‘{age}’, ’31’) WHERE username = ‘alice’;
— SELECT profile ->> ‘age’ FROM users WHERE username = ‘alice’;
— 31

— 添加一个不存在的键 ‘status’
UPDATE users SET profile = jsonb_set(profile, ‘{status}’, ‘”active”‘) WHERE username = ‘alice’;
— SELECT profile ->> ‘status’ FROM users WHERE username = ‘alice’;
— active

— 修改 nested 的 email
UPDATE users SET profile = jsonb_set(profile, ‘{contact, email}’, ‘”[email protected]”‘) WHERE username = ‘alice’;
— SELECT profile #>> ‘{contact, email}’ FROM users WHERE username = ‘alice’;
— [email protected]

— 向 tags 数组的末尾添加一个元素 (索引为数组长度)
UPDATE users SET profile = jsonb_set(profile, ‘{tags, ‘ || jsonb_array_length(profile -> ‘tags’) || ‘}’, ‘”database”‘) WHERE username = ‘alice’;
— SELECT profile -> ‘tags’ FROM users WHERE username = ‘alice’;
— [“tech”, “travel”, “coding”, “database”]

— 删除 contact.email (通过将值设置为 ‘null’ 并指定 create_if_missing = false 实现，或者使用 jsonb_delete)
— 注意：直接删除键通常使用 jsonb_delete 操作符
UPDATE users SET profile = jsonb_set(profile, ‘{contact, email}’, ‘null’, false) WHERE username = ‘alice’; — 设置为 null 并且不创建，如果存在则设置为 null，但不是删除键
— 使用 jsonb_delete 更直接
“`

6.3 使用 jsonb_delete 操作符 - 和 #-

jsonb 提供 操作符 来删除键或路径。

• - key 操作符:

  • 从顶级 JSON 对象中删除指定的键。
  • 从顶级 JSON 数组中删除指定索引的元素。
  • 语法: jsonb_column - key (删除对象键) 或 jsonb_column - index (删除数组元素)

• #- path 操作符:

  • 根据指定的路径删除 JSON 数据。
  • 语法: jsonb_column #- path

示例：使用 - 和 #- 删除 JSONB

“`sql
— 从 “alice” 的 profile 中删除 ‘occupation’ 键
UPDATE users SET profile = profile – ‘occupation’ WHERE username = ‘alice’;
— SELECT profile ? ‘occupation’ FROM users WHERE username = ‘alice’; — False

— 从 “alice” 的 profile 中删除 ‘tags’ 数组的第二个元素 (索引 1, 即 ‘travel’)
UPDATE users SET profile = profile – ‘{tags}’ – 1 WHERE username = ‘alice’; — 先获取tags数组，再删除数组元素
— SELECT profile -> ‘tags’ FROM users WHERE username = ‘alice’;
— [“tech”, “coding”, “database”]

— 从 “alice” 的 profile 中删除 nested 的 contact.email
UPDATE users SET profile = profile #- ‘{contact, email}’ WHERE username = ‘alice’;
— SELECT profile #>> ‘{contact, email}’ FROM users WHERE username = ‘alice’; — NULL
— SELECT profile -> ‘contact’ FROM users WHERE username = ‘alice’; — {} (如果contact下没有其他键)
“`

6.4 使用 jsonb_insert()

jsonb_insert(target, path, new_value, [insert_after]) 函数用于在指定的路径插入新的值。对于对象，如果键已存在，会抛出错误，除非路径指向一个数组。对于数组，可以在指定索引之前或之后插入。

• target: 要修改的 jsonb 值。
• path: 一个 text 数组，指定插入的位置。
• new_value: 要插入的新值 (jsonb 类型)。
• insert_after (可选，默认为 false): 如果路径指向数组元素，是在此元素之后插入 (true) 还是之前插入 (false)。

示例：使用 jsonb_insert() 修改 JSONB

“`sql
— 向 “alice” 的 tags 数组的索引 1 处（在 ‘coding’ 前）插入 ‘open source’
UPDATE users SET profile = jsonb_insert(profile, ‘{tags, 1}’, ‘”open source”‘) WHERE username = ‘alice’;
— SELECT profile -> ‘tags’ FROM users WHERE username = ‘alice’;
— [“tech”, “open source”, “coding”, “database”]

— 向 “alice” 的 contact 对象中插入一个 fax 键 (如果 fax 已存在会报错)
— UPDATE users SET profile = jsonb_insert(profile, ‘{contact, fax}’, ‘”123-456-7890″‘) WHERE username = ‘alice’;
— SELECT profile -> ‘contact’ FROM users WHERE username = ‘alice’;
— {“email”: null, “fax”: “123-456-7890”} — 假设 email 之前被设置为 null

— 使用 insert_after=true 在索引 2 处（在 ‘coding’ 后）插入 ‘sql’
UPDATE users SET profile = jsonb_insert(profile, ‘{tags, 2}’, ‘”sql”‘, true) WHERE username = ‘alice’;
— SELECT profile -> ‘tags’ FROM users WHERE username = ‘alice’;
— [“tech”, “open source”, “coding”, “sql”, “database”]
“`

jsonb_set 倾向于更新或替换，而 jsonb_insert 更专注于在特定位置（尤其是数组中）插入新元素，并且对对象键的存在性检查更严格。

7. 常见使用场景

• 用户配置/偏好: 存储用户界面设置、订阅选项、通知偏好等不固定或不属于核心身份信息的数据。
• 产品目录: 对于具有大量可选或变体属性的产品（如服装的颜色/尺寸，电子产品的配置），将这些属性存储在 JSONB 字段中，而不是创建几十个甚至上百个稀疏的列。
• 日志和事件数据: 存储结构多变的日志条目或系统事件的详细信息。
• 第三方 API 数据: 存储直接从外部服务获取的、结构复杂的 JSON 响应，方便后续查询和分析。
• 内容管理: 存储文章、博客或页面的元数据，这些元数据可能因内容类型而异。

8. 优点与缺点总结

优点:

• 灵活性: 轻松存储和管理结构多变或半结构化数据。
• 开发速度: 减少数据库 schema 变更的频率，加快迭代。
• 数据整合: 允许在同一个数据库表中同时存储结构化和半结构化数据。
• 强大的查询和索引: PostgreSQL 提供了丰富的功能来高效地处理 JSON 数据。
• 与现代生态系统兼容: JSON 是主流的数据格式。

缺点:

• 失去关系模型的优势: JSON 字段内部的数据不是第一公民，不能直接利用 SQL 的 JOIN、外键约束、列级数据类型检查等关系特性（除非通过复杂的提取和转换）。
• 查询复杂性增加: 访问 JSON 内部数据需要使用特定的操作符和函数，不如直接访问列直观。
• 性能考虑: 虽然有索引，但涉及 JSON 内部数据的查询可能不如对规范化列的查询快，特别是对于非常复杂的查询或涉及 JSON 内部数据的连接。
• 数据一致性难以保证: 数据库无法强制 JSON 字段内部的数据结构或数据类型，这需要由应用程序层面来保证。
• 存储开销: 对于包含大量重复键的小 JSON 对象，存储 JSONB 可能比规范化存储占用更多空间。

9. 最佳实践和提示

• 优先使用 jsonb: 除非你有强烈的理由需要保留原始文本格式，否则总是选择 jsonb。
• 合理设计 JSON 结构: 避免将整个应用的状态倾倒入一个巨大的 JSON 字段。将结构稳定、需要频繁关联或强制约束的数据保留在结构化列中。JSON 字段适合存储那些可选的、变动的、层级化的、或不属于核心业务流程的数据。
• 为常用查询创建索引: 分析你的查询模式，为那些频繁用于过滤或查找的 JSON 字段创建合适的 GIN 或 B-tree 索引。
• 理解操作符的返回类型: 记住 -> 和 #> 返回 jsonb (或 json)，而 ->> 和 #>> 返回 text。根据你的需求选择合适的操作符，并在需要时进行类型转换。
• 避免在 JSON 字段中存储关系数据: 不要试图在 JSON 字段中存储指向其他表 ID 的引用，这会破坏关系模型的完整性，并使得数据关联变得异常困难和低效。
• 监控性能: 使用 EXPLAIN 命令检查查询计划，确保索引被有效利用，并识别潜在的性能瓶颈。

10. 总结

PostgreSQL 对 JSON 的支持是其强大功能的一个重要体现。通过 jsonb 数据类型和丰富的操作符、函数及索引机制，PostgreSQL 能够有效地处理半结构化数据，为应用开发提供了极大的灵活性。

掌握如何在 PostgreSQL 中存储、查询、索引和修改 JSON 数据，将使你能够更好地应对现代应用开发中遇到的各种数据存储挑战。在需要灵活性时利用 JSON 的优势，同时保留关系型数据库在处理结构化数据方面的强大能力，是构建健壮、高效应用程序的关键。

希望本入门指南能为你打开 PostgreSQL JSON 的大门，鼓励你进一步探索其更高级的功能和用法！

---