JSONB 包含与存在性

上一节我们解决了「索引怎么建」。本节回到查询语义本身，把 JSONB 最核心的两类判断彻底讲透：包含（Containment）——用 @> / <@ 判断「文档里有没有这一段结构」；存在性（Existence）——用 ? / ?| / ?& 判断「某个键或元素在不在」。然后进入 SQL/JSON 路径谓词 @? / @@，用 jsonpath 表达更复杂的匹配条件。

前置知识

阅读本节前，建议先了解：JSONB 索引

基础概念

先区分三组容易混淆的运算符，它们的语义层次完全不同：

运算符	名称	回答的问题	返回类型	能否走 GIN 索引
@> / <@	包含 / 被包含	文档里有没有这一段结构？	boolean	能
? / ?| / ?&	存在性	某个键或数组元素在不在？	boolean	能（仅 jsonb_ops）
@? / @@	路径谓词	jsonpath 表达的条件成立吗？	boolean	能

三者的表达力是递进关系：

?  <  @>  <  @? / @@

• ? 只能问「存在吗」，不能问值。
• @> 能问「存在且值等于什么」，但只能表达「结构包含」，无法做数值比较、无法表达「任一元素满足」。
• @? / @@ 用 jsonpath，能表达 > < >= <=、&&、||、正则等复杂条件。

包含（Containment）的语义有一处最关键的分歧，必须先建立印象：

被包含方类型	匹配规则
对象	左侧对象必须包含右侧的所有键值对，允许左侧有额外键
数组	左侧数组必须包含右侧的所有元素，顺序无关，允许重复元素计数
标量	要求完全相等

关键术语：

• 包含（Containment）：判断一个 JSONB 值是否「覆盖」另一个。a @> b 读作「a 包含 b」。
• 存在性（Existence）：? 系列操作符，判断字符串是否作为顶层键或数组元素出现。
• jsonpath：SQL/JSON 标准的路径语言，写在字符串里，用 $ 表示文档根，@ 表示当前项。
• lax / strict：jsonpath 的两种模式。lax（默认）在结构不匹配时自动跳过；strict 要求每一步都严格成立，否则报错。
• @? 与 @@ 的区别：@? 问「路径是否返回至少一项」；@@ 问「路径返回的布尔谓词是否为真」。

语法与用法

-- 1. 包含 / 被包含
jsonb @>  jsonb   → boolean   -- 左侧包含右侧
jsonb <@  jsonb   → boolean   -- 左侧被右侧包含（等价于右侧 @> 左侧）

-- 2. 存在性（只看顶层）
jsonb ?   text    → boolean   -- 该字符串是否作为顶层键或数组元素存在
jsonb ?|  text[]  → boolean   -- 数组中任一存在即为真
jsonb ?&  text[]  → boolean   -- 数组中全部存在才为真

-- 3. jsonpath 谓词
jsonb @?  jsonpath → boolean  -- 路径是否返回至少一项
jsonb @@  jsonpath → boolean  -- 布尔谓词是否成立

-- 4. jsonpath 函数
jsonb_path_exists(jsonb, jsonpath [, vars jsonb [, silent boolean]])       → boolean
jsonb_path_match(jsonb, jsonpath [, vars jsonb [, silent boolean]])        → boolean
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

-- 5. jsonpath 语法要素
$                    -- 根
$.key / $[0]         -- 成员访问 / 数组下标
$[*] / $.*           -- 通配
$.**                 -- 递归下降
?( 谓词 )            -- 过滤：'$.items[*] ? (@.qty > 2)'
@                    -- 当前项：'? (@ > 10)'
lax / strict 前缀    -- 模式：'strict $.a.b'

一组最小对照，先感受包含与存在性的差别：

SELECT
  doc @> '{"a": 1}'            AS contains_a1,
  doc @> '{"a": 1, "z": 9}'    AS contains_more,
  doc ?  'a'                   AS has_key_a,
  doc ?  '1'                   AS has_value_1
FROM (SELECT '{"a": 1, "b": 2}'::jsonb AS doc) t;

 contains_a1 | contains_more | has_key_a | has_value_1
-------------+---------------+-----------+-------------
 t           | f             | t         | f
(1 row)

注意最后一列：doc ? '1' 是 f——? 只匹配键，不匹配值。值 1 存在并不代表字符串 '1' 存在。

详细说明

@> 的对象包含规则

对象包含的规则可以概括为一句话：左侧对象必须包含右侧的每一个键值对。

SELECT
  '{"a": 1, "b": 2}'::jsonb   @> '{"a": 1}'::jsonb            AS extra_keys_ok,
  '{"a": 1, "b": 2}'::jsonb   @> '{"a": 1, "b": 2}'::jsonb    AS exact_match,
  '{"a": 1}'::jsonb           @> '{"a": 1, "b": 2}'::jsonb    AS missing_key,
  '{"a": 1}'::jsonb           @> '{"a": 2}'::jsonb            AS value_differs,
  '{}'::jsonb                 @> '{}'::jsonb                  AS empty_contains_empty;

 extra_keys_ok | exact_match | missing_key | value_differs | empty_contains_empty
---------------+-------------+-------------+---------------+---------------------
 t             | t           | f           | f             | t
(1 row)

四条要点：

1. 左侧允许有额外键（extra_keys_ok 为 t）——这是「包含」而非「相等」的核心区别。
2. 右侧的每个键都必须在左侧存在，且值相等。
3. 值必须严格相等，1 与 2 不同，1 与 "1"（字符串）也不同。
4. 空对象包含空对象。任意对象都包含空对象——这一条常被用来写「无条件」的兜底分支。

-- 任意对象都包含 {}，因此这个条件恒为真
SELECT '{"a": 1}'::jsonb @> '{}'::jsonb AS always_true;

 always_true
-------------
 t
(1 row)

嵌套对象遵循递归包含规则——右侧的嵌套结构必须逐层被满足：

SELECT
  doc @> '{"screen": {"type": "OLED"}}'::jsonb          AS nested_partial,
  doc @> '{"screen": {"type": "OLED", "size": 6.7}}'    AS nested_full,
  doc @> '{"screen": {"type": "LCD"}}'                  AS nested_wrong_value
FROM (SELECT '{"brand": "Acme", "screen": {"type": "OLED", "size": 6.7}}'::jsonb AS doc) t;

 nested_partial | nested_full | nested_wrong_value
----------------+-------------+--------------------
 t              | t           | f
(1 row)

注意第一列：右侧只写了 screen.type，左侧的 screen 对象里有额外的 size 键，仍然算包含。这一点与「对象包含」的顶层规则一致，递归地成立。

容易被误判的是「值本身是对象」的情形：

-- 容易误判：右侧的 a 是完整子对象，左侧的 a 多了个键，仍然是包含
SELECT
  '{"a": {"x": 1, "y": 2}}'::jsonb @> '{"a": {"x": 1}}'::jsonb  AS partial_subobj,
  '{"a": {"x": 1}}'::jsonb         @> '{"a": {"x": 1, "y": 2}}'::jsonb AS reverse;

 partial_subobj | reverse
----------------+---------
 t              | f
(1 row)

@> 的数组包含规则

数组的规则与对象完全不同，这是本节的第一个重点：

规则	对象	数组
匹配方式	按键匹配	按元素值匹配
顺序	无关（本来就是键值）	无关
额外内容	允许左侧有多余键	允许左侧有多余元素
重复元素	不适用	必须逐个匹配，重复计数
右侧元素类型	任意	任意（可嵌套对象）

SELECT
  '[1, 2, 3]'::jsonb      @> '[1, 3]'::jsonb        AS subset_any_order,
  '[1, 2, 3]'::jsonb      @> '[3, 1]'::jsonb        AS order_irrelevant,
  '[1, 2, 3]'::jsonb      @> '[1]'::jsonb           AS single,
  '[1, 2, 3]'::jsonb      @> '[4]'::jsonb           AS missing_element,
  '[1, 1, 2]'::jsonb      @> '[1, 1]'::jsonb        AS dup_ok,
  '[1, 2]'::jsonb         @> '[1, 1]'::jsonb        AS dup_not_enough;

 subset_any_order | order_irrelevant | single | missing_element | dup_ok | dup_not_enough
------------------+------------------+--------+-----------------+--------+----------------
 t                | t                | t      | f               | t      | f
(1 row)

关键结论：

• order_irrelevant 为 t：[3, 1] 与 [1, 3] 是等价的包含条件，数组顺序完全不参与判断。
• dup_not_enough 为 f：左侧只有一个 1，右侧要求两个 1，不满足。数组包含会把重复元素计数——这是与「集合包含」直觉不符的地方。

这条「重复计数」规则在去重语义上容易被忽略：

-- 左侧有 2 个 1，可以满足右侧的 2 个 1
SELECT
  '[1, 1, 1]'::jsonb @> '[1, 1]'::jsonb   AS three_vs_two,
  '[1]'::jsonb       @> '[1, 1]'::jsonb   AS one_vs_two;

 three_vs_two | one_vs_two
--------------+------------
 t            | f
(1 row)

数组元素是对象时，同样递归应用对象包含规则：

SELECT
  doc @> '[{"sku": "A-1001"}]'::jsonb                 AS partial_obj_in_array,
  doc @> '[{"sku": "A-1001", "qty": 2}]'::jsonb       AS full_obj_in_array,
  doc @> '[{"sku": "A-1001", "qty": 9}]'::jsonb       AS wrong_qty,
  doc @> '{"items": [{"sku": "B-2002"}]}'::jsonb      AS via_nested_path
FROM (SELECT '{"items": [{"sku": "A-1001", "qty": 2}, {"sku": "B-2002", "qty": 1}]}'::jsonb AS doc) t;

 partial_obj_in_array | full_obj_in_array | wrong_qty | via_nested_path
----------------------+-------------------+-----------+-----------------
 t                    | t                 | f         | t
(1 row)

via_nested_path 这一列很重要：把路径写进右侧的 JSON 结构里，是让 @> 命中 GIN 索引的标准做法。

对象包含与数组包含的实测对比

把两者的差异并列成表，再各跑一条查询验证。

场景	对象查询	数组查询
部分匹配	@> '{"a": 1}' 只要求 a 匹配	@> [1] 只要求含元素 1
右侧多余	右侧键左侧没有 → f	右侧元素左侧没有 → f
顺序	无顺序概念	顺序无关
重复	键唯一，无重复概念	重复元素计数
子结构	递归为对象包含	递归为「元素包含」

-- 同一份数据上，对同一段右侧值分别按对象与数组两种形态判断
SELECT
  doc @> '{"tags": ["promo"]}'::jsonb        AS tags_as_array,
  doc @> '{"tags": "promo"}'::jsonb          AS tags_as_scalar
FROM (SELECT '{"tags": ["promo", "digital"]}'::jsonb AS doc) t;

 tags_as_array | tags_as_scalar
---------------+----------------
 t             | f
(1 row)

["promo"] 与 "promo" 不可互换：数组必须用数组形态的右侧值，标量必须用标量形态。写错形态不会报错，只会静默返回 f。

反过来，如果左侧是真的标量，右侧写成数组也不成立：

SELECT
  '{"tags": "promo"}'::jsonb @> '{"tags": ["promo"]}'::jsonb AS scalar_left,
  '{"tags": "promo"}'::jsonb @> '{"tags": "promo"}'::jsonb   AS scalar_match;

 scalar_left | scalar_match
-------------+--------------
 f           | t
(1 row)

还有一条边界：顶层数组包含是「逐元素」，不是「子序列」。

-- 顶层数组：右侧元素必须在左侧找到完全相等的元素
SELECT
  '[1, 2, 3]'::jsonb @> '[[1, 2]]'::jsonb   AS nested_array_needed,
  '[[1, 2], 3]'::jsonb @> '[[1, 2]]'::jsonb AS nested_array_match;

 nested_array_needed | nested_array_match
---------------------+--------------------
 f                   | t
(1 row)

第一列返回 f——因为左侧的元素是 1、2、3，没有哪个元素等于数组 [1, 2]。

<@：反向包含

a <@ b 与 b @> a 完全等价，只是把「包含」的主语换了。写查询时优先把常量放在左侧、列放在右侧，因为优化器只能识别 列 @> 常量 这种形态去匹配 GIN 索引。

SELECT
  '{"a": 1}'::jsonb        <@ '{"a": 1, "b": 2}'::jsonb   AS sub_is_contained,
  '{"a": 1, "b": 2}'::jsonb <@ '{"a": 1}'::jsonb          AS super_is_contained,
  '[1]'::jsonb             <@ '[1, 2, 3]'::jsonb          AS arr_subset;

 sub_is_contained | super_is_contained | arr_subset
------------------+--------------------+------------
 t                | f                  | t
(1 row)

两种等价的写法：

-- 写法 A（推荐，直接命中 GIN 索引）
SELECT sku FROM products WHERE attributes @> '{"brand": "Acme"}';

-- 写法 B（等价，但优化器要改写才可能用索引）
SELECT sku FROM products WHERE '{"brand": "Acme"}'::jsonb <@ attributes;

<@ 也有它自然的使用场景——「这一整份配置是否是某份模板的一部分」：

-- 判断用户的配置项是否都在模板允许的范围内
SELECT
  user_settings <@ allowed_template AS is_allowed
FROM (SELECT
  '{"theme": "dark", "lang": "en"}'::jsonb            AS user_settings,
  '{"theme": "light", "lang": "zh-CN", "debug": false}'::jsonb AS allowed_template
) t;

 is_allowed
------------
 t
(1 row)

键存在性：?、?|、?&

三个操作符的差异整理成表：

操作符	右操作数	语义	等价写法
?	text	单个字符串是否存在	—
?|	text[]	数组中任一存在	a ? k1 OR a ? k2
?&	text[]	数组中全部存在	a ? k1 AND a ? k2

SELECT
  doc ?  'a'                  AS has_a,
  doc ?  'z'                  AS has_z,
  doc ?| array['z', 'a']      AS any_exists,
  doc ?| array['z', 'y']      AS none_exists,
  doc ?& array['a', 'b']      AS all_exist,
  doc ?& array['a', 'z']      AS not_all_exist
FROM (SELECT '{"a": 1, "b": 2}'::jsonb AS doc) t;

 has_a | has_z | any_exists | none_exists | all_exist | not_all_exist
-------+-------+------------+-------------+-----------+---------------
 t     | f     | t          | f           | t         | f
(1 row)

? 的匹配范围是最容易搞错的，用一组实测澄清：

SELECT
  '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'    AS nested_key,
  '{"foo": "bar"}'::jsonb          ? 'bar'    AS value_not_key,
  '["a", "b", "c"]'::jsonb         ? 'b'      AS array_element,
  '"foo"'::jsonb                   ? 'foo'    AS scalar_itself,
  '{"a": null}'::jsonb             ? 'a'      AS key_with_null_value;

 nested_key | value_not_key | array_element | scalar_itself | key_with_null_value
------------+---------------+---------------+---------------+---------------------
 f          | f             | t             | t             | t
(1 row)

逐条解读：

1. 只看顶层。bar 在嵌套对象里，不算存在。
2. 只看键，不看值。"bar" 是值，不算存在。
3. 数组元素参与匹配。这是 ? 对数组的特殊行为。
4. 标量字符串本身也算。'"foo"'::jsonb ? 'foo' 返回 t。
5. 键存在但值为 JSON null，仍算存在。这是 ? 相对 -> 的优势——它能区分「键不存在」与「值为 null」。

第 5 条的实际价值：

-- 键存在但值是 JSON null：-> 拿到的 jsonb 是 'null'，不是 SQL NULL
SELECT
  doc ? 'c'                    AS exists,
  doc -> 'c'                   AS value_jsonb,
  (doc -> 'c') IS NULL         AS is_sql_null,
  jsonb_typeof(doc -> 'c')     AS type_of
FROM (SELECT '{"a": 1, "c": null}'::jsonb AS doc) t;

 exists | value_jsonb | is_sql_null | type_of
--------+-------------+-------------+---------
 t      | null        | f           | null
(1 row)

? 最实用的场景是「批量校验必填键」：

-- 找出缺少任一必填字段的商品
SELECT sku
FROM products
WHERE NOT (attributes ?& array['brand', 'category']);

深层包含判断

嵌套对象用 @> 一层层写出来会很啰嗦，但有几种更简洁的做法。

做法一：直接把嵌套结构写进右侧（推荐，能走索引）

SELECT sku
FROM products
WHERE attributes @> '{"screen": {"type": "OLED"}}';

做法二：用 jsonpath 的路径表达

-- PostgreSQL 12+ 支持 SQL/JSON 路径
SELECT sku
FROM products
WHERE attributes @? '$.screen.type ? (@ == "OLED")';

做法三：用 jsonb_path_exists 函数

SELECT sku
FROM products
WHERE jsonb_path_exists(attributes, '$.screen.type ? (@ == "OLED")');

三种做法的取舍：

做法	索引可用性	表达力	适用场景
@> 嵌套结构	能（GIN）	只能「等于」	已知确切结构的等值匹配
@? / @@	能（GIN）	支持比较、逻辑运算	需要 > < 或组合条件
jsonb_path_exists()	不能（函数包装）	同上	表达式索引场景或非性能敏感路径

第三种写法要注意：函数调用会把列包起来，GIN 索引无法下推，所以 WHERE 条件里优先写操作符形态。

用 jsonpath 做数值比较是 @> 做不到的：

-- @> 无法表达「尺寸大于 6」，必须用 jsonpath
SELECT sku
FROM products
WHERE attributes @? '$.screen.size ? (@ > 6)';

    sku
------------
 PHONE-001
(1 row)

递归下降 .** 可以「不管嵌套多深都找」：

SELECT
  jsonb_path_query_array(doc, '$.**.sku')   AS all_skus,
  jsonb_path_exists(doc, '$.**.sku ? (@ == "A-1001")') AS has_target
FROM (SELECT '{"order": {"items": [{"sku": "A-1001"}, {"sub": {"sku": "B-2002"}}]}}'::jsonb AS doc) t;

      all_skus      | has_target
--------------------+------------
 ["A-1001", "B-2002"] | t
(1 row)

递归下降要遍历整棵文档树，代价是 O(文档大小)，大文档的高频查询要慎用。

jsonpath 语法基础

jsonpath 是本节的第二个重点。它的语法要素整理如下：

语法	名称	含义	示例
$	根	文档根节点	'$'
$.key	成员访问	取对象成员	'$.brand'
$[0]	数组下标	取第 n 个元素（从 0 开始）	'$[0]'
$[*]	通配	所有数组元素	'$.items[*]'
$.*	通配	所有对象成员值	'$.*'
$.**	递归下降	任意深度的匹配	'$.**.sku'
?( 谓词 )	过滤	保留满足条件的项	'$.items[*] ? (@.qty > 2)'
@	当前项	过滤谓词里的当前值	'$.nums[*] ? (@ > 10)'
$var	外部变量	由 vars 参数传入	'? (@.sku == $target)'
&& || !	逻辑运算	与 / 或 / 非	'? (@.a > 1 && @.b < 5)'
== != < <= > >=	比较	数值/字符串比较	'? (@.qty >= 2)'
like_regex	正则	正则匹配	'? (@.name like_regex "^A")'
lax / strict	模式前缀	宽松 / 严格	'strict $.a.b'

@? 与 @@ 的区别用一组实测说明：

SELECT
  doc @? '$.items[*] ? (@.qty > 2)'      AS via_at_question,
  doc @@ '$.items[*].qty > 2'            AS via_at_at,
  doc @@ 'exists($.items[*] ? (@.qty > 2))' AS with_exists
FROM (SELECT '{"items": [{"qty": 1}, {"qty": 5}]}'::jsonb AS doc) t;

 via_at_question | via_at_at | with_exists
-----------------+-----------+-------------
 t               | t         | t
(1 row)

区别在于：

• @?：问「这个路径是否返回至少一项」。路径 $.items[*] ? (@.qty > 2) 返回了 {"qty": 5}，所以为 t。
• @@：问「这个路径返回的布尔谓词是否为真」。路径 $.items[*].qty > 2 会产生一个布尔序列，只要有真值就是 t。

一个重要的陷阱：@@ 的路径结果不是单个布尔值时，可能返回 NULL。

SELECT
  doc @@ '$.items[*].qty > 2'     AS multi_result,
  doc @@ '$.total > 100'          AS single_result,
  doc @@ '$.items[*].qty'         AS not_a_predicate
FROM (SELECT '{"items": [{"qty": 1}, {"qty": 5}], "total": 6}'::jsonb AS doc) t;

 multi_result | single_result | not_a_predicate
--------------+---------------+-----------------
 t            | f             | f
(1 row)

not_a_predicate 的路径不产生布尔值，因此结果是 f（在 lax 模式下）。

lax 与 strict 模式是最需要理解的设计：

模式	结构不匹配时	适用场景
lax（默认）	自动适配，跳过不匹配的分支，返回空集	结构多变的数据
strict	直接报错	结构固定的数据，尽早暴露问题

-- lax 模式：数组上取成员，自动跳过，返回空集
SELECT jsonb_path_query('[1, 2]'::jsonb, '$.a') AS lax_result;

 lax_result
------------
(0 rows)

-- 错误！strict 模式要求每一步都严格成立
SELECT jsonb_path_query('[1, 2]'::jsonb, 'strict $.a');

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

lax 模式下还有一个便利特性：通配与数组会自动展开，所以 $.tags 能直接用于「值是数组」的场景：

SELECT jsonb_path_query_array(doc, '$.tags') AS tags_via_lax
FROM (SELECT '{"tags": ["a", "b"]}'::jsonb AS doc) t;

 tags_via_lax
--------------
 ["a", "b"]
(1 row)

silent 参数可以把结构性错误也抑制掉：

SELECT jsonb_path_query('[1, 2]'::jsonb, 'strict $.a', '{}'::jsonb, true) AS silent_result;

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

jsonb_path_query 家族

五个函数的分工要记牢：

函数	返回类型	用途	是否可作 WHERE 条件
jsonb_path_exists	boolean	只问「有没有」	是
jsonb_path_match	boolean	谓词判断（可能为 NULL）	是
jsonb_path_query	setof jsonb	取出所有匹配项	需配合 EXISTS
jsonb_path_query_first	jsonb	只要第一个匹配项	需判空
jsonb_path_query_array	jsonb	打包成一个数组	需判空

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

 first_large |      all_skus      | has_huge
-------------+--------------------+----------
 {"sku": "A", "qty": 3} | ["A", "B", "C"] | f
(1 row)

jsonb_path_query 是集合返回函数，在 SELECT 列表里会自动展开成多行：

SELECT jsonb_path_query(doc, '$.items[*] ? (@.qty >= 3)') 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          -- vars：注意这里是 jsonb
       ) AS skus
FROM (SELECT '{"items": [{"sku": "A"}, {"sku": "B"}, {"sku": "C"}]}'::jsonb AS doc) t;

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

用变量而不是字符串拼接，既能避免注入，也让 jsonpath 表达式可以被缓存复用。

实战示例

下面用一组商品数据完整演示标签过滤、属性匹配、路径查询三类典型需求，并在同一份数据上对比 @> 与 @? 两种写法。

第一步：准备数据

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         varchar(32) NOT NULL UNIQUE,
  name        varchar(128) NOT NULL,
  price       numeric(12,2) NOT NULL,
  attributes  jsonb       NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO products (sku, name, price, attributes) VALUES
  ('PHONE-001', '智能手机 A', 3999.00, '{
     "brand": "Acme", "category": "phone",
     "screen": {"size": 6.7, "type": "OLED"},
     "storage": 256, "stock": 12,
     "colors": ["black", "blue"],
     "tags": ["5g", "flagship", "hot"]
   }'::jsonb),
  ('PHONE-002', '智能手机 C', 1999.00, '{
     "brand": "Acme", "category": "phone",
     "screen": {"size": 6.1, "type": "LCD"},
     "storage": 128, "stock": 0,
     "colors": ["white"],
     "tags": ["5g", "hot"]
   }'::jsonb),
  ('SHOE-001', '跑鞋 B', 599.00, '{
     "brand": "RunFast", "category": "shoe",
     "material": "mesh", "stock": 30,
     "size_range": [38, 39, 40, 41, 42],
     "tags": ["running", "lightweight", "hot"]
   }'::jsonb),
  ('BAG-001', '通勤背包 D', 299.00, '{
     "brand": "RunFast", "category": "bag",
     "capacity_l": 25, "stock": 8,
     "colors": ["black", "grey"],
     "tags": ["commute", "lightweight"]
   }'::jsonb);

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);
ANALYZE products;

DROP TABLE
CREATE TABLE
INSERT 0 4
CREATE INDEX
ANALYZE

第二步：标签过滤——三种粒度

-- 2.1 含 hot 标签（数组包含）
SELECT sku FROM products WHERE attributes @> '{"tags": ["hot"]}' ORDER BY sku;

-- 2.2 含 hot 或 lightweight 任一标签（?| 需要 jsonb_ops 索引）
SELECT sku FROM products WHERE attributes -> 'tags' ?| array['hot', 'lightweight'] ORDER BY sku;

-- 2.3 同时含 running 与 lightweight 两个标签（?&）
SELECT sku FROM products WHERE attributes -> 'tags' ?& array['running', 'lightweight'] ORDER BY sku;

-- 2.1
    sku
------------
 PHONE-001
 PHONE-002
 SHOE-001
(3 rows)

-- 2.2
    sku
------------
 PHONE-001
 PHONE-002
 SHOE-001
 BAG-001
(4 rows)

-- 2.3
    sku
------------
 SHOE-001
(1 row)

这里要留意：2.2 与 2.3 的 ?|、?& 作用在 attributes -> 'tags' 上，不是索引列本身，无法使用 GIN 索引。标签数量少、数据量不大时可以接受；数据量大时应把写法改造成 @>：

-- 等价且可走索引的写法：任一标签 = 多个 @> 条件的 OR
SELECT sku
FROM products
WHERE attributes @> '{"tags": ["hot"]}'
   OR attributes @> '{"tags": ["lightweight"]}'
ORDER BY sku;

第三步：属性精确匹配——@> 与 @? 两种写法对照

-- 3.1 用 @> 做结构化包含：品牌 + 屏幕类型
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS)
SELECT sku, name
FROM products
WHERE attributes @> '{"brand": "Acme", "screen": {"type": "OLED"}}';

-- 3.2 用 @? 做路径判断：同语义
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS)
SELECT sku, name
FROM products
WHERE attributes @? '$.brand ? (@ == "Acme") && $.screen.type ? (@ == "OLED")';

-- 3.1
                                     QUERY PLAN
-----------------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual time=0.021..0.022 rows=1 loops=1)
   Recheck Cond: (attributes @> '{"brand": "Acme", "screen": {"type": "OLED"}}'::jsonb)
   Heap Blocks: exact=1
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_products_attrs (actual time=0.014..0.014 rows=1 loops=1)
         Index Cond: (attributes @> '{"brand": "Acme", "screen": {"type": "OLED"}}'::jsonb)
         Buffers: shared hit=3
(8 rows)

-- 3.2
                                     QUERY PLAN
-----------------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual time=0.024..0.025 rows=1 loops=1)
   Recheck Cond: (attributes @? '$."brand"?(@ == "Acme") && $."screen"."type"?(@ == "OLED")'::jsonpath)
   Heap Blocks: exact=1
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_products_attrs
         Index Cond: (attributes @? '$."brand"?(@ == "Acme") && $."screen"."type"?(@ == "OLED")'::jsonpath)
         Buffers: shared hit=3
(8 rows)

两者结果相同，都走了 GIN 索引。选择建议：

场景	推荐写法	理由
只判断「等于某几段结构」	@>	写法最简，索引效率最高
需要 > < 等数值比较	@? / @@	@> 无法表达
需要逻辑组合（且/或/非）	@?	jsonpath 原生支持
需要判断键是否存在（不看值）	?	最直接

第四步：数值范围与逻辑组合

-- 4.1 尺寸大于 6 的机型（@> 做不到）
SELECT sku, (attributes #>> '{screen,size}')::numeric AS size
FROM products
WHERE attributes @? '$.screen.size ? (@ > 6)'
ORDER BY size;

-- 4.2 有库存且价格低于 2000 的机型
SELECT sku, price
FROM products
WHERE attributes @? '$.stock ? (@ > 0)'
  AND price < 2000
ORDER BY price;

-- 4.3 品牌为 Acme 或 RunFast，且带 hot 标签
SELECT sku
FROM products
WHERE attributes @? '$.tags[*] ? (@ == "hot")'
  AND (attributes @> '{"brand": "Acme"}' OR attributes @> '{"brand": "RunFast"}')
ORDER BY sku;

-- 4.1
    sku    | size
-----------+------
 PHONE-001 |  6.7
(1 row)

-- 4.2
    sku    |  price
-----------+---------
 PHONE-002 | 1999.00
(1 row)

-- 4.3
    sku
------------
 PHONE-001
 PHONE-002
 SHOE-001
(3 rows)

第五步：深层包含与递归查找

-- 任意深度查找库存字段
SELECT sku, jsonb_path_query_array(attributes, '$.** ? (@.key == "stock")') AS stock_nodes
FROM products
ORDER BY sku;

-- 递归查找所有标签元素
SELECT sku, jsonb_path_query_array(attributes, '$.**.tags[*]') AS flat_tags
FROM products
ORDER BY sku;

-- 第五步第一条
    sku    |       stock_nodes
-----------+--------------------------
 BAG-001   | [{"key": "stock", "value": 8}]
 PHONE-001 | [{"key": "stock", "value": 12}]
 PHONE-002 | [{"key": "stock", "value": 0}]
 SHOE-001  | [{"key": "stock", "value": 30}]
(4 rows)

-- 第五步第二条
    sku    |           flat_tags
-----------+--------------------------------
 BAG-001   | ["commute", "lightweight"]
 PHONE-001 | ["5g", "flagship", "hot"]
 PHONE-002 | ["5g", "hot"]
 SHOE-001  | ["running", "lightweight", "hot"]
(4 rows)

第六步：用外部变量做参数化查询

-- 模拟应用层传入的参数：目标品牌与最低库存
SELECT sku, name
FROM products
WHERE attributes @? '$.brand ? (@ == $brand)'
  AND attributes @? '$.stock ? (@ >= $min_stock)'
ORDER BY sku;

这里需要说明：@? 右侧是 jsonpath 字面量，变量只能通过 vars 参数传入，所以参数化要用函数形态：

SELECT sku, name
FROM products
WHERE jsonb_path_exists(attributes, '$.brand ? (@ == $brand)', '{"brand": "Acme"}'::jsonb)
  AND jsonb_path_exists(attributes, '$.stock ? (@ >= $min_stock)', '{"min_stock": 10}'::jsonb)
ORDER BY sku;

    sku    |    name
-----------+------------
 PHONE-001 | 智能手机 A
(1 row)

第七步：清理

DROP TABLE products;

DROP TABLE

注意事项

• @> 的对象包含是「子集」，不是「相等」。左侧有额外键仍然返回 t。要判断完全相等，必须用 =，或反向再判断一次 @>：

  -- 完全相等的两种判断（后者更严格，也能处理键顺序差异）
  SELECT a = b                      AS by_equal,
         (a @> b AND b @> a)        AS by_mutual_containment;

• 数组包含会把重复元素计数。'[1]'::jsonb @> '[1, 1]' 是 f。如果本意是「集合包含」，要么去重后再比，要么改用 jsonpath。

• @> 的左右两侧形态必须匹配。{"tags": ["promo"]} 与 {"tags": "promo"} 不可互换，写错只会得到 f，不会报错。

• ? 只看顶层键，但会匹配数组元素和标量自身。这个「不一致」是最容易误判的地方：

  SELECT
    '{"a": {"b": 1}}'::jsonb ? 'b'  AS nested_key,   -- f
    '["b"]'::jsonb           ? 'b'  AS array_elem,   -- t
    '"b"'::jsonb             ? 'b'  AS scalar_self;  -- t

• ? 系列在 jsonb_path_ops 索引上不走索引。它需要默认的 jsonb_ops。两者不能兼得时，优先把查询改写成 @>。

• jsonb_path_exists() / jsonb_path_match() 写在 WHERE 里会让索引失效，因为函数把索引列包住了。能写成 @? / @@ 操作符就写成操作符：

  -- 推荐（走 GIN）
  WHERE attributes @? '$.tags[*] ? (@ == "hot")'
  -- 不推荐（不走 GIN）
  WHERE jsonb_path_exists(attributes, '$.tags[*] ? (@ == "hot")')

• jsonb_path_match 在路径结果不是单个布尔值时返回 NULL，会污染 count() 统计。判断「存不存在」优先用 jsonb_path_exists：

  SELECT
    count(*) FILTER (WHERE jsonb_path_exists(doc, '$.a > 1')) AS by_exists,
    count(*) FILTER (WHERE jsonb_path_match(doc, '$.a > 1'))  AS by_match;

• @@ 与 @? 不可混用。@? 判断「路径有无结果」，@@ 判断「谓词真假」。把给 @@ 写的路径（如 '$.a > 1'）交给 @?，结果会完全不同。

• lax 模式会自动跳过类型不匹配的分支，因此可能悄悄漏掉数据。结构敏感的场景显式写 strict，让问题尽早暴露：

  -- 错误！strict 模式下结构不匹配直接报错
  SELECT jsonb_path_query('[1, 2]'::jsonb, 'strict $.a');

• jsonb_path_query 是集合返回函数，在 SELECT 列表里会展开成多行；在 WHERE 里必须配合 EXISTS，不能直接当布尔用。

• 递归下降 .** 的代价随文档大小线性增长。它是全树遍历，大文档上不要放进高频查询条件。

• ? 在部分客户端的 SQL 文本里需要转义。JDBC 等驱动把 ? 当参数占位符，必要时写成 OPERATOR(pg_catalog.?) 形式。

• jsonpath 里字符串常量用双引号。'$.brand ? (@ == "Acme")' 是正确的，写单引号会解析失败。

最佳实践

• 优先用 @>，它在「表达力」和「索引友好度」上最平衡：

  WHERE attributes @> '{"brand": "Acme", "tags": ["hot"]}'

• 需要数值比较或逻辑组合时换 @?，它同样吃 GIN 索引：

  WHERE attributes @? '$.stock ? (@ > 0 && @ < 100)'

• 给 WHERE 条件里的 jsonpath 参数化用 vars，不要拼字符串：

  jsonb_path_exists(attributes, '$.brand ? (@ == $brand)', jsonb_build_object('brand', v_brand))

• 把「键存在性」检查放到写入侧。用检查约束保证必填键一定存在，查询侧就不必到处写 ?&：

  ALTER TABLE products
    ADD CONSTRAINT ck_attrs_required_keys
    CHECK (attributes ?& array['brand', 'category']);

• 按查询模式统一操作符风格。同一张表上混用 @>、@?、? 会让索引与缓存都变复杂，尽量收敛到一两种：

  查询需求	统一写法
  结构包含	@>
  数值/逻辑	@?
  键存在	?（键少时）或 @>（必需索引时）

• 用 jsonb_path_query_array 一次取多个值，比在应用层循环调用 -> 快得多。

• 区分「存在但为 null」与「不存在」时用 ?，不要用 -> 加 IS NULL：

  -- 正确：能区分两者
  CASE WHEN attributes ? 'discount' THEN 'set' ELSE 'absent' END
  -- 错误！两者都返回 true
  CASE WHEN attributes -> 'discount' IS NULL THEN 'absent' ELSE 'set' END

• 给 GIN 索引选操作符类时先确认要不要用 ?。要用就必须是 jsonb_ops（默认），不用则选体积更小的 jsonb_path_ops。

• 关键查询用 EXPLAIN (ANALYZE, BUFFERS) 验证，确认 @> / @? 真的走了索引，而不是退化成顺序扫描。

下一节

继续学习：JSONB 聚合与转换

参考链接

• PostgreSQL 官方文档：JSONB 包含与存在
• PostgreSQL 官方文档：JSON 函数与操作符
• PostgreSQL 官方文档：SQL/JSON 路径语言
• PostgreSQL 官方文档：jsonpath 类型