PostgreSQL / 系统课时
使用包含与存在查询
使用包含与存在查询
JSONB 不必先把每个字段提取成文本再比较。PostgreSQL 提供包含、被包含和存在操作符,可以直接按 JSON 结构表达过滤条件。本课通过一组可执行查询掌握对象与数组的匹配规则,并辨清“包含某个值”和“存在某个键”的区别。
学习目标
- 使用
@>与<@判断 JSONB 结构的包含关系 - 使用
?、?|、?&判断顶层键或数组字符串元素是否存在 - 区分对象包含、数组包含与键存在的匹配语义
准备一组查询数据
下面建立一张商品表。attrs 同时包含对象字段和数组字段,便于观察不同操作符的行为。
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id bigint PRIMARY KEY,
attrs jsonb NOT NULL
);
INSERT INTO products (id, attrs) VALUES
(1, '{"brand":"Acme","color":"black","tags":["sale","new"]}'),
(2, '{"brand":"Acme","color":"white","tags":["featured"]}'),
(3, '{"brand":"Nova","color":"black","tags":["sale"]}');
SELECT id, attrs FROM products ORDER BY id;预期结果
id | attrs
----+------------------------------------------------------------------
1 | {"tags": ["sale", "new"], "brand": "Acme", "color": "black"}
2 | {"tags": ["featured"], "brand": "Acme", "color": "white"}
3 | {"tags": ["sale"], "brand": "Nova", "color": "black"}JSONB 对象的键顺序不具备业务语义,因此实际显示顺序可能与插入文本不同;这不会影响后续匹配。
用包含关系表达结构条件
左值 @> 右值 表示左侧 JSONB 是否包含右侧描述的结构。查询品牌为 Acme 的记录时,可以直接提供一个较小的 JSON 对象作为条件。
SELECT id
FROM products
WHERE attrs @> '{"brand":"Acme"}'::jsonb
ORDER BY id;预期结果
id
----
1
2右侧不必列出完整对象。只要要求的键值结构能够在左侧找到,就算包含。多个键则同时参与判断,例如 {"brand":"Acme","color":"black"} 只匹配同时满足两个条件的对象。
包含也适用于数组。下面要求 tags 数组中包含字符串 sale:
SELECT id
FROM products
WHERE attrs @> '{"tags":["sale"]}'::jsonb
ORDER BY id;预期结果
id
----
1
3对于 JSONB 数组,@> 判断的是包含关系,而不是数组整体完全相等。因此 ['sale','new'] 可以包含 ['sale'],数组元素顺序也不是这种包含判断的重点。
<@ 是反向写法:左值 <@ 右值 表示左侧是否被右侧包含。它在“拿一个候选 JSON 判断是否属于某个较大结构”时比较直观,但业务表查询中通常更常见 列 @> 条件。
SELECT '{"brand":"Acme"}'::jsonb <@ attrs AS matched
FROM products
WHERE id = 1;预期结果
matched
---------
t判断键或数组成员是否存在
存在操作符关注的是顶层成员名称或顶层字符串元素,并不等同于任意深度搜索。? 判断一个字符串是否作为顶层对象键存在,或作为顶层数组字符串元素存在。
SELECT id
FROM products
WHERE attrs ? 'color'
ORDER BY id;预期结果
id
----
1
2
3这里检查的是 attrs 顶层是否有 color 键,而不是颜色值是什么。如果要检查嵌套的 tags 数组是否含 sale,应先定位到该数组再使用 ?:
SELECT id
FROM products
WHERE attrs->'tags' ? 'sale'
ORDER BY id;预期结果
id
----
1
3?| 表示给定字符串中至少一个存在,?& 表示全部存在。它们右侧接收 PostgreSQL 的 text[],不是 JSON 数组。
SELECT
attrs ?| ARRAY['brand', 'price'] AS any_exists,
attrs ?& ARRAY['brand', 'color'] AS all_exist
FROM products
WHERE id = 1;预期结果
any_exists | all_exist
------------+-----------
t | t选择正确的操作符
当条件要求“某个键具有某个 JSON 值”或“数组包含指定成员”时,优先考虑 @>;当问题只是“这个顶层键有没有”或“这个字符串是不是顶层数组元素”时,使用存在操作符更直接。
另外,JSONB 包含比较保留类型语义:JSON 字符串 "1" 与数字 1 不是同一个值。构造右侧条件时,应让字符串、数字、布尔值和 null 的类型与数据本身一致。
这些结构化操作符的价值不只是语句更简洁,它们也是后续为 JSONB 包含和存在条件设计 GIN 索引时最重要的一组查询表达式。