PostgreSQL / 系统课时

使用包含与存在查询

使用包含与存在查询

JSONB 不必先把每个字段提取成文本再比较。PostgreSQL 提供包含、被包含和存在操作符,可以直接按 JSON 结构表达过滤条件。本课通过一组可执行查询掌握对象与数组的匹配规则,并辨清“包含某个值”和“存在某个键”的区别。

学习目标

  • 使用 @> 与 <@ 判断 JSONB 结构的包含关系
  • 使用 ?、?|、?& 判断顶层键或数组字符串元素是否存在
  • 区分对象包含、数组包含与键存在的匹配语义

准备一组查询数据

下面建立一张商品表。attrs 同时包含对象字段和数组字段,便于观察不同操作符的行为。

sql
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;

预期结果

text
 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 对象作为条件。

sql
SELECT id
FROM products
WHERE attrs @> '{"brand":"Acme"}'::jsonb
ORDER BY id;

预期结果

text
 id
----
  1
  2

右侧不必列出完整对象。只要要求的键值结构能够在左侧找到,就算包含。多个键则同时参与判断,例如 {"brand":"Acme","color":"black"} 只匹配同时满足两个条件的对象。

包含也适用于数组。下面要求 tags 数组中包含字符串 sale:

sql
SELECT id
FROM products
WHERE attrs @> '{"tags":["sale"]}'::jsonb
ORDER BY id;

预期结果

text
 id
----
  1
  3

对于 JSONB 数组,@> 判断的是包含关系,而不是数组整体完全相等。因此 ['sale','new'] 可以包含 ['sale'],数组元素顺序也不是这种包含判断的重点。

<@ 是反向写法:左值 <@ 右值 表示左侧是否被右侧包含。它在“拿一个候选 JSON 判断是否属于某个较大结构”时比较直观,但业务表查询中通常更常见 列 @> 条件。

sql
SELECT '{"brand":"Acme"}'::jsonb <@ attrs AS matched
FROM products
WHERE id = 1;

预期结果

text
 matched
---------
 t

判断键或数组成员是否存在

存在操作符关注的是顶层成员名称或顶层字符串元素,并不等同于任意深度搜索。? 判断一个字符串是否作为顶层对象键存在,或作为顶层数组字符串元素存在。

sql
SELECT id
FROM products
WHERE attrs ? 'color'
ORDER BY id;

预期结果

text
 id
----
  1
  2
  3

这里检查的是 attrs 顶层是否有 color 键,而不是颜色值是什么。如果要检查嵌套的 tags 数组是否含 sale,应先定位到该数组再使用 ?:

sql
SELECT id
FROM products
WHERE attrs->'tags' ? 'sale'
ORDER BY id;

预期结果

text
 id
----
  1
  3

?| 表示给定字符串中至少一个存在,?& 表示全部存在。它们右侧接收 PostgreSQL 的 text[],不是 JSON 数组。

sql
SELECT
    attrs ?| ARRAY['brand', 'price'] AS any_exists,
    attrs ?& ARRAY['brand', 'color'] AS all_exist
FROM products
WHERE id = 1;

预期结果

text
 any_exists | all_exist
------------+-----------
 t          | t

选择正确的操作符

当条件要求“某个键具有某个 JSON 值”或“数组包含指定成员”时,优先考虑 @>;当问题只是“这个顶层键有没有”或“这个字符串是不是顶层数组元素”时,使用存在操作符更直接。

另外,JSONB 包含比较保留类型语义:JSON 字符串 "1" 与数字 1 不是同一个值。构造右侧条件时,应让字符串、数字、布尔值和 null 的类型与数据本身一致。

这些结构化操作符的价值不只是语句更简洁,它们也是后续为 JSONB 包含和存在条件设计 GIN 索引时最重要的一组查询表达式。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

使用包含与存在查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通