PostgreSQL / 系统课时
使用 SQL JSON 路径
使用 SQL JSON 路径
当 JSONB 同时包含嵌套对象、数组和条件筛选时,连续拼接传统运算符会很快变得难读。本节用一组订单数据掌握 SQL/JSON 路径的导航、过滤与取值,并重点厘清 @? 与 @@ 的不同判断语义。
学习目标
- 使用
jsonpath表达嵌套对象、数组遍历和过滤条件 - 正确区分
@?、@@与路径查询函数的语义边界 - 判断 SQL/JSON 路径和传统 JSONB 运算符各自更合适的场景
读懂路径表达式
SQL/JSON 路径以 $ 表示文档根节点,点号访问对象成员,[*] 遍历数组元素。比如 $.customer.name 指向客户名称,$.items[*].price 会访问 items 数组中每个对象的 price。
过滤器写成 ? (...),其中 @ 表示当前正在检查的路径项。$.items[*] ? (@.price > 100) 的含义是:遍历所有商品,只保留 price 大于 100 的元素。
用同一组数据比较 @? 与 @@
下面的主示例同时验证“是否存在匹配项”“谓词检查”和“返回匹配值”三种需求:
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id bigint PRIMARY KEY,
payload jsonb NOT NULL
);
INSERT INTO orders (id, payload) VALUES
(1, '{"customer":{"name":"Alice"},"items":[{"sku":"A1","price":80},{"sku":"B2","price":150}]}'),
(2, '{"customer":{"name":"Bob"},"items":[{"sku":"C3","price":60}]}');
SELECT
id,
payload @? '$.items[*] ? (@.price > 100)' AS has_expensive_item,
payload @@ '$.customer.name == "Alice"' AS customer_is_alice,
(payload @@ '$.items[*].price') IS NULL AS non_boolean_is_null
FROM orders
ORDER BY id;
SELECT
id,
jsonb_path_query(
payload,
'$.items[*] ? (@.price >= 80).sku'
) AS sku
FROM orders
ORDER BY id, sku;预期结果:
id | has_expensive_item | customer_is_alice | non_boolean_is_null
----+--------------------+-------------------+---------------------
1 | t | t | t
2 | f | f | t
(2 rows)
id | sku
----+------
1 | "A1"
1 | "B2"
(2 rows)@? 是存在性检查:只要指定 JSON 路径能产生至少一个结果项,就返回 true。因此它适合回答“有没有满足条件的数组元素”。
@@ 则执行 JSON path predicate check。它不是另一种存在性运算符,而是取路径求值结果中的第一个 item作为谓词检查结果;如果这个 item 不是布尔值,SQL 结果就是 NULL。示例中的 $.customer.name == "Alice" 会产生布尔判断结果,所以适合 @@;而 $.items[*].price 产生的是数值项,因此 @@ 得到 NULL。
取回路径命中的值
过滤整行和返回路径值是两类需求。jsonb_path_query 返回路径命中的所有 JSON 值,因此同一行中存在多个匹配元素时会产生多行结果。只想判断是否存在结果,可以使用 jsonb_path_exists;只想取得第一个匹配值,可以使用 jsonb_path_query_first。
这些函数适合表达“沿 JSON 结构查找并返回值”的意图。若先展开数组再拼多层子查询才能完成同一件事,路径函数通常更紧凑,也更容易把导航和过滤条件放在一起阅读。
理解 lax 与 strict
JSON 路径默认使用 lax 模式,会对部分结构差异做自动适配,适合结构存在合理变化的数据。strict 模式要求路径与 JSON 结构严格对应,更适合把结构偏差视为数据契约问题的场景。
两种模式不是“宽松查询”和“安全查询”的简单二选一。选择标准应是数据契约:允许兼容一定结构变化时保留默认 lax;需要尽早暴露结构不符合预期的问题时,再显式使用 strict。
什么时候继续使用传统运算符
SQL/JSON 路径不是对 ->、->>、@> 的全面替代。读取固定字段时,payload ->> 'status' 更短;检查确定结构是否被包含时,@> 往往更直接。
真正适合路径表达式的场景,是多层导航、数组遍历以及带条件的结构匹配。可以按查询意图选择:固定位置取值或固定结构包含判断优先传统 JSONB 运算符;需要跨层级遍历并筛选时,再优先考虑 SQL/JSON 路径。