PostgreSQL / 系统课时
JSONB 与数组
JSONB 与数组
业务字段无法完全固定时,JSONB 适合保存可查询的半结构化对象;同一列需要保存一组同类型值时,数组更直接。本课通过一张商品表完成写入、查询、局部更新、包含判断,并为常用条件准备索引。
学习目标
- 使用
JSONB和数组运算符查询半结构化数据 - 安全更新 JSON 属性与数组元素
- 根据查询方式创建合适的 GIN 或表达式索引
建表与写入
示例表使用 attrs 保存品牌、颜色等动态属性,使用 tags 保存同类型标签。JSONB 会以二进制结构存储,便于比较、包含判断和建立 GIN 索引。
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']
);预期结果:
CREATE TABLE
INSERT 0 2再查询完整记录进行验证。
SELECT id, name, attrs, tags
FROM product_demo
ORDER BY id;预期结果:
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 运算时使用 ->;需要比较字符串或转换为数字时通常使用 ->>。
SELECT
name,
attrs ->> 'brand' AS brand,
(attrs ->> 'stock')::integer AS stock
FROM product_demo
WHERE attrs @> '{"color":"black"}'::jsonb;预期结果:
name | brand | stock
----------+--------+------
机械键盘 | KeyPro | 12@> 表示左侧 JSONB 是否包含右侧结构。它比较的是结构和值,不依赖对象键的书写顺序。将数字文本转换为整数前,应保证字段存在且内容可转换,否则查询会报类型转换错误。
查询数组内容
数组包含判断同样使用 @>。判断是否包含指定元素时,右侧仍然必须是数组;tags @> ARRAY['wireless'] 表示标签集合至少包含 wireless。
SELECT id, name, tags
FROM product_demo
WHERE tags @> ARRAY['wireless']::text[];预期结果:
id | name | tags
----+----------+------------------
2 | 无线鼠标 | {mouse,wireless}判断“任意一个元素重叠”可以使用 &&,例如 tags && ARRAY['office', 'gaming']。判断某个值是否等于数组中的任意元素,也可写成 'office' = ANY(tags)。
局部更新 JSONB 与数组
更新单个 JSON 属性时使用 jsonb_set,避免在应用层读出整个对象再覆盖。下面把商品 1 的库存改为 15,并向标签数组追加一个不存在的新标签。
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;预期结果:
UPDATE 1
id | attrs | tags
----+----------------------------------------------------+---------------------------
1 | {"brand": "KeyPro", "color": "black", "stock": 15} | {keyboard,office,featured}jsonb_set 的第四个参数为 true,表示路径不存在时允许创建。数组追加前先做包含判断,可以避免重复标签。
索引准备与验证
常用包含查询可以使用 GIN 索引。若查询主要是 attrs @> ...,可考虑 jsonb_path_ops;若还需要键存在运算符等更广泛操作,使用默认操作符类更通用。
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'));预期结果:
CREATE INDEX
CREATE INDEX
CREATE INDEX第一个索引服务于 JSONB 包含查询,第二个服务于数组包含或重叠查询,第三个服务于按品牌文本等值过滤。下面直接检查三个索引是否已经注册。
SELECT indexname
FROM pg_indexes
WHERE tablename = 'product_demo'
AND indexname LIKE 'idx_product_demo_%'
ORDER BY indexname;预期结果:
indexname
------------------------------
idx_product_demo_attrs_gin
idx_product_demo_brand
idx_product_demo_tags_gin小表执行查询时仍可能选择顺序扫描,因为优化器认为成本更低;这不代表索引无效。生产环境应结合真实数据量、统计信息和 EXPLAIN 结果判断。
设计边界
JSONB 适合变化频繁、字段稀疏或来源不统一的属性,但经常参与连接、唯一约束和强类型校验的核心字段仍应拆成普通列。数组适合短小、同类型且整体读写的集合,不适合无限增长的明细关系。
当某个 JSON 属性逐渐变成高频筛选条件时,可以增加表达式索引;若还需要外键、复杂统计或独立生命周期,应把它迁移为结构化列或关联表,而不是继续堆在 JSON 中。
本课总结
JSONB使用->、->>和@>完成取值、文本比较与结构包含判断- PostgreSQL 数组可用
@>、&&和ANY表达包含、重叠与成员判断 - 索引必须与实际运算符匹配,GIN、表达式索引分别解决不同查询模式
下一课预告
下一课将继续学习高级数据表达,重点关注复杂字段如何与查询计划、约束和性能优化协同工作。