第十一章文档型数据库风格操作

第一节使用 `jsonpath` 进行复杂文档查询

目标：学习和掌握 SQL/JSON Path 语言，这是一种功能强大的标准化查询语言，用于在 JSONB 文档中进行复杂的路径导航、过滤和数据提取。

虽然 ->, #> 和 @> 等操作符在很多场景下已经足够好用，但当我们需要进行更复杂的查询时，例如“查找数组中所有价格大于 100 的商品”或“获取所有拥有 email 属性的联系人”，这些操作符就显得力不从心了。

为了解决这个问题，PostgreSQL 引入了对 SQL/JSON Path 语言的支持。它提供了一种类似 XPath 的语法，让我们能够对 JSONB 文档的内部结构进行精细的、基于条件的查询。

`jsonpath` 核心概念

jsonpath 是一种路径表达式，它描述了如何在 JSON 文档中导航。

$：表示整个文档（上下文根）。
.key：访问对象的键。
[*]：访问数组的所有元素。
[index]：访问数组的特定索引。
?(<filter_expression>)：过滤器表达式，只返回满足条件的元素。

`jsonpath` 相关函数

PostgreSQL 提供了四个核心函数来执行 jsonpath 查询：

函数	返回类型	描述
`jsonb_path_query(target, path)`	`setof jsonb`	返回所有匹配路径的 `JSONB` 值集合。
`jsonb_path_query_first(target, path)`	`jsonb`	只返回第一个匹配的 `JSONB` 值。
`jsonb_path_exists(target, path)`	`boolean`	检查是否存在任何匹配路径的值。
`jsonb_path_match(target, path)`	`boolean`	检查路径表达式是否返回 `true`（用于谓词检查）。

`jsonpath` 实战演练

我们使用一个更复杂的 JSON 文档作为示例。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 准备一个包含客户信息的文档
CREATE TABLE customers (id INT, doc JSONB);
INSERT INTO customers VALUES (1, '{
    "name": "Alice",
    "tags": ["premium", "loyal"],
    "contacts": [
        {"type": "email", "value": "[email protected]"},
        {"type": "phone", "value": "123-456-7890"}
    ],
    "orders": [
        {"id": 101, "amount": 80, "items": ["A", "B"]},
        {"id": 102, "amount": 150, "items": ["C"]},
        {"id": 103, "amount": 200, "items": ["D", "E"]}
    ]
}');

查询 1：提取所有订单的金额

1
2
SELECT jsonb_path_query(doc, '$.orders[*].amount')
FROM customers WHERE id = 1;

$.orders: 访问顶层的 orders 键。
[*]：遍历 orders 数组中的所有元素。
.amount: 访问每个数组元素的 amount 键。结果：会返回一个集合，包含 80, 150, 200。

查询 2：查找金额大于 100 的订单

这是 jsonpath 最强大的功能——过滤。

1
2
SELECT jsonb_path_query(doc, '$.orders[*] ? (@.amount > 100)')
FROM customers WHERE id = 1;

?(): 过滤器表达式的开始。
@: 在过滤器内部，@ 代表当前正在被处理的元素（这里是 orders 数组中的每个订单对象）。
@.amount > 100: 过滤条件。结果：会返回 ID 为 102 和 103 的两个订单对象。

查询 3：检查客户是否拥有电话联系方式

使用 jsonb_path_exists，这对于在 WHERE 子句中进行过滤非常有用。

1
2
SELECT id, doc->'name' FROM customers
WHERE jsonb_path_exists(doc, '$.contacts[*] ? (@.type == "phone")');

@.type == "phone": jsonpath 使用 == 进行相等性比较。结果：会返回 Alice 的记录，因为她的 contacts 数组中存在一个 type 为 “phone” 的对象。

查询 4：获取第一个 email 地址

1
2
SELECT jsonb_path_query_first(doc, '$.contacts[*] ? (@.type == "email").value')
FROM customers WHERE id = 1;

在过滤器之后，我们可以继续用 .value 来提取该对象的 value 键。
jsonb_path_query_first 只返回它找到的第一个匹配项。结果："[email protected]"

性能考量

与 @> 操作符类似，jsonb_path_exists 和 jsonb_path_match 也可以利用 GIN 索引（需要使用 jsonb_path_ops 索引类）来加速查询。

1
2
3
-- 为 jsonpath 的存在性检查创建索引
CREATE INDEX idx_customers_doc_path_ops ON customers
USING GIN (doc jsonb_path_ops);

创建该索引后，上面示例中的查询 3 将会获得极大的性能提升。

📌 小结

SQL/JSON Path 语言为 PostgreSQL 的 JSONB 查询能力带来了质的飞跃。

它提供了一种标准化的、富有表现力的方式来查询 JSON 内部的复杂结构。
过滤器 ?() 是其最强大的特性，允许在 JSON 文档内部进行复杂的条件判断，而无需将数据提取到 SQL层面。
结合 jsonb_path_exists 和 GIN 索引，可以高效地对大量 JSONB 文档进行深度查询和过滤。

当你发现 ->> 或 @> 等基本操作符无法简洁地表达你的查询意图时，就应该立即想到使用 jsonpath。它是你在 PostgreSQL 中进行高级文档型数据库操作的必备利器。

文章目录