JSONB 的查询操作
数据写进 JSONB 列之后，下一个问题就是「怎么把它取出来」。本节围绕四组提取操作符（->、->>、#>、#>>）、存在性操作符（?、?|、?&）、展开函数（jsonb_each / jsonb_array_elements 系列）与 jsonpath 查询函数（jsonb_path_query 家族）展开，重点讲清返回类型差异与NULL 陷阱这两个最容易踩的坑。
前置知识
阅读本节前，建议先了解：JSONB 的创建与插入
基础概念
查询 JSONB 的手段按「问题复杂度」分成四层，先建立整体认知：
| 层次 | 手段 | 典型问题 | 返回类型 |
|---|---|---|---|
| 取值 | ->、->>、#>、#>> | 某个键/下标的值是什么？ | jsonb 或 text |
| 存在 | ?、`? | 、?&` | 这个键存在吗？ |
| 包含 | @>、<@ | 文档里有没有这一段结构？ | boolean |
| 路径 | @?、@@、jsonb_path_query | 满足条件的元素有哪些？ | boolean / setof jsonb |
| 展开 | jsonb_each、jsonb_array_elements | 把 JSON 变成多行 | 集合（多列/多行） |
四组提取操作符是最常用的，它们的差异归结为两点：返回类型（jsonb 还是 text）和路径写法（逐级还是数组）。
| 操作符 | 右操作数类型 | 返回类型 | 含义 |
|---|---|---|---|
-> | integer | jsonb | 取数组第 n 个元素（下标从 0 开始） |
-> | text | jsonb | 取对象的指定键 |
->> | integer | text | 同上，但结果转成 text |
->> | text | text | 同上，但结果转成 text |
#> | text[] | jsonb | 按路径取子文档 |
#>> | text[] | text | 按路径取子文档并转成 text |
两条铁律必须先记住：
- 带
>的返回jsonb，带>>的返回text。这不是「多个箭头」，而是「多一层文本转换」。->得到的还是一个 JSON 值（数字还是数字，字符串带引号），->>得到的是去掉引号的纯文本，类型是text。 - 结构不匹配时返回 NULL，而不是报错。键不存在、下标越界、对象上用了数组下标、数组上用了对象键，一律得到 SQL NULL。
关键术语：
- 提取操作符（Extraction Operators）：
->、->>、#>、#>>四组，用于从 JSON 中取出一个子值。 - 路径数组（Path Array）：
#>与#>>的右操作数，形如'{a,b,1}'，元素按顺序依次深入，键与下标可以混用。 - 存在操作符（Existence Operator）：
?及其变体，判断字符串是否作为顶层键或数组元素出现。 - jsonpath：SQL/JSON 标准的路径语言，用字符串表达「怎么走 + 怎么筛」，
jsonb_path_query系列函数都基于它。
语法与用法
-- 1. 单级提取
jsonb -> integer → jsonb -- 数组第 n 个元素（从 0 开始，负数从尾部数）
jsonb -> text → jsonb -- 对象指定键的值
jsonb ->> integer → text
jsonb ->> text → text
-- 2. 多级路径提取
jsonb #> text[] → jsonb
jsonb #>> text[] → text
-- 3. 存在性判断
jsonb ? text → boolean -- 单个字符串是否存在于顶层
jsonb ?| text[] → boolean -- 任一存在即为真
jsonb ?& text[] → boolean -- 全部存在才为真
-- 4. 包含判断（详见「包含与存在」一节）
jsonb @> jsonb → boolean
jsonb <@ jsonb → boolean
-- 5. jsonpath 运算符
jsonb @? jsonpath → boolean -- 路径是否返回至少一项
jsonb @@ jsonpath → boolean -- 谓词检查是否成立
-- 6. 展开为多行
jsonb_each(jsonb) → setof (key text, value jsonb)
jsonb_each_text(jsonb) → setof (key text, value text)
jsonb_array_elements(jsonb) → setof jsonb
jsonb_array_elements_text(jsonb) → setof text
jsonb_object_keys(jsonb) → setof text
-- 7. jsonpath 函数族
jsonb_path_query(jsonb, jsonpath [, vars jsonb [, silent boolean]]) → setof jsonb
jsonb_path_query_array(jsonb, jsonpath [, vars jsonb [, silent boolean]]) → jsonb
jsonb_path_query_first(jsonb, jsonpath [, vars jsonb [, silent boolean]]) → jsonb
jsonb_path_exists(jsonb, jsonpath [, vars jsonb [, silent boolean]]) → boolean
jsonb_path_match(jsonb, jsonpath [, vars jsonb [, silent boolean]]) → boolean先看一组最小对照，感受 -> 与 ->> 的差别：
SELECT
doc -> 'a' AS arrow_jsonb,
doc ->> 'a' AS arrow_text,
doc -> 'n' AS num_jsonb,
doc ->> 'n' AS num_text,
pg_typeof(doc -> 'n') AS type_jsonb,
pg_typeof(doc ->> 'n') AS type_text
FROM (SELECT '{"a": "hello", "n": 42}'::jsonb AS doc) t; arrow_jsonb | arrow_text | num_jsonb | num_text | type_jsonb | type_text
-------------+------------+-----------+----------+------------+-----------
"hello" | hello | 42 | 42 | jsonb | text
(1 row)注意 arrow_jsonb 的值是 "hello"——带双引号，因为它是一个合法的 JSON 字符串值；而 arrow_text 是 hello，是真正的 SQL 文本。type_text 列显示类型确实是 text。
详细说明
-> 与 ->>：返回类型是关键差异
选择哪一个，取决于拿到值之后要干什么：
- 要继续往下钻（再取子键），必须用
->，因为->>返回text，链式操作会立刻报错。 - 要跟 SQL 字符串比较（
WHERE ... = 'x'），必须用->>，否则左边是jsonb、右边是text，会走上强制类型转换的慢路径，还可能因为引号不一致而永远不相等。
SELECT
doc -> 'user' -> 'name' AS nested_jsonb, -- 全程 jsonb，链式继续钻
doc ->> 'user' AS user_text, -- 中间层转成 text（丢括号）
doc #>> '{user,name}' AS path_text -- 一步到位
FROM (SELECT '{"user": {"name": "alice", "age": 30}}'::jsonb AS doc) t; nested_jsonb | user_text | path_text
--------------+-----------------------------+-----------
"alice" | {"name": "alice", "age": 30}| alice
(1 row)注意 doc ->> 'user' 得到的是整个子对象序列化后的文本，而 text 上不存在 ->> 操作符，所以 doc ->> 'user' ->> 'name' 是非法写法（详见「注意事项」）。
两者不等价的典型场景：判断「值是否等于某文本」。
-- 推荐：右侧是 text，与 SQL 字符串直接可比
SELECT count(*) FROM user_events WHERE payload ->> 'event_level' = 'high';
-- 错误！左边是 jsonb，右侧会隐式转成 jsonb，看起来能跑，但索引与语义都变差
SELECT count(*) FROM user_events WHERE payload -> 'event_level' = '"high"'::jsonb;第二种写法必须手工补上 JSON 的引号，一旦值里有转义字符（如 \"）就会对不上，且完全无法利用表达式索引的条件推导。
#> 与 #>>：多级路径一次到位
当层级较深时，#> 比链式 -> 更简洁，也更适合把路径当成参数传入（例如从配置或应用层动态构造）。
SELECT
doc #> '{a,b,1}' AS as_jsonb,
doc #>> '{a,b,1}' AS as_text,
doc #> '{a,b,9}' AS out_of_range,
doc #> '{a,x}' AS missing_key
FROM (SELECT '{"a": {"b": ["foo", "bar"]}}'::jsonb AS doc) t; as_jsonb | as_text | out_of_range | missing_key
----------+---------+--------------+-------------
"bar" | bar | |
(1 row)路径数组里的元素既可以是对象的键，也可以是数组的下标，还能混用：
SELECT doc #>> '{items,0,sku}' AS first_sku,
doc #> '{items,1}' AS second_item
FROM (SELECT '{"items": [{"sku": "A-1001", "qty": 2}, {"sku": "B-2002", "qty": 1}]}'::jsonb AS doc) t; first_sku | second_item
-----------+-----------------------------
A-1001 | {"sku": "B-2002", "qty": 1}
(1 row)路径数组的写法规范：
| 写法 | 说明 |
|---|---|
'{a,b}' | 最常用，路径元素用逗号分隔，无需给键加引号 |
'{a,b,1}' | 数字元素被视为数组下标 |
'{a,"b c"}' | 键中含空格或逗号时用双引号包起来 |
('{a,' || i || '}')::text[] | 需要动态拼接时先构造文本再转 text[] |
数组下标与负索引
数组下标从 0 开始，这与 SQL 数组的下标规则（从 1 开始）完全相反，是最容易写错的地方。
SELECT
arr -> 0 AS idx_0,
arr -> 2 AS idx_2,
arr -> -1 AS idx_last,
arr -> -3 AS idx_first,
arr -> 5 AS idx_overflow,
arr ->> -1 AS last_text
FROM (SELECT '["a", "b", "c"]'::jsonb AS arr) t; idx_0 | idx_2 | idx_last | idx_first | idx_overflow | last_text
-------+-------+----------+-----------+--------------+-----------
"a" | "c" | "c" | "a" | | c
(1 row)-1 是最后一个元素，-2 是倒数第二个，以此类推。越界返回 NULL，不报错。
用下标取「对象」或用键取「数组」都会得到 NULL：
SELECT
'{"a": 1}'::jsonb -> 0 AS object_by_index, -- NULL
'[1, 2, 3]'::jsonb -> 'a' AS array_by_key, -- NULL
'{"a": 1}'::jsonb #> '{0}' AS object_path_index, -- NULL
'[1, 2, 3]'::jsonb #> '{a}' AS array_path_key; -- NULL object_by_index | array_by_key | object_path_index | array_path_key
-----------------+--------------+-------------------+----------------
| | |
(1 row)四个 NULL。这种「静默返回 NULL」的设计让查询语句不会因为某一行数据结构不同而整体失败——这在 JSON 场景下是优点，但也意味着写入侧的约束检查很重要（参见上一节的 jsonb_typeof 检查约束）。
PostgreSQL 14+ 的下标语法
除操作符外，jsonb 还支持类似数组的下标写法 doc['a']['b'] 与切片 doc['a'][0:2]，语义等同于 ->。这是 PostgreSQL 14 引入的特性，写法更简洁，但不支持 text 转换，需要文本时仍要用 ->>。
存在性判断：?、?|、?&
存在操作符回答的是「这个字符串在不在顶层」。它比 -> 多了一个关键能力：能区分「键不存在」和「键存在但值为 JSON null」。
SELECT
doc ? 'a' AS has_a,
doc ? 'b' AS has_b,
doc ? 'c' AS has_null_c,
doc -> 'c' AS value_c,
(doc -> 'c') IS NULL AS extract_is_null
FROM (SELECT '{"a": 1, "c": null}'::jsonb AS doc) t; has_a | has_b | has_null_c | value_c | extract_is_null
-------+-------+------------+---------+-----------------
t | f | t | null | f
(1 row)doc ? 'c' 返回 t，但 doc -> 'c' 用 IS NULL 判断却是 f——因为它是 JSON null，不是 SQL NULL。想用提取操作符区分这两者，只能借助 jsonb_typeof：
SELECT
jsonb_typeof(doc -> 'a') AS type_of_a, -- number
jsonb_typeof(doc -> 'c') AS type_of_c, -- null（JSON null）
jsonb_typeof(doc -> 'missing') AS type_of_missing -- SQL NULL，无输出
FROM (SELECT '{"a": 1, "c": null}'::jsonb AS doc) t; type_of_a | type_of_c | type_of_missing
-----------+-----------+-----------------
number | null |
(1 row)?|（任一存在）与 ?&（全部存在）用于批量判断：
SELECT
doc ?| array['a', 'z'] AS any_exists, -- t：a 存在
doc ?& array['a', 'z'] AS all_exist, -- f：z 不存在
doc ?& array['a', 'b'] AS all_exist_2 -- t：都存在
FROM (SELECT '{"a": 1, "b": 2}'::jsonb AS doc) t; any_exists | all_exist | all_exist_2
------------+-----------+-------------
t | f | t
(1 row)三个必须记住的语义边界：
- 只看顶层。
'{"foo": {"bar": "baz"}}'::jsonb ? 'bar'返回f，bar在第二层，不算数。 - 对象的值不参与匹配。
'{"foo": "bar"}'::jsonb ? 'bar'返回f——bar是值不是键。 - 数组元素参与匹配。
'["a", "b", "c"]'::jsonb ? 'b'返回t；连标量字符串本身也算：'"foo"'::jsonb ? 'foo'返回t。
展开函数：把 JSON 变回行
查询结果要参与 SQL 的 GROUP BY、ORDER BY、JOIN，就得先把 JSON 展开成行。这一族函数都是返回集合（set-returning function），要写在 FROM 或 LATERAL 子查询里。
| 函数 | 输入要求 | 输出 |
|---|---|---|
jsonb_each | 对象 | key text, value jsonb |
jsonb_each_text | 对象 | key text, value text |
jsonb_array_elements | 数组 | 单列 value jsonb |
jsonb_array_elements_text | 数组 | 单列 value text |
jsonb_object_keys | 对象 | 单列 jsonb_object_keys text（仅键） |
jsonb_each 系列把对象拆成键值对：
SELECT key, value, pg_typeof(value) AS value_type
FROM jsonb_each('{"name": "alice", "age": 30, "active": true}'::jsonb); key | value | value_type
--------+---------+------------
active | true | jsonb
age | 30 | jsonb
name | "alice" | jsonb
(3 rows)注意两点：jsonb_each 的 key 列是 text，而 value 列是 jsonb，所以字符串值仍带引号；行的顺序不保证是写入顺序（jsonb 内部按键的字节序排列）。需要稳定顺序就显式加 ORDER BY key。
换成 jsonb_each_text 即可拿到干净的文本：
SELECT key, value
FROM jsonb_each_text('{"name": "alice", "age": 30}'::jsonb)
ORDER BY key; key | value
------+-------
age | 30
name | alice
(2 rows)jsonb_array_elements 把数组拆成多行，是「一对多」查询的主力：
SELECT value, pg_typeof(value) AS value_type
FROM jsonb_array_elements('[1, "two", {"three": 3}, null]'::jsonb); value | value_type
----------+------------
1 | jsonb
"two" | jsonb
{"three": 3} | jsonb
null | jsonb
(4 rows)传入的对象不是对应类型时会报错，这是展开函数与提取操作符最大的不同：
-- 错误！jsonb_each 要求对象，输入是数组
SELECT * FROM jsonb_each('[1, 2, 3]'::jsonb);ERROR: cannot call jsonb_each on a non-object-- 错误！jsonb_array_elements 要求数组，输入是对象
SELECT * FROM jsonb_array_elements('{"a": 1}'::jsonb);ERROR: cannot call jsonb_array_elements on a non-array因此生产环境里展开之前，务必先用 jsonb_typeof 判类型，或者把结构约束固化到表定义里。
配合 LATERAL 可以做「数组元素 × 主表行」的笛卡尔展开：
SELECT e.id, e.event_type, item.value ->> 'sku' AS sku
FROM user_events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item
WHERE e.event_type = 'purchase';jsonb_object_keys 只取键，适合做「字段是否存在」的批量探查：
SELECT user_id, k AS payload_key
FROM user_events, LATERAL jsonb_object_keys(payload) AS k
ORDER BY user_id, payload_key;jsonpath：jsonb_path_query 家族
-> 与 #> 只能表达「直线路径」，一旦需要「数组里满足条件的元素」，就必须换用 jsonpath 语言。jsonpath 表达式写在字符串里，用 $ 表示文档根，形如 '$.items[*] ? (@.qty >= 2).sku'。
基础语法速查：
| 语法 | 含义 | 示例 |
|---|---|---|
$ | 文档根 | '$' |
$.key | 取对象成员 | '$.user' |
$[0] | 取数组元素 | '$[0]' |
$[*] | 所有数组元素 | '$.items[*]' |
.* | 所有对象成员值 | '$.*' |
.** | 递归下降 | '$.**.sku' |
?(...) | 过滤条件 | '$.items[*] ? (@.qty > 1)' |
@ | 当前项 | '? (@ > 2)' |
vars | 外部变量 | '$.items[*] ? (@.qty > $min)' |
函数族的分工：
| 函数 | 返回 | 适用场景 |
|---|---|---|
jsonb_path_exists | boolean | 只问「有没有」，最安全、最快 |
jsonb_path_match | boolean | 谓词检查，路径必须只产出一个布尔值，否则返回 NULL |
jsonb_path_query | setof jsonb | 要取出所有匹配项 |
jsonb_path_query_first | jsonb | 只要第一个匹配项 |
jsonb_path_query_array | jsonb | 把所有匹配项打包成一个 JSON 数组 |
SELECT
jsonb_path_exists(doc, '$.items[*] ? (@.qty >= 2)') AS has_large_qty,
jsonb_path_query_first(doc, '$.items[*] ? (@.qty >= 2)') AS first_large,
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) t; has_large_qty | first_large | all_skus
---------------+-----------------------+--------------------
t | {"sku": "A", "qty": 3}| ["A", "B", "C"]
(1 row)jsonb_path_query 把多个匹配项摊成多行：
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) t; matched
-----------------------
{"sku": "A", "qty": 3}
{"sku": "C", "qty": 5}
(2 rows)外部变量用 vars 参数传入，注意变量在 jsonpath 里以 $ 开头：
SELECT jsonb_path_query_array(doc, '$.items[*].sku ? (@ != $exclude)', '{"exclude": "B"}'::jsonb) AS skus
FROM (SELECT '{"items": [{"sku": "A"}, {"sku": "B"}, {"sku": "C"}]}'::jsonb AS doc) t; skus
------------
["A", "C"]
(1 row)jsonb_path_match 与 jsonb_path_exists 的差异值得单独强调：
SELECT
jsonb_path_exists(doc, '$.a[*] ? (@ > 2)') AS by_exists, -- t
jsonb_path_match(doc, '$.a[*] > 2') AS by_match -- t
FROM (SELECT '{"a": [1, 2, 3, 4]}'::jsonb AS doc) t; by_exists | by_match
-----------+----------
t | t
(1 row)两者都能用，但路径结果不是单个布尔值时 jsonb_path_match 返回 NULL，而 jsonb_path_exists 稳定返回 t/f。在 WHERE 条件里，NULL 等价于「不匹配」，看起来行为一致；但在 SELECT 列表里，NULL 会污染统计（count(flag) 不计 NULL）。除非确实需要谓词语义，否则优先用 jsonb_path_exists。
最后，silent 参数控制「结构不匹配是否报错」：
-- 错误！strict 模式要求路径每一步都严格成立：数组上不能取成员
SELECT jsonb_path_query('[1, 2]'::jsonb, 'strict $.a');ERROR: jsonpath member accessor can only be applied to an object-- lax（默认）模式会自动适应不匹配的结构，返回空集而不报错
SELECT jsonb_path_query('[1, 2]'::jsonb, '$.a') AS lax_result;
-- 显式开启 silent：把结构性错误也抑制为「无结果」
SELECT jsonb_path_query('[1, 2]'::jsonb, 'strict $.a', '{}'::jsonb, true) AS silent_result; lax_result
------------
(0 rows)lax（默认模式）会自动适应不匹配的数据结构，剩余的结构性错误被抑制为空集；strict 模式则要求路径每一步都精确匹配，否则直接报错。结构多变的数据用默认的 lax 更省事，结构固定的场景用 strict 能更早暴露数据问题。
提取操作符的边界行为与 NULL 陷阱
把所有「返回 NULL」的情况汇总成一张表，便于排查「为什么查出来是空的」：
| 表达式 | 结果 | 原因 |
|---|---|---|
'{"a":1}'::jsonb -> 'b' | NULL | 键不存在 |
'[1,2]'::jsonb -> 5 | NULL | 下标越界 |
'[1,2]'::jsonb -> 'a' | NULL | 数组不能用键取 |
'{"a":1}'::jsonb -> 0 | NULL | 对象不能用下标取 |
'{"a":null}'::jsonb -> 'a' | JSON null | 键存在，值是 JSON null |
'{"a":null}'::jsonb ->> 'a' | SQL NULL | JSON null 转文本即 SQL NULL |
'"scalar"'::jsonb -> 0 | NULL | 标量无子结构 |
这里第六行是本节的头号陷阱：->> 遇到 JSON null 与遇到「键不存在」都会返回 SQL NULL，两者无法通过结果区分。要区分，必须用 ? 或 jsonb_typeof：
SELECT
doc ->> 'b' AS missing_via_text, -- NULL
doc ->> 'c' AS jsonnull_via_text, -- NULL
doc ? 'b' AS key_b_exists, -- f
doc ? 'c' AS key_c_exists, -- t
jsonb_typeof(doc -> 'c') AS jsonnull_type -- null 字符串
FROM (SELECT '{"a": 1, "c": null}'::jsonb AS doc) t; missing_via_text | jsonnull_via_text | key_b_exists | key_c_exists | jsonnull_type
------------------+-------------------+--------------+--------------+---------------
| | f | t | null
(1 row)在 WHEN / COALESCE 场景下写清楚兜底值，能显著减少下游的判空分支：
SELECT
COALESCE(payload ->> 'duration_ms', '0')::int AS duration_ms
FROM user_events
WHERE event_type = 'page_view';把提取结果还原成 SQL 类型
->> 出来的是 text，要参与数值运算或日期比较必须显式转换：
SELECT
(payload ->> 'duration_ms')::int AS duration_ms,
(payload ->> 'price')::numeric(12, 2) AS price,
(payload ->> 'created_at')::timestamptz AS created_at
FROM user_events
WHERE event_type = 'add_to_cart';有一类特殊写法必须避免：
-- 错误！先转 jsonb 再转 int，绕了一大圈，且引号规则容易出错
SELECT ((payload -> 'duration_ms')::text)::int FROM user_events;直接 ->> 一步到位即可。另一种情况则是必须用 ->——取出嵌套对象/数组后继续处理：
-- 取出数组后用 jsonb_array_length 数个数
SELECT jsonb_array_length(payload -> 'items') AS item_count
FROM user_events
WHERE event_type = 'purchase';
-- 取出数组后展开
SELECT item.value ->> 'sku' AS sku
FROM user_events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item
WHERE e.event_type = 'purchase';jsonb 也支持与 text 之间的显式转换，但注意 JSON 字符串转 text 会保留引号，通常不是你想要的结果：
SELECT
('"hello"'::jsonb)::text AS scalar_cast, -- "hello"，带引号
'{"a":1}'::jsonb::text AS object_cast -- {"a": 1}，规范化后的 JSON 文本
; scalar_cast | object_cast
-------------+---------------
"hello" | {"a": 1}
(1 row)需要不带引号的文本，用 #>> '{}' 这种「空路径」技巧也能取到：
SELECT '"hello"'::jsonb #>> '{}' AS unquoted; -- hello实战示例
沿用上一节的 user_events 表，走一遍从建档到多维查询的完整流程。
第一步：准备数据
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 varchar(32) NOT NULL,
payload jsonb NOT NULL DEFAULT '{}'::jsonb,
occurred_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO user_events (user_id, event_type, payload, occurred_at) VALUES
(1001, 'page_view', '{"url": "/products/list", "referer": null, "duration_ms": 1200}', now() - interval '3 hour'),
(1001, 'add_to_cart', '{"sku": "A-1001", "qty": 2, "price": 199.00, "tags": ["promo", "digital"]}', now() - interval '2 hour'),
(1002, 'page_view', '{"url": "/index", "referer": "https://example.com", "duration_ms": 300}', now() - interval '90 minute'),
(1002, 'purchase', '{"order_no": "SO-2026-0001", "items": [{"sku": "A-1001", "qty": 2}, {"sku": "B-2002", "qty": 1}], "total": 598.00}', now() - interval '1 hour'),
(1003, 'add_to_cart', '{"sku": "C-3003", "qty": 1, "price": 59.90, "tags": ["digital"]}', now() - interval '10 minute');DROP TABLE
CREATE TABLE
INSERT 0 5第二步：基础取值——四个操作符的对照
SELECT
id,
payload ->> 'url' AS url, -- 纯文本
payload ->> 'duration_ms' AS duration_raw, -- 仍是 text
(payload ->> 'duration_ms')::int AS duration_ms, -- 转成 int
payload -> 'referer' AS referer_jsonb, -- jsonb，null 会带出来
payload #>> '{url}' AS url_via_path
FROM user_events
WHERE event_type = 'page_view'
ORDER BY id; id | url | duration_raw | duration_ms | referer_jsonb | url_via_path
----+------------------+--------------+-------------+---------------+--------------------
1 | /products/list | 1200 | 1200 | null | /products/list
3 | /index | 300 | 300 | "https://example.com" | /index
(2 rows)注意 referer_jsonb 一列：第一行是 JSON null（原文如此），第二行是带引号的字符串。用 -> 取出来时两者形态不同，用 ->> 取出则都会退化成 SQL NULL 或纯文本。
第三步：条件筛选——存在、包含与数值比较
-- 3.1 哪些事件的 payload 里带了 tags 键（顶层存在）
SELECT id, event_type, payload -> 'tags' AS tags
FROM user_events
WHERE payload ? 'tags'
ORDER BY id;
-- 3.2 tags 数组里含 promo 的事件
SELECT id, payload ->> 'sku' AS sku
FROM user_events
WHERE payload -> 'tags' ? 'promo'
ORDER BY id;
-- 3.3 停留时长超过 500ms 的页面浏览（文本转数值后比较）
SELECT id, payload ->> 'url' AS url, (payload ->> 'duration_ms')::int AS duration_ms
FROM user_events
WHERE event_type = 'page_view'
AND (payload ->> 'duration_ms')::int > 500
ORDER BY duration_ms DESC;
-- 3.4 一次性限定多个键（全部存在）
SELECT id, event_type
FROM user_events
WHERE payload ?& array['sku', 'qty', 'price']
ORDER BY id;-- 3.1
id | event_type | tags
----+-------------+--------------------------
2 | add_to_cart | ["promo", "digital"]
5 | add_to_cart | ["digital"]
(2 rows)
-- 3.2
id | sku
----+--------
2 | A-1001
(1 row)
-- 3.3
id | url | duration_ms
----+----------------+-------------
1 | /products/list | 1200
(1 row)
-- 3.4
id | event_type
----+------------
2 | add_to_cart
5 | add_to_cart
(2 rows)第三步的 3.2 有个性能隐患：payload -> 'tags' ? 'promo' 中 ? 没有作用在索引列本身上，无法使用 jsonb 的 GIN 索引（见后续「JSONB 索引」一节）。条件允许时改写成包含判断即可走上索引：
-- 等价且可走索引的写法（前提是知道 tags 是数组）
SELECT id, payload ->> 'sku' AS sku
FROM user_events
WHERE payload @> '{"tags": ["promo"]}'
ORDER BY id;第四步：展开数组，把「一次购买」拆成「多个商品行」
SELECT
e.id AS event_id,
e.payload ->> 'order_no' AS order_no,
item.value ->> 'sku' AS sku,
(item.value ->> 'qty')::int AS qty,
item.ordinality AS item_no
FROM user_events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items')
WITH ORDINALITY AS item(value, ordinality)
WHERE e.event_type = 'purchase'
ORDER BY e.id, item.ordinality; event_id | order_no | sku | qty | item_no
----------+----------------+---------+-----+---------
4 | SO-2026-0001 | A-1001 | 2 | 1
4 | SO-2026-0001 | B-2002 | 1 | 2
(2 rows)WITH ORDINALITY 会给集合函数附加一个从 1 开始的行号，用来还原数组中的原始顺序（因为 jsonb 不保存顺序信息，行号只能靠这里生成）。
汇总每个订单的商品件数与金额：
SELECT
e.payload ->> 'order_no' AS order_no,
count(*) AS line_count,
sum((item.value ->> 'qty')::int) AS total_qty,
(e.payload ->> 'total')::numeric AS order_total
FROM user_events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item
WHERE e.event_type = 'purchase'
GROUP BY e.id, e.payload
ORDER BY order_no; order_no | line_count | total_qty | order_total
----------------+------------+-----------+-------------
SO-2026-0001 | 2 | 3 | 598.00
(1 row)第五步：用 jsonpath 做条件过滤
-- 5.1 存在满足条件的商品行（数量 >= 2）
SELECT id, payload ->> 'order_no' AS order_no
FROM user_events
WHERE jsonb_path_exists(payload, '$.items[*] ? (@.qty >= 2)')
ORDER BY id;
-- 5.2 把所有商品的 sku 提取为一个数组
SELECT id, jsonb_path_query_array(payload, '$.items[*].sku') AS skus
FROM user_events
WHERE jsonb_path_exists(payload, '$.items')
ORDER BY id;
-- 5.3 递归下降查找任意深度的 sku 字段
SELECT id, jsonb_path_query(payload, '$.**.sku') AS any_sku
FROM user_events
WHERE jsonb_path_exists(payload, '$.**.sku')
ORDER BY id;
-- 5.4 用外部变量做参数化过滤
SELECT id
FROM user_events
WHERE jsonb_path_exists(payload, '$.items[*] ? (@.sku == $target)', '{"target": "B-2002"}'::jsonb)
ORDER BY id;-- 5.1
id | order_no
----+---------------
4 | SO-2026-0001
(1 row)
-- 5.2
id | skus
----+--------------------
4 | ["A-1001", "B-2002"]
(1 row)
-- 5.3
id | any_sku
----+---------
2 | "A-1001"
4 | "A-1001"
4 | "B-2002"
5 | "C-3003"
(4 rows)
-- 5.4
id
----
4
(1 row)第六步：对象展开做键值透视
SELECT
e.id,
kv.key,
kv.value
FROM user_events e
CROSS JOIN LATERAL jsonb_each_text(e.payload) AS kv(key, value)
WHERE e.event_type = 'page_view'
ORDER BY e.id, kv.key; id | key | value
----+-------------+------------------
1 | duration_ms | 1200
1 | referer |
1 | url | /products/list
3 | duration_ms | 300
3 | referer | https://example.com
3 | url | /index
(6 rows)这份结果顺带验证了前面讲的 NULL 语义：第 1 行的 referer 值是 JSON null，jsonb_each_text 把它转成了 SQL NULL（显示为空白），但行本身仍然存在，不会丢行。
第七步：清理
DROP TABLE user_events;DROP TABLE注意事项
->与->>不要混用。链式下钻用->，取最终值用->>。写成doc -> 'a' ->> 'b'是合法的（先取 jsonb 再转 text），但反过来doc ->> 'a' -> 'b'会报错：sql-- 错误！左侧已经是 text，text 上没有 -> 操作符 SELECT '{"a": {"b": 1}}'::jsonb ->> 'a' -> 'b';textERROR: operator does not exist: text -> unknown LINE 1: SELECT '{"a": {"b": 1}}'::jsonb ->> 'a' -> 'b'; ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.提取操作符不会报错，只会返回 NULL。这既是优点也是陷阱：写错键名不会立刻暴露，只会静默少数据。上线前用
?或jsonb_path_exists做一次数据体检。JSON null 与缺失键靠
->>无法区分。二者都返回 SQL NULL。要区分必须用?或jsonb_typeof。jsonb_each/jsonb_array_elements类型不符会直接报错，与提取操作符的宽容行为相反。展开前先jsonb_typeof判断，或在表上加固检查约束。jsonb_each不保证输出顺序。jsonb内部按键排序，与写入顺序无关；需要稳定输出时显式ORDER BY key。jsonb_array_elements不提供行号。需要保留数组位置时用WITH ORDINALITY。左连接展开时不要写成
JOIN。空数组会让整行消失，要用LEFT JOIN LATERAL ... ON true：sqlSELECT e.id, item.value ->> 'sku' AS sku FROM user_events e LEFT JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item ON true;jsonb_path_match可能返回 NULL。路径结果不是单个布尔值时返回 NULL，会污染count()等聚合，优先用jsonb_path_exists。不要在
WHERE里对jsonb列做函数包装。WHERE jsonb_typeof(payload) = 'object'这类条件无法走索引，能写成@>或表达式索引的要尽量改写。?与?|在 psql 里可能需要转义。?是 JDBC 等驱动的参数占位符；在 psql 中如果开启某些客户端变量也可能有歧义，必要时写成OPERATOR(pg_catalog.?)形式。递归下降
.**的代价是 O(文档大小)。它要遍历整棵文档树，大文档上慎用于高频查询。jsonpath 表达式的
lax/strict前缀要明确。默认lax会自动跳过类型不匹配的路径，strict则报错；跨结构查询用lax，结构固定的场景可以用strict尽早暴露数据问题。
最佳实践
提取值一律用
->>，再显式转换类型。这让类型一目了然，也便于优化器推导：sql(payload ->> 'qty')::int (payload ->> 'price')::numeric(12, 2)WHERE条件优先写成@>包含判断，它可以直接使用 GIN 索引；存在性判断?只有直接作用于索引列时才走索引：sql-- 推荐（可走 GIN 索引） WHERE payload @> '{"tags": ["promo"]}' -- 次优（? 作用在 payload -> 'tags' 上，通常不走索引） WHERE payload -> 'tags' ? 'promo'高频单值条件抽成表达式索引。把
payload ->> 'order_no'这种固定查询模式注册为索引，查询时保持表达式完全一致：sqlCREATE INDEX idx_events_order_no ON user_events ((payload ->> 'order_no'));展开数组时始终带
LATERAL，并在需要保留主表行时用LEFT JOIN LATERAL ... ON true。用
jsonb_path_query_array一次取出多个值，比在应用层循环调用->快得多，也少一次网络往返。需要「先筛后取」时把条件下推到 jsonpath 的
?(...)里，而不是先取全部再在应用层过滤：sqlSELECT jsonb_path_query_array(payload, '$.items[*] ? (@.qty >= 2).sku') FROM user_events WHERE event_type = 'purchase';给 jsonpath 用外部变量而不是字符串拼接。变量通过
vars参数传入，能避免注入风险并让路径可缓存：sqljsonb_path_exists(payload, '$.items[*] ? (@.sku == $target)', jsonb_build_object('target', v_sku))用
jsonb_typeof做前置探查再决定分支。多结构共存的列上，先判类型可以避免展开函数报错，也让 SQL 意图更清晰：sqlWHERE jsonb_typeof(payload -> 'items') = 'array'统计口径里注意
->>产生的 SQL NULL。需要区分「键不存在」与「值为 null」时，把?的判断加进CASE：sqlCASE WHEN payload ? 'referer' THEN COALESCE(payload ->> 'referer', '(null)') ELSE '(absent)' END关键查询路径先用
EXPLAIN (ANALYZE, BUFFERS)验证，确认@>、@?这类条件确实走了 GIN 索引，而不是全表顺序扫描。
下一节
继续学习：JSONB 的更新与修改