PostgreSQL / 系统课时
查询 JSONB 数组
查询 JSONB 数组
JSONB 数组查询的关键不是先展开,而是先判断问题属于成员存在、结构包含,还是元素级计算。下面用同一张订单表同时演示标量数组和对象数组,并说明直接过滤、关系化展开与 EXISTS 的选择边界。
学习目标
- 使用
?与@>判断 JSONB 数组的成员存在和结构包含 - 使用
jsonb_array_elements_text与jsonb_array_elements展开数组 - 根据查询目标在直接过滤、聚合展开和
EXISTS之间做选择
准备示例数据
用 labels 保存字符串数组,用 items 保存对象数组。后续查询都基于这组数据,避免为不同操作重复创建表。
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id bigint PRIMARY KEY,
labels jsonb NOT NULL,
items jsonb NOT NULL
);
INSERT INTO orders (id, labels, items) VALUES
(101, '["priority","web"]', '[{"sku":"A1","qty":2},{"sku":"B2","qty":1}]'),
(102, '["bulk","api"]', '[{"sku":"C3","qty":4}]'),
(103, '["priority","api"]', '[{"sku":"A1","qty":1},{"sku":"C3","qty":2}]');预期结果
DROP TABLE
CREATE TABLE
INSERT 0 3若表原本不存在,客户端还可能显示一条 NOTICE,不影响上述命令标签和后续查询。初始化数据必须使用 PostgreSQL 的 INSERT INTO 语句,不能用客户端元命令或其他非 SQL 写法代替。
直接判断标量数组
当目标只是判断顶层字符串数组里是否存在某个值,优先保留 JSONB 整体。? 判断单个顶层成员是否存在,@> 判断左侧 JSONB 数组是否包含右侧给出的成员集合。
SELECT id,
labels ? 'priority' AS has_priority,
labels @> '["priority","api"]'::jsonb AS has_both
FROM orders
ORDER BY id;预期结果
id | has_priority | has_both
-----+--------------+----------
101 | t | f
102 | f | f
103 | t | t@> 表达 JSONB 包含关系,数组元素不要求与右侧保持相同顺序。只做成员存在或固定结构匹配时,没有必要为了统一写法先展开数组。
展开数组做逐元素处理
当业务需要统计、计算或关联每个元素时,再使用集合返回函数。jsonb_array_elements_text 会把字符串数组拆成多行文本,LATERAL 让函数读取当前订单行中的 labels。
SELECT label, count(*) AS order_count
FROM orders AS o
CROSS JOIN LATERAL jsonb_array_elements_text(o.labels) AS t(label)
GROUP BY label
ORDER BY label;预期结果
label | order_count
----------+------------
api | 2
bulk | 1
priority | 2
web | 1展开后,一条订单记录可能变成多行,因此要重新检查聚合口径、连接基数和最终去重需求。仅做布尔判断时,展开往往只会制造不必要的中间结果。
查询对象数组
对象数组同样可以先尝试结构包含。下面同时判断“是否存在 sku = A1 的对象”和“是否存在数量大于等于 3 的元素”。前者适合 @>,后者需要展开后做数值比较。
SELECT o.id,
o.items @> '[{"sku":"A1"}]'::jsonb AS has_a1,
EXISTS (
SELECT 1
FROM jsonb_array_elements(o.items) AS e(item)
WHERE (e.item->>'qty')::integer >= 3
) AS has_qty_ge_3
FROM orders AS o
ORDER BY o.id;预期结果
id | has_a1 | has_qty_ge_3
-----+--------+--------------
101 | t | f
102 | f | t
103 | t | f右侧对象只需给出参与包含判断的字段,因此 [{"sku":"A1"}] 可以匹配还包含 qty 的数组元素。范围条件不能由 @> 表达;此时用 jsonb_array_elements 展开,并以 EXISTS 保持“订单是否命中”的布尔语义,避免多个元素命中时重复返回订单行。
选择直接过滤还是展开
成员存在、对象局部包含这类等值结构条件,优先使用 ? 或 @>;元素级范围比较、计算、聚合或与其他表关联时,再展开数组。若只是判断“至少存在一个满足条件的元素”,通常用 EXISTS 包住展开逻辑。
还要注意类型边界:jsonb_array_elements* 要求输入值确实是 JSON 数组,->> 返回 text,数值范围比较前应显式转换为 integer、numeric 等目标类型。若生产数据的结构或元素类型不稳定,应先治理写入格式或增加约束,而不是假设所有值都能安全展开和转换。