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 的元素。

用同一组数据比较 @? 与 @@

下面的主示例同时验证“是否存在匹配项”“谓词检查”和“返回匹配值”三种需求:

sql
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;

预期结果:

text
 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 路径。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

使用 SQL JSON 路径

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通