JSONB 查询函数（jsonb_path_query）

当 JSON 从"展示载体"变成"查询对象"，问题就从"怎么取值"升级为"怎么在路径上做筛选"。jsonb_path_query 家族是 PostgreSQL 给出的答案：把 SQL/JSON 标准的 jsonpath 表达式作为参数传给函数，让数据库在 JSON 内部完成"走路径 + 条件过滤 + 投影"。本节从函数视角讲解这五个函数的签名差异、返回形态、变量绑定与索引配合，路径语言本身的语法细节留给下一节。

与「JSONB 的查询操作」的分工

JSONB 的查询操作一章从取值手段出发，系统讲 -> / ->> / #> / #>> 操作符、? 存在判断与 jsonb_each 展开，jsonpath 只是其中一节；本节聚焦 jsonpath 函数族本身——五个函数的返回值形态与选型、vars 变量绑定、silent 参数、@? / @@ 操作符与 GIN 索引的配合、以及在 JOIN LATERAL 与函数封装中的工程用法。路径表达式的语法（[*]、?()、strict/lax）详见 JSONPath（PostgreSQL 12+）。

前置知识

阅读本节前，建议先了解：JSONB 构建函数（jsonb_build_object / jsonb_agg）

基础概念

jsonpath 函数族一共五个，返回值形态是唯一的选型依据：要布尔用前两个，要数据用后三个。

函数	返回类型	回答的问题	典型用途
`jsonb_path_exists`	`boolean`	有没有满足条件的？	`WHERE` 过滤、存在性校验
`jsonb_path_match`	`boolean`	整条路径的谓词是否成立？	单值断言、布尔表达式求值
`jsonb_path_query`	`setof jsonb`	把每个匹配项摊成一行	`JOIN` 展开、逐项处理
`jsonb_path_query_first`	`jsonb`	第一条匹配项是什么？	取"最优/最新"一条
`jsonb_path_query_array`	`jsonb`	所有匹配项打包成数组	直接作为响应字段返回

关键术语：

概念	术语	说明
路径语言	JSONPath	SQL/JSON 标准定义，用字符串表达"怎么走 + 怎么筛"
根	Root `$`	路径起点，永远从文档根开始
上下文项	Context Item	当前所处的位置，`@` 表示它
过滤谓词	Filter `?(...)`	就地筛选，只在符合条件的项上继续
变量绑定	`vars` 参数	从外部注入 JSON 值，路径里用 `$name` 引用
静默模式	`silent`	把结构错误抑制为"无结果"而不是抛错
宽松/严格	`lax` / `strict`	路径遇到结构不匹配时是否自动适配
存在操作符	`@?` / `@@`	语法糖，`@?` 等价 `jsonb_path_exists`，`@@` 等价 `jsonb_path_match`

最小示例：

sql

SELECT
  jsonb_path_exists(doc, '$.items[*] ? (@.qty >= 2)')     AS has_big,
  jsonb_path_query_first(doc, '$.items[*] ? (@.qty >= 2)') AS first_big,
  jsonb_path_query_array(doc, '$.items[*].sku')           AS all_skus
FROM (SELECT '{"items": [{"sku": "A", "qty": 3}, {"sku": "B", "qty": 1}, {"sku": "C", "qty": 5}]}'::jsonb AS doc) AS t;

text

 has_big |          first_big          |     all_skus
---------+-----------------------------+------------------
 t       | {"qty": 3, "sku": "A"}      | ["A", "B", "C"]
(1 row)

三个函数读的是同一个文档，但一个给布尔、一个给单个值、一个给数组——这就是整个函数族的组织逻辑。

语法与用法

text

-- 全部五个函数共享同一套可选参数
jsonb_path_exists     (target jsonb, path jsonpath [, vars jsonb [, silent boolean]]) → boolean
jsonb_path_match      (target jsonb, path jsonpath [, vars jsonb [, silent boolean]]) → boolean
jsonb_path_query      (target jsonb, path jsonpath [, vars jsonb [, silent boolean]]) → setof jsonb
jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb [, silent boolean]]) → jsonb
jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb [, silent boolean]]) → jsonb

-- 操作符形式（语法糖）
target @? path      → boolean    -- 等价 jsonb_path_exists(target, path)
target @@ path      → boolean    -- 等价 jsonb_path_match(target, path)

四个参数位逐个说明：

参数	类型	是否必填	说明
`target`	`jsonb`	必填	被查询的文档
`path`	`jsonpath`	必填	路径表达式，字符串会被隐式转成 `jsonpath`
`vars`	`jsonb`	可选	变量绑定，路径里用 `$name` 引用
`silent`	`boolean`	可选	`true` 时不抛错，把错误当"无结果"

三组最小示例，从简到繁：

sql

-- 1. 最简：只传两个参数
SELECT jsonb_path_exists('{"a": 1}'::jsonb, '$.a') AS exists_a;   -- true

-- 2. 带变量
SELECT jsonb_path_query_array('{"a": 1}'::jsonb, '$.a ? (@ > $min)', '{"min": 0}'::jsonb) AS bigger;

-- 3. 带 silent，抑制结构性错误
SELECT jsonb_path_query('[1, 2]'::jsonb, 'strict $.a', '{}'::jsonb, true) AS no_error;

text

 exists_a | bigger | no_error
----------+--------+----------
 t        | [1]    |
(1 row)

操作符形式：@? 与 @@

@? 与 @@ 是函数调用的语法糖，写起来更短，且在 WHERE 中一眼能看出是 JSON 过滤：

操作符	等价函数	语义
`doc @? '$.a.b'`	`jsonb_path_exists(doc, '$.a.b')`	路径是否有匹配项
`doc @@ '$.a > 1'`	`jsonb_path_match(doc, '$.a > 1')`	路径产出的谓词是否为真

sql

-- 两种写法完全等价
SELECT
  doc @? '$.items[*] ? (@.qty >= 2)'                          AS via_op,
  jsonb_path_exists(doc, '$.items[*] ? (@.qty >= 2)')         AS via_fn,
  doc @@ '$.total > 100'                                      AS match_op,
  jsonb_path_match(doc, '$.total > 100')                      AS match_fn
FROM (SELECT '{"total": 299, "items": [{"qty": 3}]}'::jsonb AS doc) AS t;

text

 via_op | via_fn | match_op | match_fn
--------+--------+----------+----------
 t      | t      | t        | t
(1 row)

两者都能走 GIN 索引，这是它们相对 jsonb_path_query 的关键优势——见后文"与索引的配合"。

vars：把外部值注入路径

vars 让你把变量值当作参数传进路径，避免手工拼字符串（拼接既易错又有注入风险）。路径里用 $变量名 引用：

sql

SELECT jsonb_path_query_array(
         doc,
         '$.items[*].sku ? (@ != $exclude && @ != $exclude2)',
         '{"exclude": "B", "exclude2": "D"}'::jsonb
       ) AS skus
FROM (SELECT '{"items": [{"sku":"A"},{"sku":"B"},{"sku":"C"},{"sku":"D"}]}'::jsonb AS doc) AS t;

text

   skus
-----------
 ["A", "C"]
(1 row)

变量必须是合法 JSON 值，vars 参数的键不带 $，路径里引用时才加：

sql

-- 对比：路径里写 $min，vars 里写 "min"
SELECT jsonb_path_query_array('[1,2,3]'::jsonb, '$[*] ? (@ > $min)', '{"min": 1}'::jsonb) AS result;

text

 result
--------
 [2, 3]
(1 row)

若路径引用了 vars 中不存在的变量，会直接报错；不想让它报错就传 silent = true：

sql

-- 报错
SELECT jsonb_path_query_array('[1,2,3]'::jsonb, '$[*] ? (@ > $min)');

-- 静默：返回 NULL（无结果）
SELECT jsonb_path_query_array('[1,2,3]'::jsonb, '$[*] ? (@ > $min)', '{}'::jsonb, true) AS safe;

text

 safe
--------
(1 row)

详细说明

五个函数的返回形态决定用途

这是选型的唯一判断依据，先看一张"要什么 → 用哪个"表：

你想要的结果	用哪个	返回	是否可能产出行
有没有匹配（布尔）	`jsonb_path_exists` / `@?`	`boolean`	否
谓词是否为真	`jsonb_path_match` / `@@`	`boolean`	否
每个匹配项分别处理	`jsonb_path_query`	`setof jsonb`	是
只要第一个匹配项	`jsonb_path_query_first`	`jsonb`	否
打包成一个数组	`jsonb_path_query_array`	`jsonb`	否

jsonb_path_query 是唯一会改变行数的，与 unnest 类似。它是集合返回函数（SRF），在 SELECT 列表中会展开成多行：

sql

-- 一行输入，三行输出
SELECT jsonb_path_query(doc, '$.items[*] ? (@.qty >= 2)') AS matched
FROM (SELECT '{"items": [{"sku":"A","qty":3},{"sku":"B","qty":1},{"sku":"C","qty":5}]}'::jsonb AS doc) AS t;

text

         matched
--------------------------
 {"qty": 3, "sku": "A"}
 {"qty": 5, "sku": "C"}
(2 rows)

而 jsonb_path_query_array 的输入输出始终是一行对一行：

sql

-- 一行输入，一行输出
SELECT jsonb_path_query_array(doc, '$.items[*] ? (@.qty >= 2)') AS matched_arr
FROM (SELECT '{"items": [{"sku":"A","qty":3},{"sku":"B","qty":1},{"sku":"C","qty":5}]}'::jsonb AS doc) AS t;

text

                matched_arr
---------------------------------------------
 [{"qty": 3, "sku": "A"}, {"qty": 5, "sku": "C"}]
(1 row)

无匹配时的行为差异更重要，这是最常踩的坑：

场景	`jsonb_path_query`	`jsonb_path_query_first`	`jsonb_path_query_array`	`jsonb_path_exists`
无匹配	0 行（行消失）	`NULL`	`'[]'`（空数组）	`false`

sql

SELECT
  (SELECT count(*) FROM jsonb_path_query(doc, '$.nope[*]'))  AS query_rows,
  jsonb_path_query_first(doc, '$.nope[*]')                   AS first_val,
  jsonb_path_query_array(doc, '$.nope[*]')                   AS array_val,
  jsonb_path_exists(doc, '$.nope[*]')                        AS exists_val
FROM (SELECT '{"a": 1}'::jsonb AS doc) AS t;

text

 query_rows | first_val | array_val | exists_val
------------+-----------+-----------+------------
          0 |           | []        | f
(1 row)

实践含义：接口层拼响应时用 jsonb_path_query_array（永远拿到一个数组，不会整行消失）；做 WHERE 过滤用 jsonb_path_exists / @?（能走索引）；要逐项 JOIN 才用 jsonb_path_query。

jsonb_path_match 的严格要求

jsonb_path_match 要求路径恰好产出一个布尔值，这是它与 jsonb_path_exists 最容易被混淆的地方：

场景	`jsonb_path_exists`	`jsonb_path_match`
路径产出 0 个值	`false`	`NULL`
路径产出 1 个布尔值	`true`	该布尔值
路径产出多个值	`true`	报错
路径产出 1 个非布尔值	`true`	报错

sql

-- 正确：路径产出一个谓词
SELECT jsonb_path_match('{"a": 5}'::jsonb, '$.a > 3') AS ok;   -- true

-- 错误：路径产出多个值
SELECT jsonb_path_match('{"a": [1,2,3]}'::jsonb, '$.a[*] > 0');

text

ERROR:  jsonpath expression in match is not a predicate
DETAIL:  ... yields multiple items ...

sql

-- 错误：路径产出非布尔值
SELECT jsonb_path_match('{"a": 5}'::jsonb, '$.a');

text

ERROR:  jsonpath expression in match is not a predicate

规则：@@ / jsonb_path_match 的路径必须以比较或逻辑表达式结尾（如 $.a > 3、$.a == 1 && $.b < 2），并且只能有一个结果。任何可能产出多个值的路径（带 [*] 的）都不能用 match。

sql

-- 想表达"存在任一 qty >= 2"，应该用 exists + filter，而不是 match
SELECT jsonb_path_exists(doc, '$.items[*] ? (@.qty >= 2)') AS any_big
FROM (SELECT '{"items": [{"qty":1},{"qty":5}]}'::jsonb AS doc) AS t;

与索引的配合：@? @@ 与 GIN

这是 jsonpath 函数族最实际的性能话题。GIN 索引的 jsonb_ops 与 jsonb_path_ops 操作符类都支持 @?、@@、@> 等运算符：

操作符类	支持	索引大小	查询速度
`jsonb_ops`	`@>`、`?`、`?	`、`?&`、`@?`、`@@`	较大
`jsonb_path_ops`	`@>`、`@?`、`@@`（不支持 `?` 系列）	更小	更快

sql

-- jsonb_path_ops 更省空间、查询更快，专为路径查询设计
CREATE INDEX idx_events_payload_path ON user_events USING gin (payload jsonb_path_ops);

-- 能走索引
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM user_events WHERE payload @? '$.items[*] ? (@.qty >= 2)';

text

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on user_events
   Recheck Cond: (payload @? '$."items"[*]?(@."qty" >= 2)'::jsonpath)
   ->  Bitmap Index Scan on idx_events_payload_path
         Index Cond: (payload @? '$."items"[*]?(@."qty" >= 2)'::jsonpath)
(4 rows)

关键限制：jsonb_path_query / jsonb_path_query_first / jsonb_path_query_array 不是索引友好的形式。它们返回的是数据而不是布尔，PostgreSQL 无法用 GIN 索引直接支撑"按路径筛选行"。要改写：

sql

-- 慢：无法用 GIN 支撑筛选
SELECT * FROM user_events
WHERE jsonb_path_query_array(payload, '$.items[*] ? (@.qty >= 2)') != '[]'::jsonb;

-- 快：改成运算符 + 索引
SELECT * FROM user_events
WHERE payload @? '$.items[*] ? (@.qty >= 2)';

-- 甚至更快：GIN 的 @? 先把范围缩小，再用 jsonb_path_query 展开
SELECT e.id, jsonb_path_query(e.payload, '$.items[*] ? (@.qty >= 2)') AS matched
FROM user_events e
WHERE e.payload @? '$.items[*] ? (@.qty >= 2)';

这正是"索引筛行 + 函数取值"的两段式范式：@? 负责快速缩小候选集（走 GIN），jsonb_path_query 只在命中行上展开取数据。

与 JOIN LATERAL 配合：把 JSON 数组接进关系运算

jsonb_path_query 返回 setof jsonb，天然适合 JOIN LATERAL——为每一行展开它的 JSON 数组，再与别的表连接：

sql

SELECT e.id, item ->> 'sku' AS sku, (item ->> 'qty')::int AS qty
FROM user_events e
CROSS JOIN LATERAL jsonb_path_query(e.payload, '$.items[*] ? (@.qty >= 2)') AS item
ORDER BY e.id, sku;

text

 id |  sku  | qty
----+-------+-----
  1 | SKU-A |   3
  1 | SKU-C |   5
  2 | SKU-D |   2
(3 rows)

CROSS JOIN LATERAL 与 LEFT JOIN LATERAL ... ON true 的差别，与数组展开一节完全一致：前者会丢掉没有匹配项的行，后者保留：

sql

-- 保留所有事件，没有匹配项的行 item 为 NULL
SELECT e.id, item
FROM user_events e
LEFT JOIN LATERAL jsonb_path_query(e.payload, '$.items[*] ? (@.qty >= 2)') AS item ON true;

注意 jsonb_path_query 在 FROM 中的列名：它返回单列，别名直接指代该列，item ->> 'sku' 取值。

silent 参数的正确用法

silent 只在路径本身有结构性问题时起作用，典型的两个场景：strict 模式遇到不匹配、引用了不存在的外部变量。

sql

-- strict 模式：数组上取成员，报错
SELECT jsonb_path_query('[1, 2]'::jsonb, 'strict $.a');

text

ERROR:  jsonpath member accessor can only be applied to an object

sql

-- 加 silent，把错误降级为"无结果"
SELECT jsonb_path_query('[1, 2]'::jsonb, 'strict $.a', '{}'::jsonb, true) AS silent_result;

text

 silent_result
---------------
(0 rows)

silent 是掩盖问题的双刃剑：生产环境用它防止一条脏数据打挂整个查询，但也可能让真实的路径拼写错误被静默吞掉。建议只在"数据来自不可信外部、结构无法保证"的场景使用，并在开发/测试环境保持 false 让错误暴露。

把路径封装成函数与生成列

路径字符串是常量时，可以封装成 SQL 函数，让调用方不必重复写：

sql

CREATE OR REPLACE FUNCTION order_has_big_item(doc jsonb)
RETURNS boolean
LANGUAGE sql
IMMUTABLE
PARALLEL SAFE
AS $$
  SELECT jsonb_path_exists(doc, '$.items[*] ? (@.qty >= 2)');
$$;

-- 调用
SELECT id FROM user_events WHERE order_has_big_item(payload);

IMMUTABLE 是关键：jsonpath 求值是纯函数，标成 IMMUTABLE 后可用于表达式索引；标错成 STABLE/VOLATILE 会导致索引不可用（详见 IMMUTABLE / STABLE / VOLATILE）。

也可以直接把路径结果固化成生成列：

sql

ALTER TABLE user_events
  ADD COLUMN big_item_skus jsonb
  GENERATED ALWAYS AS (jsonb_path_query_array(payload, '$.items[*] ? (@.qty >= 2).sku')) STORED;

生成列要求表达式 IMMUTABLE，而 jsonb_path_query_array 本身是 IMMUTABLE 的，因此可以直接用——这是把"查询时计算"转成"写入时计算"的有效手段。

jsonpath 的两种模式：lax 与 strict

路径表达式的默认模式是 lax，它会自动适配结构不匹配（数组上取键就遍历数组元素取键），strict 则要求每一步都严格成立：

模式	数组上取成员 `$.a`	对象上取 `[0]`	无匹配	结构错误
`lax`（默认）	自动对每个元素取 `.a`	报错	空集	部分报错
`strict`	报错	报错	空集	报错

sql

-- lax：数组自动"穿透"
SELECT jsonb_path_query_array('[{"a":1},{"a":2}]'::jsonb, '$.a') AS lax_unwrap;

-- strict：同样的路径直接报错
SELECT jsonb_path_query_array('[{"a":1},{"a":2}]'::jsonb, 'strict $.a') AS strict_fail;

text

 lax_unwrap
------------
 [1, 2]
(1 row)

text

ERROR:  jsonpath member accessor can only be applied to an object

实践建议：默认用 lax（容错），只在"数据结构必须严格符合契约、宁可报错也不静默返回空"时用 strict。路径语言的全部语法细节（[*]、?()、strict/lax、.key() 方法等）见下一节。

实战示例

场景：埋点事件表用 jsonb 存 payload，需要查询"包含高价商品的订单""最近登录的城市"等。

第 1 步：建表与索引

sql

DROP TABLE IF EXISTS user_events;

CREATE TABLE user_events (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  event_type TEXT NOT NULL,
  payload    JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 专为路径查询设计的 GIN 索引
CREATE INDEX idx_user_events_payload_path
  ON user_events USING gin (payload jsonb_path_ops);

第 2 步：插入数据

sql

INSERT INTO user_events (user_id, event_type, payload) VALUES
  (1, 'order', '{"order_no": "SO-001", "total": 299,
                 "items": [{"sku": "SKU-A", "qty": 3}, {"sku": "SKU-B", "qty": 1}]}'),
  (1, 'order', '{"order_no": "SO-002", "total": 158,
                 "items": [{"sku": "SKU-C", "qty": 1}]}'),
  (2, 'login', '{"city": "上海", "device": "ios", "tags": ["vip", "new"]}'),
  (2, 'order', '{"order_no": "SO-003", "total": 499,
                 "items": [{"sku": "SKU-D", "qty": 2}, {"sku": "SKU-E", "qty": 5}]}'),
  (3, 'login', '{"city": "北京", "device": "android", "tags": ["trial"]}');

第 3 步：用 @? 过滤（走索引）

sql

-- 找出有 qty >= 2 商品的订单事件
EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, payload ->> 'order_no' AS order_no
FROM user_events
WHERE payload @? '$.items[*] ? (@.qty >= 2)';

text

 id | order_no
----+----------
  1 | SO-001
  4 | SO-003
(2 rows)

第 4 步：用 jsonb_path_query 展开匹配项

sql

-- 索引筛行 + 函数取值，两段式
SELECT e.id,
       e.payload ->> 'order_no' AS order_no,
       item ->> 'sku'           AS sku,
       (item ->> 'qty')::int    AS qty
FROM user_events e
CROSS JOIN LATERAL jsonb_path_query(e.payload, '$.items[*] ? (@.qty >= 2)') AS item
WHERE e.payload @? '$.items[*] ? (@.qty >= 2)'
ORDER BY e.id, sku;

text

 id | order_no |  sku  | qty
----+----------+-------+-----
  1 | SO-001   | SKU-A |   3
  4 | SO-003   | SKU-D |   2
  4 | SO-003   | SKU-E |   5
(3 rows)

第 5 步：用 jsonb_path_query_array 打包成数组字段

sql

-- 每个订单事件输出"大件商品的 sku 列表"，接口层直接可用
SELECT id,
       payload ->> 'order_no' AS order_no,
       jsonb_path_query_array(payload, '$.items[*] ? (@.qty >= 2).sku') AS big_skus
FROM user_events
WHERE event_type = 'order'
ORDER BY id;

text

 id | order_no |   big_skus
----+----------+---------------
  1 | SO-001   | ["SKU-A"]
  2 | SO-002   | []
  4 | SO-003   | ["SKU-D", "SKU-E"]
(3 rows)

注意 SO-002 没有大件商品，输出是 [] 而不是行消失——这正是选 _array 变体的理由。

第 6 步：变量绑定做参数化查询

sql

-- 用 vars 传入"最低数量"与"排除的城市"
SELECT jsonb_path_query_array(
         payload,
         '$.tags[*] ? (@ != $exclude)',
         '{"exclude": "trial"}'::jsonb
       ) AS tags
FROM user_events
WHERE event_type = 'login'
  AND jsonb_path_exists(payload, '$.city ? (@ == $city)', '{"city": "上海"}'::jsonb)
ORDER BY id;

text

   tags
-----------
 ["vip", "new"]
(1 row)

第 7 步：一次性统计各订单的大件商品数

sql

SELECT e.id,
       e.payload ->> 'order_no' AS order_no,
       count(item)              AS big_item_cnt
FROM user_events e
LEFT JOIN LATERAL jsonb_path_query(e.payload, '$.items[*] ? (@.qty >= 2)') AS item ON true
WHERE e.event_type = 'order'
GROUP BY e.id, order_no
ORDER BY e.id;

text

 id | order_no | big_item_cnt
----+----------+--------------
  1 | SO-001   |            1
  2 | SO-002   |            0
  4 | SO-003   |            2
(3 rows)

第 8 步：清理

sql

DROP TABLE user_events;

注意事项

jsonb_path_query 是无匹配时整行消失（返回 0 行），不是返回 NULL；要"每行都保留"必须用 LEFT JOIN LATERAL ... ON true。

sql

-- 会丢行
, jsonb_path_query(payload, '$.items[*] ? (@.qty >= 2)') AS item

-- 保留行
LEFT JOIN LATERAL jsonb_path_query(payload, '$.items[*] ? (@.qty >= 2)') AS item ON true

jsonb_path_match / @@ 要求路径产出一个布尔谓词，带 [*] 的路径会报 not a predicate；想表达"存在任一"，用 @? / jsonb_path_exists。

sql

-- 错误
SELECT jsonb_path_match('{"a":[1,2]}'::jsonb, '$.a[*] > 1');
-- 正确
SELECT jsonb_path_exists('{"a":[1,2]}'::jsonb, '$.a[*] ? (@ > 1)');

只有 @? 与 @@ 能走 GIN 索引，jsonb_path_query* 三个函数的返回值不是布尔，无法支撑索引筛选，必须改写成"运算符筛行 + 函数取值"。

sql

-- 快
WHERE payload @? '$.items[*] ? (@.qty >= 2)'

-- 慢（全表扫描 + 每行计算）
WHERE jsonb_path_query_array(payload, '$.items[*] ? (@.qty >= 2)') != '[]'::jsonb

vars 里的键名不带 $，路径里引用时才带，写错会报 could not find jsonpath variable。

sql

SELECT jsonb_path_query('[1,2]'::jsonb, '$[*] ? (@ > $min)', '{"min": 1}'::jsonb);

无匹配时 jsonb_path_query_array 返回 [] 而非 NULL，与 jsonb_agg 的结果可能为 NULL 不同；接口层需要 [] 时它天然满足。

sql

SELECT jsonb_path_query_array('{"a":1}'::jsonb, '$.nope[*]');   -- []

silent = true 会吞掉路径拼写错误，开发环境务必保持 false，否则 $.items 写成 $.itemss 也只会安静地返回空。

sql

-- 危险：路径写错也无声
SELECT jsonb_path_query_array(doc, '$.itemss[*]', '{}'::jsonb, true);

-- 安全：让错误暴露
SELECT jsonb_path_query_array(doc, '$.itemss[*]');

lax 模式会自动穿透数组，$.a 在数组上会遍历每个元素取 a；若业务上需要严格区分，用 strict 前缀。

sql

SELECT jsonb_path_query_array('[{"a":1},{"a":2}]'::jsonb, '$.a');   -- [1, 2]

jsonpath 字符串会被规范化为 jsonpath 类型，在 EXPLAIN 里看到的引号形式可能与写法不同，这是正常现象。

text

Recheck Cond: (payload @? '$."items"[*]?(@."qty" >= 2)'::jsonpath)

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

sql

-- 需要 ? 系列时必须用 jsonb_ops
CREATE INDEX idx_ev_payload_ops ON user_events USING gin (payload);

路径里的 @ 与 SQL 的 @ 无关，它是 jsonpath 的"当前上下文项"，别与变量符号混淆。

sql

'$.items[*] ? (@.qty >= $min)'   -- @ 是当前元素，$min 是外部变量

封装函数时务必标 IMMUTABLE，标错易变性等级会导致生成列、表达式索引不可用。

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)'); $$;

最佳实践

先按返回值形态选函数：要布尔用 @? / @@，要数组用 _array，要逐项展开用 jsonb_path_query。

sql

-- WHERE 过滤
WHERE payload @? '$.items[*] ? (@.qty >= 2)'
-- 接口字段
jsonb_path_query_array(payload, '$.items[*].sku')

WHERE 条件里坚持用 @? / @@，让 GIN 索引发挥作用，避免在条件里调用 jsonb_path_query*。

sql

-- 推荐
WHERE payload @? '$.items[*] ? (@.qty >= 2)'

索引优先选 jsonb_path_ops，它更小更快；只有当还需要 ? / ?| / ?& 时才退回 jsonb_ops。

sql

CREATE INDEX idx_ev_path ON user_events USING gin (payload jsonb_path_ops);

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

sql

LEFT JOIN LATERAL jsonb_path_query(payload, '$.items[*]') AS item ON true

参数化一律用 vars，绝不手工拼接路径字符串，避免注入与转义问题。

sql

jsonb_path_exists(payload, '$.city ? (@ == $city)', '{"city": "上海"}'::jsonb)

接口层固定用 _array 变体，保证"无匹配 = 空数组"，响应结构稳定。

sql

jsonb_path_query_array(payload, '$.items[*] ? (@.qty >= 2).sku')

高频且路径固定的查询，考虑落成生成列，把查询期计算转成写入期计算。

sql

ADD COLUMN big_skus jsonb GENERATED ALWAYS AS (jsonb_path_query_array(payload, '$.items[*] ? (@.qty >= 2).sku')) STORED

封装函数一律标 IMMUTABLE PARALLEL SAFE，并在生产前用 EXPLAIN 确认索引生效。

sql

CREATE FUNCTION f(doc jsonb) RETURNS boolean LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$ ... $$;

开发环境不要用 silent = true，让路径错误尽早暴露；生产只在数据不可信时开启。

sql

-- 开发
SELECT jsonb_path_query_array(doc, '$.items[*]');

用 EXPLAIN (ANALYZE, BUFFERS) 验证索引，确认 @? 确实走了 Bitmap Index Scan，而不是退化成 Seq Scan。

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM user_events WHERE payload @? '$.items[*] ? (@.qty >= 2)';

继续学习：JSONB 处理函数（jsonb_set / jsonb_insert）

JSONB 查询函数（jsonb_path_query） ​

基础概念 ​

语法与用法 ​

操作符形式：@? 与 @@ ​

vars：把外部值注入路径 ​

详细说明 ​

五个函数的返回形态决定用途 ​

jsonb_path_match 的严格要求 ​

与索引的配合：@? @@ 与 GIN ​

与 JOIN LATERAL 配合：把 JSON 数组接进关系运算 ​

silent 参数的正确用法 ​

把路径封装成函数与生成列 ​

jsonpath 的两种模式：lax 与 strict ​

实战示例 ​

注意事项 ​

最佳实践 ​

参考链接 ​

JSONB 查询函数（jsonb_path_query）

基础概念

语法与用法

操作符形式：@? 与 @@

vars：把外部值注入路径

详细说明

五个函数的返回形态决定用途

jsonb_path_match 的严格要求

与索引的配合：@? @@ 与 GIN

与 JOIN LATERAL 配合：把 JSON 数组接进关系运算

silent 参数的正确用法

把路径封装成函数与生成列

jsonpath 的两种模式：lax 与 strict

实战示例

注意事项

最佳实践

参考链接