PostgreSQL实战指南：多领域数据库应用与实践

第三部分：PostgreSQL与NoSQL特性

PostgreSQL 凭借其对 JSON/JSONB、XML、Hstore 等数据类型的原生支持以及强大的全文搜索能力，完美融合了关系型数据库的严谨性与 NoSQL 的灵活性。本部分将详细介绍如何利用这些特性来高效处理半结构化和非结构化数据，满足现代应用的多样化需求。

第12章：JSON/JSONB数据类型与操作

在现代应用程序中，处理半结构化数据已成为常态。PostgreSQL 提供了强大的 JSON 和 JSONB 数据类型，并随着版本迭代，逐步增加了对 SQL/JSON 标准的支持，使其 JSON 处理能力达到了新的高度。本章将深入探讨 JSON/JSONB 的区别、传统操作符，并重点介绍新的 SQL/JSON 标准函数。

12.1 JSON vs JSONB：该如何选择？

PostgreSQL 提供了两种原生的 JSON 数据类型：JSON 和 JSONB。

特性JSONJSONB
存储方式存储原始的、精确的文本表示存储为分解后的二进制格式
写入性能非常快，因为它只做最少的文本验证较慢，因为它需要解析和转换输入文本
读取性能较慢，每次访问都需要重新解析整个文本非常快，因为数据已被解析，可以直接访问
处理保留所有空格、重复的键和键的顺序不保留空格，不保留键的顺序，并去重（保留最后一个）
索引支持有限全面支持，特别是 GIN 索引

选择原则：

  • 如果你的应用只是为了存储和检索完整的 JSON 文档，并且不关心内部结构查询，JSON 类型可能就足够了。
  • 在绝大多数情况下，JSONB 是更好的选择。因为它提供了更高的查询性能和更强大的索引支持，这对于需要频繁查询 JSON 内部字段的应用至关重要。
12.2 传统操作符与 GIN 索引

PostgreSQL 的传统 ->, ->>, @> 等操作符是查询 JSONB 的基础，它们与 GIN 索引结合能实现高效的查询。

  • ->: 获取 JSON 对象字段（返回 jsonb）。
  • ->>: 获取 JSON 对象字段作为文本（返回 text）。
  • @>: (包含) 左边的 JSONB 是否“包含”右边的 JSONB？这是 GIN 索引最高效的用法。

创建 GIN 索引:

1
2
3
4
5

-- 为 profile 列创建一个标准的 GIN 索引
CREATE INDEX idx_gin_user_profiles ON user_profiles USING GIN (profile);

-- 使用 @> 进行高效查询
SELECT * FROM user_profiles WHERE profile @> '{"tags": ["dev"]}';
12.3 SQL/JSON 标准函数 (PostgreSQL 16+)

从 PostgreSQL 16 开始，官方大力推进对 SQL/JSON 标准的支持，引入了一系列新的构造函数和查询函数，提供了更标准、更强大的 JSON 处理能力。

  • 构造函数:
    • JSON_OBJECT(...): 从键值对创建 JSON 对象。
    • JSON_ARRAY(...): 从元素列表创建 JSON 数组。
  • 查询函数:
    • JSON_EXISTS(json_val, path): 检查 JSON Path 表达式是否存在。
    • JSON_VALUE(json_val, path): 提取一个标量值。
    • JSON_QUERY(json_val, path): 提取一个对象或数组。

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 使用新的构造函数
SELECT JSON_OBJECT('id': 1, 'name': 'Alice');
SELECT JSON_ARRAY(1, 'hello', true, null);

-- 使用新的查询函数
SELECT name, profile
FROM user_profiles
WHERE JSON_EXISTS(profile, '$.contact.phones[*] ? (@ == "111-222")');

SELECT JSON_VALUE(profile, '$.name') AS name
FROM user_profiles;
12.4 JSON_TABLE：将 JSON 转换为关系表 (PostgreSQL 17+)

JSON_TABLE 是 SQL/JSON 标准中的一个里程碑功能，在 PostgreSQL 17 中被引入。它提供了一个极其强大的、标准化的方式来将复杂的 JSON 文档（特别是包含数组的文档）“展开”成一个关系型表格，是 jsonb_to_recordset 和 jsonb_array_elements 等传统函数的超集。

JSON_TABLE 语法:

1
2
3
4
5
6
7
8
9

JSON_TABLE(
    json_expression,
    path_to_array
    COLUMNS (
        column_name_1 type PATH path_from_element,
        column_name_2 type PATH path_from_element,
        NESTED PATH ... COLUMNS (...) -- 用于处理嵌套数组
    )
)
12.5 场景实战：处理和分析订单数据

业务场景描述:

一个订单系统以 JSON 格式存储每笔订单的详细信息，包括购买的商品列表。我们需要对这些半结构化的订单数据进行复杂的聚合分析。

数据与查询:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_data JSONB
);

INSERT INTO orders (order_data) VALUES
('{
    "customer_id": "cust-101",
    "order_date": "2025-07-12",
    "shipping_address": {"city": "New York", "zip": "10001"},
    "items": [
        {"product_id": "p-001", "name": "Laptop", "quantity": 1, "price": 1200.00},
        {"product_id": "p-002", "name": "Mouse", "quantity": 1, "price": 25.50}
    ]
}'),
('{
    "customer_id": "cust-102",
    "order_date": "2025-07-13",
    "shipping_address": {"city": "Chicago", "zip": "60601"},
    "items": [
        {"product_id": "p-003", "name": "Keyboard", "quantity": 2, "price": 75.00},
        {"product_id": "p-001", "name": "Laptop", "quantity": 1, "price": 1250.00}
    ]
}');

-- 使用 JSON_TABLE (PG 17+) 展开订单项并进行分析
SELECT
    o.order_id,
    t.customer_id,
    t.order_date,
    t.product_name,
    t.quantity,
    t.price,
    (t.quantity * t.price) AS line_total
FROM
    orders o,
    JSON_TABLE(o.order_data, '
 COLUMNS (
        customer_id TEXT PATH '$.customer_id',
        order_date DATE PATH '$.order_date',
        NESTED PATH '$.items[*]' COLUMNS (
            product_name TEXT PATH '$.name',
            quantity INT PATH '$.quantity',
            price NUMERIC(10, 2) PATH '$.price'
        )
    )) AS t;

查询结果:

1
2
3
4
5
6

 order_id | customer_id | order_date | product_name | quantity |  price  | line_total
----------+-------------+------------+--------------+----------+---------+------------
        1 | cust-101    | 2025-07-12 | Laptop       |        1 | 1200.00 |    1200.00
        1 | cust-101    | 2025-07-12 | Mouse        |        1 |   25.50 |      25.50
        2 | cust-102    | 2025-07-13 | Keyboard     |        2 |   75.00 |     150.00
        2 | cust-102    | 2025-07-13 | Laptop       |        1 | 1250.00 |    1250.00

代码解释与思考:

  • JSON_TABLE 的威力: JSON_TABLE 优雅地解决了 JSON 数据中最常见的“一对多”关系（一个订单有多个商品）。它通过 NESTED PATH 子句将 items 数组中的每个元素都展开成一个独立的行，同时还能从父级 JSON 中提取 customer_id 和 order_date 等信息，并将它们与展开的行关联起来。
  • 标准化与可读性: 相比于使用多个 jsonb_array_elements 并进行复杂的交叉连接，JSON_TABLE 的语法更加清晰、声明性更强，也更符合 SQL 标准，具有更好的可移植性。
  • 性能: JSON_TABLE 被设计为与查询优化器紧密集成，能够生成高效的执行计划。
12.6 总结

本章我们深入探讨了 PostgreSQL 强大的 JSON 处理能力。我们不仅回顾了 JSONB 的核心优势和传统操作符，更重点学习了 PostgreSQL 16 和 17 中引入的、符合 SQL/JSON 标准的新函数。特别是 JSON_TABLE 的出现，标志着 PostgreSQL 在处理复杂 JSON 文档、实现关系型与非关系型数据无缝融合方面迈上了一个新的台阶，为开发者提供了前所未有的灵活性和强大的分析能力。

在下一章，我们将探索 PostgreSQL 对另一种半结构化数据——XML 的支持。