PostgreSQL / 系统课时

查询 JSONB 数组

查询 JSONB 数组

JSONB 数组查询的关键不是先展开,而是先判断问题属于成员存在、结构包含,还是元素级计算。下面用同一张订单表同时演示标量数组和对象数组,并说明直接过滤、关系化展开与 EXISTS 的选择边界。

学习目标

  • 使用 ? 与 @> 判断 JSONB 数组的成员存在和结构包含
  • 使用 jsonb_array_elements_text 与 jsonb_array_elements 展开数组
  • 根据查询目标在直接过滤、聚合展开和 EXISTS 之间做选择

准备示例数据

用 labels 保存字符串数组,用 items 保存对象数组。后续查询都基于这组数据,避免为不同操作重复创建表。

sql
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}]');

预期结果

text
DROP TABLE
CREATE TABLE
INSERT 0 3

若表原本不存在,客户端还可能显示一条 NOTICE,不影响上述命令标签和后续查询。初始化数据必须使用 PostgreSQL 的 INSERT INTO 语句,不能用客户端元命令或其他非 SQL 写法代替。

直接判断标量数组

当目标只是判断顶层字符串数组里是否存在某个值,优先保留 JSONB 整体。? 判断单个顶层成员是否存在,@> 判断左侧 JSONB 数组是否包含右侧给出的成员集合。

sql
SELECT id,
       labels ? 'priority' AS has_priority,
       labels @> '["priority","api"]'::jsonb AS has_both
FROM orders
ORDER BY id;

预期结果

text
 id  | has_priority | has_both
-----+--------------+----------
 101 | t            | f
 102 | f            | f
 103 | t            | t

@> 表达 JSONB 包含关系,数组元素不要求与右侧保持相同顺序。只做成员存在或固定结构匹配时,没有必要为了统一写法先展开数组。

展开数组做逐元素处理

当业务需要统计、计算或关联每个元素时,再使用集合返回函数。jsonb_array_elements_text 会把字符串数组拆成多行文本,LATERAL 让函数读取当前订单行中的 labels。

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

预期结果

text
  label   | order_count
----------+------------
 api      |          2
 bulk     |          1
 priority |          2
 web      |          1

展开后,一条订单记录可能变成多行,因此要重新检查聚合口径、连接基数和最终去重需求。仅做布尔判断时,展开往往只会制造不必要的中间结果。

查询对象数组

对象数组同样可以先尝试结构包含。下面同时判断“是否存在 sku = A1 的对象”和“是否存在数量大于等于 3 的元素”。前者适合 @>,后者需要展开后做数值比较。

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

预期结果

text
 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 等目标类型。若生产数据的结构或元素类型不稳定,应先治理写入格式或增加约束,而不是假设所有值都能安全展开和转换。

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

目录与阅读设置

记录一个想法

查询 JSONB 数组

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

登录哪都通