PHP小码农

Appearance

JSONPath（PostgreSQL 12+） ​

上一节讲了 jsonpath 函数族的返回值形态与选型，但真正决定「能查到什么」的，是路径字符串本身。PostgreSQL 12 引入了 SQL/JSON 标准的 jsonpath 数据类型：它不再只是一段字符串，而是一个可以存储、可以索引、可以作为参数传递的一等类型。本节从路径语言出发，把语法元素逐条拆开，再回到函数与运算符的配合上。

与「JSONB 查询函数」的分工

JSONB 查询函数（jsonb_path_query） 一节讲的是五个函数怎么选——_exists / _match / _query / _query_first / _query_array 的返回形态、vars 绑定、silent 参数、@? / @@ 与 GIN 索引的配合；本节聚焦 JSONPath 表达式语言本身——$ / . / [*] / .** / [start to end] / ?() / == / like_regex / starts with 这套语法，以及把路径落成列、落成索引、落成参数的工程写法。

前置知识

阅读本节前，建议先了解：JSONB 查询函数（jsonb_path_query）、JSONB 处理函数（jsonb_set / jsonb_insert）

基础概念 ​

JSONPath 的定位可以类比 XPath 之于 XML：用一段字符串在树形结构里描述「从根出发、经过哪些节点、满足什么条件」。

$.items[*] ? (@.price > 100).sku
│ │       │  │  │        │   │
│ │       │  │  │        │   └─ 投影：取 sku 字段
│ │       │  │  │        └───── 比较：大于
│ │       │  │  └────────────── 当前项
│ │       │  └───────────────── 过滤表达式
│ │       └──────────────────── 通配：所有数组元素
│ └──────────────────────────── 成员访问
└────────────────────────────── 根

路径的作用是「走路径 + 条件过滤 + 投影」三步合一。它返回的永远是一组结果（可能 0 个、1 个或多个），再由外层函数决定怎么呈现——这正是上一节五个函数共用一套路径的原因。

语法元素全表：

元素写法含义示例
根$文档根，路径起点$
成员访问.key取对象键$.user
成员访问（含特殊字符）."key with space"键名含空格/点时用双引号包裹$."user name"
数组下标[0]取数组第 n 个（从 0 开始，可为负）$.items[0]
通配（成员）.*对象的所有值$.*
通配（元素）[*]数组的所有元素$.items[*]
递归下降.**任意深度的所有后代$.**.sku
数组切片[start to end]取区间（用 to，不是 :）$.items[1 to 3]
过滤表达式? (条件)就地筛选$.items[*] ? (@.price > 100)
当前项@过滤条件里的当前元素? (@.qty >= 2)
逻辑与&&两个条件都成立? (@.a > 1 && @.b < 9)
逻辑或||任一条件成立? (@.a > 1 || @.b < 9)
逻辑非!条件取反? (!(@.disabled))
相等==注意是双等号? (@.status == "paid")
不等!=? (@.status != "void")
比较< <= > >=? (@.price >= 100)
正则谓词like_regex正则匹配，可带标志? (@.name like_regex "^A" flag "i")
前缀谓词starts withPostgreSQL 特有扩展? (@.sku starts with "SKU-")
变量引用$变量名外部通过 vars 注入? (@.price > $min)

关键术语：

术语含义
jsonpathPostgreSQL 12 引入的数据类型，存储已解析的路径表达式
lax / strict两种执行模式；lax（默认）自动适配结构不匹配，strict 要求每步严格成立
过滤谓词? (...) 中的布尔表达式，只有为真的项才会继续往下走
投影路径末端取字段的动作，如 [...].sku
vars函数参数，把外部 JSON 值注入路径中的 $name 变量
集合返回jsonb_path_query 返回 setof jsonb，会改变外层行数

最容易踩的坑先摆在这里：JSONPath 里的相等是 ==（两个等号），不是 SQL 的 =。写成 ? (@.a = 1) 会报语法错误。

sql
-- 错误！JSONPath 中相等必须用 ==
SELECT jsonb_path_query_array('[{"a": 1}]'::jsonb, '$[*] ? (@.a = 1)');
text
ERROR:  syntax error at or near "=" of jsonpath input
LINE 1: ...jsonb_path_query_array('[{"a": 1}]'::jsonb, '$[*] ? (@.a = 1)');
                                                                    ^

正确写法：

sql
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]'::jsonb, '$[*] ? (@.a == 1)') AS eq;
text
   eq
---------
 [{"a": 1}]
(1 row)

语法与用法 ​

text
-- 路径的基本骨架
$                                   -- 根
$.key                               -- 成员
$."key with space"                  -- 含特殊字符的键
$[0]  $[-1]                         -- 数组下标（负数从尾部数）
$[*]  $.*                           -- 通配
$.**                                -- 递归下降
$[1 to 3]                           -- 切片（用 to）
$[*] ? (条件)                        -- 过滤
$[*] ? (条件).投影                   -- 过滤 + 投影

-- 条件里的运算符
==   !=   <   <=   >   >=            -- 比较（相等是 ==）
&&   ||   !                          -- 逻辑
@.name like_regex "pattern" flag "i" -- 正则（i = 忽略大小写）
@.sku starts with "SKU-"             -- 前缀（PostgreSQL 扩展）

-- 模式前缀
lax $...                             -- 宽松（默认，可省略）
strict $...                          -- 严格

-- 作为参数传入函数
jsonb_path_query(target jsonb, path jsonpath)  -- 字符串会被隐式转成 jsonpath
target @? '$.path'                              -- 运算符形式，等价 jsonb_path_exists
target @@ '$.a > 1'                             -- 运算符形式，等价 jsonb_path_match

最小示例，一次看全四类语法：

sql
WITH d AS (SELECT '{"user": "alice", "items": [
                        {"sku": "SKU-A", "price": 120, "qty": 2},
                        {"sku": "SKU-B", "price":  45, "qty": 5},
                        {"sku": "SKU-C", "price": 300, "qty": 1}]}'::jsonb AS doc)
SELECT
  jsonb_path_query_array(doc, '$.items[*].sku')                        AS all_sku,
  jsonb_path_query_array(doc, '$.items[*] ? (@.price > 100).sku')       AS expensive,
  jsonb_path_query_array(doc, '$.items[0 to 1].sku')                    AS slice_,
  jsonb_path_query_array(doc, '$.** ? (@.price > 100).sku')             AS recursive
FROM d;
text
        all_sku        | expensive |    slice_     | recursive
-----------------------+-----------+---------------+-----------
 ["SKU-A", "SKU-B", "SKU-C"] | ["SKU-A", "SKU-C"] | ["SKU-A", "SKU-B"] | ["SKU-A", "SKU-C"]
(1 row)

详细说明 ​

成员访问与特殊键名 ​

普通键用 .key，键名以下划线、数字、字母组成时无需引号。键名含空格、连字符、点或中文标点时，必须用双引号包裹：

sql
SELECT
  jsonb_path_query_array('{"user": {"name": "alice"}}'::jsonb, '$.user.name')      AS plain,
  jsonb_path_query_array('{"user name": "bob"}'::jsonb, '$."user name"')           AS quoted,
  jsonb_path_query_array('{"a.b": 1}'::jsonb, '$."a.b"')                           AS dotted_key;
text
 plain | quoted | dotted_key
-------+--------+------------
 ["alice"] | ["bob"] | [1]
(1 row)

不加引号直接把 user name 写进去，路径解析会在空格处断掉：

sql
-- 错误！键名含空格必须加双引号
SELECT jsonb_path_query_array('{"user name": "bob"}'::jsonb, '$.user name');
text
ERROR:  syntax error at or near "name" of jsonpath input

在 lax 模式下，数组上取成员会自动穿透——这是最反直觉也最实用的一条规则：

sql
SELECT
  jsonb_path_query_array('[{"a":1},{"a":2}]'::jsonb, '$.a')        AS lax_auto_unwrap,
  jsonb_path_query_array('[{"a":1},{"a":2}]'::jsonb, 'strict $.a') AS strict_rejects;
text
 lax_auto_unwrap
-----------------
 [1, 2]
(1 row)
text
ERROR:  jsonpath member accessor can only be applied to an object

strict 模式拒绝这种自动适配。默认用 lax，只有在「数据结构必须严格符合契约，宁可报错也不静默返回空」时才用 strict。

数组下标与切片 ​

下标从 0 开始，负数从尾部数；切片用 to 而不是 :——这是与其他 JSONPath 实现的差异点。

sql
SELECT
  jsonb_path_query_array('["a","b","c","d","e"]'::jsonb, '$[0]')        AS first,
  jsonb_path_query_array('["a","b","c","d","e"]'::jsonb, '$[-1]')       AS last,
  jsonb_path_query_array('["a","b","c","d","e"]'::jsonb, '$[1 to 3]')   AS slice_,
  jsonb_path_query_array('["a","b","c","d","e"]'::jsonb, '$[last]')     AS via_last_kw;
text
 first | last |   slice_   | via_last_kw
-------+------+------------+------------
 ["a"] | ["e"] | ["b", "c", "d"] | ["e"]
(1 row)

切片的下标不能写变量（$[$lo to $hi] 不支持），但可以配合 vars 与过滤表达式实现等价效果——用 ? (@.idx >= $lo && @.idx <= $hi) 之类的条件代替。切片越界是静默裁剪而不是报错：

sql
SELECT
  jsonb_path_query_array('["a","b"]'::jsonb, '$[0 to 99]')   AS over_end,
  jsonb_path_query_array('["a","b"]'::jsonb, '$[5 to 9]')    AS out_of_range;
text
 over_end | out_of_range
----------+--------------
 ["a", "b"] | []
(1 row)

写 : 会直接报语法错误，记住这个差异能省下不少调试时间：

sql
-- 错误！切片用 to，不是 :
SELECT jsonb_path_query_array('["a","b","c"]'::jsonb, '$[0:2]');
text
ERROR:  syntax error at or near ":" of jsonpath input

通配、递归下降与投影 ​

sql
SELECT
  jsonb_path_query_array('{"a":1,"b":2}'::jsonb, '$.*')                AS wildcard_member,
  jsonb_path_query_array('[1,2,3]'::jsonb, '$[*]')                     AS wildcard_element,
  jsonb_path_query_array('{"o":{"i":{"deep":"x"}}}'::jsonb, '$.**')     AS recursive_all;
text
 wildcard_member | wildcard_element |   recursive_all
-----------------+------------------+-----------------------------
 [1, 2]          | [1, 2, 3]        | [{"i": {"deep": "x"}}, {"deep": "x"}, "x", {"o": {"i": {"deep": "x"}}}]
(1 row)

.** 会把所有层级的后代都吐出来（包括中间对象），通常要配合过滤条件缩小范围：

sql
-- 找出任意深度的 sku 字段
SELECT jsonb_path_query_array(
         '{"a": {"sku": "S1"}, "b": [{"sku": "S2"}, {"x": 1}]}'::jsonb,
         '$.**.sku'
       ) AS deep_skus;
text
   deep_skus
----------------
 ["S1", "S2"]
(1 row)

.** 的搜索范围随文档深度线性增长，大文档上开销可观。能用精确路径就不要用递归下降。

过滤表达式与逻辑运算 ​

过滤表达式 ? (...) 里的 @ 指代当前项。逻辑运算符优先级：! > && > ||，建议用括号明确分组。

sql
WITH d AS (SELECT '[{"sku":"A","price":120,"qty":2},
                  {"sku":"B","price":45,"qty":5},
                  {"sku":"C","price":300,"qty":0},
                  {"sku":"D","price":80,"qty":8}]}'::jsonb AS doc)
SELECT
  jsonb_path_query_array(doc, '$[*] ? (@.price > 100 && @.qty > 0).sku')          AS and_,
  jsonb_path_query_array(doc, '$[*] ? (@.price > 200 || @.qty > 5).sku')          AS or_,
  jsonb_path_query_array(doc, '$[*] ? (!(@.qty == 0)).sku')                       AS not_
FROM d;
text
  and_   |    or_     |   not_
---------+------------+----------
 ["A"]   | ["B", "C", "D"] | ["A", "B", "D"]
(1 row)

相等用 ==、不等用 !=，这套比较运算符与 SQL 的 = / <> 不同，必须分开记忆：

语义JSONPathSQL
等于===
不等于!=<>
与&&AND
或||OR
非!NOT
sql
SELECT
  jsonb_path_query_array('[1,2,3]'::jsonb, '$[*] ? (@ == 2)')   AS eq,
  jsonb_path_query_array('[1,2,3]'::jsonb, '$[*] ? (@ != 2)')   AS neq;
text
  eq  |  neq
------+--------
 [2]  | [1, 3]
(1 row)

比较运算符对不同类型的行为要注意：字符串与数字不能直接比较，混合比较会返回空结果而不是报错（lax 模式下）：

sql
-- 数字 100 与字符串 "100" 不能比
SELECT jsonb_path_query_array('[{"p": 100}, {"p": "100"}]'::jsonb, '$[*] ? (@.p > 50)') AS mixed;
text
   mixed
------------
 [{"p": 100}]
(1 row)

like_regex 与 starts with 谓词 ​

like_regex 用 POSIX 正则匹配字符串，flag 可选 i（忽略大小写）、s（点匹配换行）、m（多行）、x（扩展模式）：

sql
WITH d AS (SELECT '[{"name":"Alice"},{"name":"bob"},{"name":"Andy"}]'::jsonb AS doc)
SELECT
  jsonb_path_query_array(doc, '$[*] ? (@.name like_regex "^A").name')            AS case_sensitive,
  jsonb_path_query_array(doc, '$[*] ? (@.name like_regex "^a" flag "i").name')   AS ignore_case
FROM d;
text
 case_sensitive |   ignore_case
----------------+-----------------
 ["Alice", "Andy"] | ["Alice", "Andy"]
(1 row)

正则匹配只对字符串有效，对数字或对象使用会报错（lax 模式下会静默跳过不匹配类型的项）；strict 模式下直接抛错。

starts with 是 PostgreSQL 对 SQL/JSON 标准的扩展，比 like_regex "^..." 更快也更直观：

sql
SELECT jsonb_path_query_array(
         '[{"sku":"SKU-A"},{"sku":"SKU-B"},{"sku":"PRD-C"}]'::jsonb,
         '$[*] ? (@.sku starts with "SKU-").sku'
       ) AS prefixed;
text
     prefixed
------------------
 ["SKU-A", "SKU-B"]
(1 row)

同族扩展还有 ends with、contains，以及 Perl 风格操作符 ~~（等价 like_regex）、!~~（等价取反）。这些都是 PostgreSQL 特有扩展，写进代码前要确认目标版本支持。

jsonpath 作为数据类型：存储与索引 ​

jsonpath 不是「一段字符串」，它是可以建表存储、可以建索引、可以作为参数传递的一等类型。路径编译一次、复用多次，是性能与安全双赢的写法。

sql
DROP TABLE IF EXISTS search_paths;

CREATE TABLE search_paths (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   TEXT     NOT NULL UNIQUE,
  path   JSONPATH NOT NULL               -- 直接存路径类型
);

INSERT INTO search_paths (name, path) VALUES
  ('big_item',  '$.items[*] ? (@.qty >= 2)'::jsonpath),
  ('expensive', '$.items[*] ? (@.price > 100)'::jsonpath);
text
INSERT 0 2

路径存进表之后，就能用 @? 配合路径列做查询：

sql
SELECT p.name, d.id
FROM search_paths p
CROSS JOIN (VALUES (1)) AS d(id);   -- 示意：实际是关联业务表

jsonpath 类型本身也支持比较运算符（=、< 等，按内部表示排序），因此可以建 B 树索引做「路径去重」或「按路径查找配置」——但不支持 LIKE 等文本匹配：

sql
CREATE INDEX idx_search_paths_path ON search_paths USING btree (path);
text
CREATE INDEX

jsonpath 比较是内部的、不保证与文本形式一致。想看路径的规范化形式，直接选出来即可：

sql
SELECT name, path::text AS normalized FROM search_paths ORDER BY id;
text
     name    |           normalized
-------------+---------------------------------
 big_item    | $."items"[*]?(@."qty" >= 2)
 expensive   | $."items"[*]?(@."price" > 100)
(2 rows)

注意输出里自动补上了双引号——这是 jsonpath 的规范化输出，在 EXPLAIN 里看到的也是这个形式，属于正常现象。

与函数的配合：jsonpath 作为参数 ​

路径字符串在实际调用中会被隐式转换成 jsonpath，因此以下三种写法完全等价：

sql
WITH d AS (SELECT '{"a": 1}'::jsonb AS doc)
SELECT
  jsonb_path_exists(doc, '$.a')                       AS literal,
  jsonb_path_exists(doc, '$.a'::jsonpath)             AS explicit_cast,
  jsonb_path_exists(doc, '$.a'::text::jsonpath)       AS via_text
FROM d;
text
 literal | explicit_cast | via_text
---------+---------------+----------
 t       | t             | t
(1 row)

但隐式转换只在字面量场景成立。当路径来自变量时，PostgreSQL 会把它当作 unknown 字面量再尝试推断；若变量类型是 text，在某些上下文中会触发「函数不存在」错误，显式转成 jsonpath 最稳妥：

sql
CREATE OR REPLACE FUNCTION find_by_path(p_doc jsonb, p_path text)
RETURNS jsonb
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT jsonb_path_query_array(p_doc, p_path::jsonpath);   -- 显式转型
$$;

SELECT find_by_path('{"a":[1,2,3]}'::jsonb, '$.a[*] ? (@ > 1)') AS result;
text
 result
--------
 [2, 3]
(1 row)

用 p_path::jsonpath 还有一个隐性好处：非法路径在函数入口就报错，而不是等到执行到一半才发现。

jsonb_path_query 是集合返回函数 ​

这是路径语言里最实用的用法，单独重点说。jsonb_path_query 返回 setof jsonb，必须放在 SELECT 列表里展开，或者放进 FROM 用 LATERAL / 逗号连接。

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no TEXT  NOT NULL,
  data     JSONB NOT NULL
);

INSERT INTO orders (order_no, data) VALUES
  ('SO-001', '{"items": [{"sku":"A","price":120},{"sku":"B","price": 45}]}'),
  ('SO-002', '{"items": [{"sku":"C","price": 30}]}'),
  ('SO-003', '{"items": [{"sku":"D","price":500},{"sku":"E","price":800}]}');
text
INSERT 0 3

最常见、最推荐的形态是 FROM t, jsonb_path_query(...)——把 JSON 数组展开成行，与业务表连接：

sql
SELECT o.order_no,
       item ->> 'sku'            AS sku,
       (item ->> 'price')::int   AS price
FROM orders o,
     jsonb_path_query(o.data, '$.items[*] ? (@.price > 100)') AS item
ORDER BY o.order_no, sku;
text
 order_no | sku | price
----------+-----+-------
 SO-001   | A   |   120
 SO-003   | D   |   500
 SO-003   | E   |   800
(3 rows)

FROM a, f(a.x) 这种写法在 PostgreSQL 里是 CROSS JOIN LATERAL 的隐式形式，函数的参数可以引用左边表的列，称为隐式 LATERAL。等价写法：

sql
SELECT o.order_no, item ->> 'sku' AS sku
FROM orders o
CROSS JOIN LATERAL jsonb_path_query(o.data, '$.items[*] ? (@.price > 100)') AS item;

CROSS JOIN LATERAL 会丢掉没有匹配项的行（SO-002 消失了）。要保留每一行，用 LEFT JOIN LATERAL ... ON true：

sql
SELECT o.order_no, count(item) AS matched_cnt
FROM orders o
LEFT JOIN LATERAL jsonb_path_query(o.data, '$.items[*] ? (@.price > 100)') AS item ON true
GROUP BY o.order_no
ORDER BY o.order_no;
text
 order_no | matched_cnt
----------+-------------
 SO-001   |           1
 SO-002   |           0
 SO-003   |           2
(3 rows)

注意 SELECT 列表里的 SRF 不能与其他列混用（除非是在 FROM 里）。这是高频错误：

sql
-- 错误！SELECT 列表里 set-returning 函数不能与普通列混用
SELECT o.order_no, jsonb_path_query(o.data, '$.items[*]') FROM orders o;
text
ERROR:  set-returning functions must appear at top level of FROM

用 jsonb_path_query 替代 jsonb_array_elements ​

jsonb_path_query(doc, '$.items[*]') 与 jsonb_array_elements(doc -> 'items') 效果等价，但前者多了过滤能力，能把「展开」与「筛选」合并成一步。

sql
-- 传统写法：先展开再筛选，两段式
SELECT o.order_no, e.value ->> 'sku' AS sku
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.data -> 'items') AS e
WHERE (e.value ->> 'price')::int > 100
ORDER BY o.order_no, sku;

-- jsonpath 写法：展开与筛选一次完成
SELECT o.order_no, item ->> 'sku' AS sku
FROM orders o
CROSS JOIN LATERAL jsonb_path_query(o.data, '$.items[*] ? (@.price > 100)') AS item
ORDER BY o.order_no, sku;

两者结果相同。取舍标准：

场景推荐
只是简单展开数组jsonb_array_elements（更直接）
需要按条件筛选后再展开jsonb_path_query（一步到位）
需要递归下降或复杂路径jsonb_path_query
需要与 GIN 索引配合筛行路径无关，@? 负责筛行，展开函数负责取值

vars：参数化路径，防注入与复用 ​

绝不要用字符串拼接把外部值塞进路径。vars 参数是官方提供的参数化机制，路径里用 $变量名 引用，值通过 vars 传入：

sql
SELECT jsonb_path_query_array(
         data,
         '$.items[*] ? (@.price > $min && @.price < $max).sku',
         '{"min": 50, "max": 600}'::jsonb
       ) AS matched
FROM orders
WHERE order_no = 'SO-001';
text
  matched
------------
 ["A"]
(1 row)

正误对比——拼接写法的风险一目了然：

sql
-- 错误示范：把用户输入直接拼进路径，既有注入风险又有转义问题
-- path = '$.items[*] ? (@.sku == "' || p_sku || '")'
-- p_sku 如果是  SKU") || (@.price > 0 || ("  就会改变路径语义

-- 正确：用 vars 传值
SELECT jsonb_path_query_array(
         data,
         '$.items[*] ? (@.sku == $sku)',
         jsonb_build_object('sku', 'A')
       ) AS safe
FROM orders
WHERE order_no = 'SO-001';
text
  safe
------------------------------
 [{"sku": "A", "price": 120}]
(1 row)

vars 的键名不带 $，路径里引用时才加；变量值必须是合法 JSON。引用不存在的变量会报错，可用 silent 抑制：

sql
-- 报错：变量未定义
SELECT jsonb_path_query_array('[1,2]'::jsonb, '$[*] ? (@ > $min)');
text
ERROR:  could not find jsonpath variable "min"
sql
-- 静默：返回空
SELECT jsonb_path_query_array('[1,2]'::jsonb, '$[*] ? (@ > $min)', '{}'::jsonb, true) AS empty;
text
 empty
--------
 []
(1 row)

silent：静默模式的取舍 ​

silent 只在路径本身有结构性问题时起作用，典型两类：strict 模式遇到不匹配、引用不存在的变量。PostgreSQL 16 起支持命名参数写法 silent => true，可读性更好：

sql
-- 位置参数（12+ 都支持）
SELECT jsonb_path_query('[1,2]'::jsonb, 'strict $.a', '{}'::jsonb, true) AS positional;

-- 命名参数（PostgreSQL 16+）
SELECT jsonb_path_query('[1,2]'::jsonb, 'strict $.a', silent => true) AS named;
text
 positional
------------
(0 rows)

@? 与 @@ 运算符默认就是静默的，不需要额外传参：

sql
-- 结构不匹配也不报错，直接返回 false
SELECT '[1,2]'::jsonb @? 'strict $.a' AS op_silent;
text
 op_silent
-----------
 f
(1 row)

这是双刃剑：生产环境用它可以防止一条脏数据打挂整个查询；但开发环境会让真实拼写错误（$.items 写成 $.itemss）被静默吞掉。建议开发/测试保持 silent = false。

与索引的配合：@? @@ 能走 GIN，jsonb_path_query 不能 ​

这是路径选型的性能分水岭。GIN 的 jsonb_path_ops 操作符类专为路径查询设计，支持 @>、@?、@@：

sql
CREATE INDEX idx_orders_data_path ON orders USING gin (data jsonb_path_ops);
text
CREATE INDEX
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, order_no
FROM orders
WHERE data @? '$.items[*] ? (@.price > 100)';
text
                               QUERY PLAN
-------------------------------------------------------------------------
 Bitmap Heap Scan on orders
   Recheck Cond: (data @? '$."items"[*]?(@."price" > 100)'::jsonpath)
   ->  Bitmap Index Scan on idx_orders_data_path
         Index Cond: (data @? '$."items"[*]?(@."price" > 100)'::jsonpath)
(4 rows)

jsonb_path_query 无法用索支撑筛选——它返回数据而不是布尔，PostgreSQL 没法用它判断「这一行是否命中」。所以正确的范式是两段式：@? 负责快速缩小候选集（走 GIN），jsonb_path_query 只在命中行上展开取值。

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT o.id, item ->> 'sku' AS sku
FROM orders o
CROSS JOIN LATERAL jsonb_path_query(o.data, '$.items[*] ? (@.price > 100)') AS item
WHERE o.data @? '$.items[*] ? (@.price > 100)';
text
                              QUERY PLAN
-----------------------------------------------------------------------
 Nested Loop
   ->  Bitmap Heap Scan on orders o
         Recheck Cond: (data @? '$."items"[*]?(@."price" > 100)'::jsonpath)
         ->  Bitmap Index Scan on idx_orders_data_path
               Index Cond: (data @? '$."items"[*]?(@."price" > 100)'::jsonpath)
   ->  Function Scan on jsonb_path_query item
(5 rows)

慢写法对照（退化为全表扫描 + 每行计算）：

sql
-- 慢：jsonb_path_query_array 在 WHERE 里无法走索引
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders
WHERE jsonb_path_query_array(data, '$.items[*] ? (@.price > 100)') != '[]'::jsonb;
text
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on orders
   Filter: (jsonb_path_query_array(data, '...'::jsonpath) <> '[]'::jsonb)
(2 rows)

实战示例 ​

场景：一张订单表用 jsonb 存明细，需要按金额阈值筛选商品、统计数组长度、并对比加索引前后的执行计划。

第 1 步：建表与数据

sql
DROP TABLE IF EXISTS shop_orders;

CREATE TABLE shop_orders (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no TEXT  NOT NULL UNIQUE,
  data     JSONB NOT NULL,
  CONSTRAINT ck_data_is_object CHECK (jsonb_typeof(data) = 'object')
);

INSERT INTO shop_orders (order_no, data) VALUES
  ('SO-1001', '{"buyer":"alice","items":[{"sku":"A","price":120,"qty":2},{"sku":"B","price":45,"qty":5},{"sku":"C","price":300,"qty":1}]}'),
  ('SO-1002', '{"buyer":"bob","items":[{"sku":"D","price":80,"qty":8}]}'),
  ('SO-1003', '{"buyer":"carol","items":[{"sku":"E","price":500,"qty":3},{"sku":"F","price":20,"qty":10},{"sku":"G","price":240,"qty":1}]}'),
  ('SO-1004', '{"buyer":"dave","items":[]}');
text
CREATE TABLE
INSERT 0 4

第 2 步：筛出金额大于阈值的商品（阈值来自参数）

sql
SELECT o.order_no,
       item ->> 'sku'          AS sku,
       (item ->> 'price')::int AS price
FROM shop_orders o
CROSS JOIN LATERAL jsonb_path_query(o.data, '$.items[*] ? (@.price > $min)', '{"min": 100}'::jsonb) AS item
ORDER BY o.order_no, sku;
text
 order_no | sku | price
----------+-----+-------
 SO-1001  | A   |   120
 SO-1001  | C   |   300
 SO-1003  | E   |   500
 SO-1003  | G   |   240
(4 rows)

SO-1004 的 items 是空数组，因此没有匹配项，整行消失——这正是 CROSS JOIN LATERAL 的语义。要保留它，改用 LEFT JOIN LATERAL ... ON true。

第 3 步：统计每个订单的商品数量

路径 $.items[*] 的匹配项个数就是数组长度：

sql
SELECT o.order_no,
       count(item)                                              AS item_cnt,
       count(item) FILTER (WHERE (item ->> 'price')::int > 100) AS expensive_cnt
FROM shop_orders o
LEFT JOIN LATERAL jsonb_path_query(o.data, '$.items[*]') AS item ON true
GROUP BY o.order_no
ORDER BY o.order_no;
text
 order_no | item_cnt | expensive_cnt
----------+----------+---------------
 SO-1001  |        3 |             2
 SO-1002  |        1 |             0
 SO-1003  |        3 |             2
 SO-1004  |        0 |             0
(4 rows)

第 4 步：逻辑组合筛选（价格区间 + 数量条件）

sql
SELECT o.order_no, item ->> 'sku' AS sku
FROM shop_orders o
CROSS JOIN LATERAL jsonb_path_query(
         o.data,
         '$.items[*] ? (@.price >= $lo && @.price <= $hi && @.qty > 1).sku',
         '{"lo": 50, "hi": 400}'::jsonb
       ) AS item
ORDER BY o.order_no, sku;
text
 order_no | sku
----------+-----
 SO-1001  | A
 SO-1002  | D
 SO-1003  | G
(3 rows)

第 5 步：正则与前缀谓词

sql
SELECT
  jsonb_path_query_array('["alice","Bob","Amy"]'::jsonb, '$[*] ? (@ like_regex "^A")')      AS regex_a,
  jsonb_path_query_array('["alice","Bob","Amy"]'::jsonb, '$[*] ? (@ starts with "A")')      AS starts_a,
  jsonb_path_query_array('["alice","Bob","Amy"]'::jsonb, '$[*] ? (@ like_regex "^a" flag "i")') AS regex_ci;
text
   regex_a   |  starts_a |  regex_ci
-------------+-----------+--------------
 ["Amy"]     | ["Amy"]   | ["alice", "Amy"]
(1 row)

第 6 步：递归下降找任意深度的 SKU

嵌套结构（如 {"order":{"items":[...]}}）用精确路径要写全层级，递归下降可以一步到位：

sql
SELECT jsonb_path_query_array(
         '{"order":{"payment":{"items":[{"sku":"X"},{"sku":"Y"}]}}}'::jsonb,
         '$.**.sku'
       ) AS deep;
text
   deep
-----------
 ["X", "Y"]
(1 row)

第 7 步：加 GIN 索引前后的执行计划对比

sql
-- 无索引
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM shop_orders WHERE data @? '$.items[*] ? (@.price > 100)';
text
                 QUERY PLAN
---------------------------------------------
 Seq Scan on shop_orders
   Filter: (data @? '...'::jsonpath)
(2 rows)
sql
-- 加索引
CREATE INDEX idx_shop_orders_data_path ON shop_orders USING gin (data jsonb_path_ops);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM shop_orders WHERE data @? '$.items[*] ? (@.price > 100)';
text
                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on shop_orders
   Recheck Cond: (data @? '$."items"[*]?(@."price" > 100)'::jsonpath)
   ->  Bitmap Index Scan on idx_shop_orders_data_path
         Index Cond: (data @? '$."items"[*]?(@."price" > 100)'::jsonpath)
(4 rows)

小表上优化器可能仍选 Seq Scan（索引成本更高），这是正常的——索引的收益要在数据量上升后才体现，别用几十行的表下结论。

第 8 步：把常用路径存成 jsonpath 列

sql
DROP TABLE IF EXISTS saved_paths;

CREATE TABLE saved_paths (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  path JSONPATH NOT NULL
);

INSERT INTO saved_paths (name, path) VALUES
  ('expensive', '$.items[*] ? (@.price > 100)'::jsonpath),
  ('bulk',      '$.items[*] ? (@.qty > 5)'::jsonpath);

SELECT s.name, count(*) AS hit_rows
FROM saved_paths s
CROSS JOIN shop_orders o
WHERE o.data @? s.path
GROUP BY s.name
ORDER BY s.name;
text
    name    | hit_rows
------------+----------
 bulk       |        1
 expensive  |        2
(2 rows)

第 9 步：清理

sql
DROP TABLE shop_orders;
DROP TABLE saved_paths;
DROP TABLE orders;
text
DROP TABLE
DROP TABLE
DROP TABLE

注意事项 ​

  • JSONPath 的相等是 ==，不是 =。这是与 SQL 最大的差异，写错直接报语法错误。
sql
SELECT jsonb_path_query_array('[{"a":1}]'::jsonb, '$[*] ? (@.a == 1)');   -- 正确
-- '$[*] ? (@.a = 1)'                                                     -- 报错
  • 切片用 to 而不是 :。$[0:2] 会报语法错误，正确写法是 $[0 to 2]。
sql
SELECT jsonb_path_query_array('["a","b","c"]'::jsonb, '$[0 to 1]');   -- ["a", "b"]
  • jsonb_path_query 是集合返回函数，无匹配时整行消失（返回 0 行），不是返回 NULL；要保留行必须用 LEFT JOIN LATERAL ... ON true。
sql
FROM shop_orders o
LEFT JOIN LATERAL jsonb_path_query(o.data, '$.items[*]') AS item ON true

  • SELECT 列表里 SRF 不能与其他列混用，会报 set-returning functions must appear at top level of FROM。

  • 只有 @? 与 @@ 能走 GIN 索引，jsonb_path_query / _array / _first 都不行。WHERE 条件里坚持用运算符，函数只用来取值。

  • jsonb_path_ops 索引不支持 ? / ?| / ?& 存在操作符，若同时需要键存在判断，得建 jsonb_ops 索引。

sql
CREATE INDEX idx_x_ops ON t USING gin (data);   -- 需要 ? 系列时

  • .** 递归下降开销随文档深度线性增长，能用精确路径就不要用递归。

  • vars 里的键名不带 $，路径引用时才带；引用未定义变量会报 could not find jsonpath variable。

  • 不要用字符串拼接把外部值塞进路径，有注入与转义双重风险，一律用 vars。

sql
-- 危险
-- '$.items[*] ? (@.sku == "' || p_sku || '")'
-- 安全
SELECT jsonb_path_query_array(doc, '$.items[*] ? (@.sku == $sku)', jsonb_build_object('sku', p_sku));

  • silent = true 会吞掉路径拼写错误，开发环境务必保持 false。

  • lax 模式会自动穿透数组（$.a 在数组上遍历取 a），若业务上需要严格区分结构，加 strict 前缀。

  • starts with / ends with / contains 是 PostgreSQL 扩展，不是 SQL/JSON 标准的一部分，跨数据库迁移时注意。

  • jsonpath 的规范化输出会补双引号，$.a.b 在 EXPLAIN 里显示为 $."a"."b"，属正常现象。

  • jsonpath 类型的比较是内部表示的比较，不要依赖 ORDER BY path 得到「字典序」结果。

  • 切片下标不能是变量，需要动态区间时改用过滤表达式配合 vars。

最佳实践 ​

  • WHERE 条件里坚持用 @? / @@，让 GIN 索引发挥作用；jsonb_path_query* 只在 FROM / SELECT 里取值。
sql
-- 推荐：索引筛行 + 函数取值
SELECT o.id, item
FROM orders o
CROSS JOIN LATERAL jsonb_path_query(o.data, '$.items[*] ? (@.price > 100)') AS item
WHERE o.data @? '$.items[*] ? (@.price > 100)';
  • 参数化一律用 vars，绝不手工拼路径字符串。
sql
jsonb_path_query(doc, '$.items[*] ? (@.price > $min)', '{"min": 100}'::jsonb)
  • 索引优先选 jsonb_path_ops，它更小更快；只有还需要 ? / ?| / ?& 时才退回 jsonb_ops。
sql
CREATE INDEX idx_orders_path ON orders USING gin (data jsonb_path_ops);

  • 展开 JSON 数组一律用 LEFT JOIN LATERAL ... ON true，防止无匹配的行被丢掉。

  • 高频且路径固定的查询，把路径存成 jsonpath 列或落成生成列，路径只解析一次：

sql
path JSONPATH NOT NULL

  • 用 ARRAY 构造或 vars 传递动态部分，不要拼字符串。路径是代码，不是用户输入。

  • 开发/测试环境保持 silent = false，让路径错误尽早暴露；生产只在数据来源不可信时开启。

  • 写路径时优先用 starts with / contains 表达前缀/包含语义，比 like_regex "^..." 更快也更清晰。

  • 用 EXPLAIN (ANALYZE, BUFFERS) 验证索引生效，确认走的是 Bitmap Index Scan 而不是 Seq Scan。

  • 封装路径查询函数时标 IMMUTABLE PARALLEL SAFE（注意：jsonb_path_query 是 IMMUTABLE，但 _tz 版本依赖会话时区，应标 STABLE）：

sql
CREATE FUNCTION order_has_big_item(doc jsonb) RETURNS boolean
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$ SELECT jsonb_path_exists(doc, '$.items[*] ? (@.qty >= 2)'); $$;
  • 注意 _tz 后缀版本（jsonb_path_query_tz / jsonb_path_exists_tz，PostgreSQL 13+）：处理 datetime() 方法时它们使用会话时区而非 UTC，与不带后缀的版本结果可能不同。需要本地时区语义时显式选用 _tz 版本，并把函数标成 STABLE。
sql
-- PostgreSQL 13+：使用会话时区解析 datetime()
SELECT jsonb_path_query_tz(doc, '$.events[*] ? (@.at.datetime() > "2026-01-01".datetime())');
  • 大文档不要用 .** 全量递归，先缩小路径再递归：
sql
-- 优于 $.**.sku
'$.order.items[*].sku'

下一节

继续学习：NEXTVAL / CURRVAL / LASTVAL / SETVAL

参考链接 ​