PostgreSQL / 系统课时

JSONB 与数组

JSONB 与数组

业务字段无法完全固定时,JSONB 适合保存可查询的半结构化对象;同一列需要保存一组同类型值时,数组更直接。本课通过一张商品表完成写入、查询、局部更新、包含判断,并为常用条件准备索引。

学习目标

  • 使用 JSONB 和数组运算符查询半结构化数据
  • 安全更新 JSON 属性与数组元素
  • 根据查询方式创建合适的 GIN 或表达式索引

建表与写入

示例表使用 attrs 保存品牌、颜色等动态属性,使用 tags 保存同类型标签。JSONB 会以二进制结构存储,便于比较、包含判断和建立 GIN 索引。

sql
CREATE TEMP TABLE product_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    attrs jsonb NOT NULL DEFAULT '{}'::jsonb,
    tags text[] NOT NULL DEFAULT ARRAY[]::text[]
);

INSERT INTO product_demo (name, attrs, tags) VALUES
(
    '机械键盘',
    '{"brand":"KeyPro","color":"black","stock":12}'::jsonb,
    ARRAY['keyboard', 'office']
),
(
    '无线鼠标',
    '{"brand":"MoveX","color":"white","stock":0}'::jsonb,
    ARRAY['mouse', 'wireless']
);

预期结果:

text
CREATE TABLE
INSERT 0 2

再查询完整记录进行验证。

sql
SELECT id, name, attrs, tags
FROM product_demo
ORDER BY id;

预期结果:

text
 id | name     | attrs                                              | tags
----+----------+----------------------------------------------------+------------------
  1 | 机械键盘 | {"brand": "KeyPro", "color": "black", "stock": 12} | {keyboard,office}
  2 | 无线鼠标 | {"brand": "MoveX", "color": "white", "stock": 0}    | {mouse,wireless}

实际显示中的空格可能因客户端格式化而不同,但键值和数组元素应一致。

查询 JSONB 字段

-> 返回 JSON 值,->> 返回文本。需要继续进行 JSON 运算时使用 ->;需要比较字符串或转换为数字时通常使用 ->>。

sql
SELECT
    name,
    attrs ->> 'brand' AS brand,
    (attrs ->> 'stock')::integer AS stock
FROM product_demo
WHERE attrs @> '{"color":"black"}'::jsonb;

预期结果:

text
 name     | brand  | stock
----------+--------+------
 机械键盘 | KeyPro |   12

@> 表示左侧 JSONB 是否包含右侧结构。它比较的是结构和值,不依赖对象键的书写顺序。将数字文本转换为整数前,应保证字段存在且内容可转换,否则查询会报类型转换错误。

查询数组内容

数组包含判断同样使用 @>。判断是否包含指定元素时,右侧仍然必须是数组;tags @> ARRAY['wireless'] 表示标签集合至少包含 wireless。

sql
SELECT id, name, tags
FROM product_demo
WHERE tags @> ARRAY['wireless']::text[];

预期结果:

text
 id | name     | tags
----+----------+------------------
  2 | 无线鼠标 | {mouse,wireless}

判断“任意一个元素重叠”可以使用 &&,例如 tags && ARRAY['office', 'gaming']。判断某个值是否等于数组中的任意元素,也可写成 'office' = ANY(tags)。

局部更新 JSONB 与数组

更新单个 JSON 属性时使用 jsonb_set,避免在应用层读出整个对象再覆盖。下面把商品 1 的库存改为 15,并向标签数组追加一个不存在的新标签。

sql
UPDATE product_demo
SET
    attrs = jsonb_set(attrs, '{stock}', to_jsonb(15), true),
    tags = CASE
        WHEN tags @> ARRAY['featured']::text[] THEN tags
        ELSE array_append(tags, 'featured')
    END
WHERE id = 1;

SELECT id, attrs, tags
FROM product_demo
WHERE id = 1;

预期结果:

text
UPDATE 1

 id | attrs                                              | tags
----+----------------------------------------------------+---------------------------
  1 | {"brand": "KeyPro", "color": "black", "stock": 15} | {keyboard,office,featured}

jsonb_set 的第四个参数为 true,表示路径不存在时允许创建。数组追加前先做包含判断,可以避免重复标签。

索引准备与验证

常用包含查询可以使用 GIN 索引。若查询主要是 attrs @> ...,可考虑 jsonb_path_ops;若还需要键存在运算符等更广泛操作,使用默认操作符类更通用。

sql
CREATE INDEX idx_product_demo_attrs_gin
ON product_demo
USING gin (attrs jsonb_path_ops);

CREATE INDEX idx_product_demo_tags_gin
ON product_demo
USING gin (tags);

CREATE INDEX idx_product_demo_brand
ON product_demo ((attrs ->> 'brand'));

预期结果:

text
CREATE INDEX
CREATE INDEX
CREATE INDEX

第一个索引服务于 JSONB 包含查询,第二个服务于数组包含或重叠查询,第三个服务于按品牌文本等值过滤。下面直接检查三个索引是否已经注册。

sql
SELECT indexname
FROM pg_indexes
WHERE tablename = 'product_demo'
  AND indexname LIKE 'idx_product_demo_%'
ORDER BY indexname;

预期结果:

text
          indexname
------------------------------
 idx_product_demo_attrs_gin
 idx_product_demo_brand
 idx_product_demo_tags_gin

小表执行查询时仍可能选择顺序扫描,因为优化器认为成本更低;这不代表索引无效。生产环境应结合真实数据量、统计信息和 EXPLAIN 结果判断。

设计边界

JSONB 适合变化频繁、字段稀疏或来源不统一的属性,但经常参与连接、唯一约束和强类型校验的核心字段仍应拆成普通列。数组适合短小、同类型且整体读写的集合,不适合无限增长的明细关系。

当某个 JSON 属性逐渐变成高频筛选条件时,可以增加表达式索引;若还需要外键、复杂统计或独立生命周期,应把它迁移为结构化列或关联表,而不是继续堆在 JSON 中。

本课总结

  • JSONB 使用 ->、->> 和 @> 完成取值、文本比较与结构包含判断
  • PostgreSQL 数组可用 @>、&& 和 ANY 表达包含、重叠与成员判断
  • 索引必须与实际运算符匹配,GIN、表达式索引分别解决不同查询模式

下一课预告

下一课将继续学习高级数据表达,重点关注复杂字段如何与查询计划、约束和性能优化协同工作。

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

目录与阅读设置

阅读设置

17

记录一个想法

JSONB 与数组

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

登录哪都通