PostgreSQL中JSONB的使用与踩坑指南_Javascript

postgresql中jsonb的使用与踩坑记录

前言

之前接到一个数据迁移的需求，要批量修改表里 jsonb 数组中的某个字段。本以为很简单，结果折腾了大半天，踩了不少坑。这篇文章把 jsonb 的常用操作和我踩过的坑都整理出来，希望能帮到遇到类似问题的朋友。

一、jsonb是什么

1.1 json vs jsonb

postgresql 提供了两种 json 类型：

类型	存储方式	查询性能	写入性能	支持索引
json	原始文本	慢（每次解析）	快	不支持
jsonb	二进制格式	快	稍慢（需要转换）	支持

简单理解：

json 就像把 json 字符串原封不动存进去，每次查询都要重新解析
jsonb 会把 json 转成二进制格式存储，查询时直接读取，不需要解析

99% 的场景都应该用 jsonb，除非你只是存储不查询。

1.2 jsonb的优势

相比传统的 eav（entity-attribute-value）模式，jsonb 有明显优势：

传统 eav 模式：

-- 商品表
create table products (id int, name varchar(100));
-- 属性表（每个属性一行）
create table product_attributes (
    product_id int,
    attr_key varchar(50),
    attr_value varchar(200)
);
-- 查询某商品的所有属性，需要 join
select p.name, pa.attr_key, pa.attr_value
from products p
join product_attributes pa on p.id = pa.product_id
where p.id = 1;

jsonb 模式：

-- 一张表搞定
create table products (
    id int,
    name varchar(100),
    attributes jsonb  -- 所有扩展属性都在这里
);
-- 直接查询，不需要 join
select name, attributes from products where id = 1;
-- 还能直接查询 json 内部的字段
select name from products where attributes->>'color' = 'red';

jsonb 的核心优势：

灵活：不同商品可以有不同的属性，不需要改表结构
高效：支持 gin 索引，查询性能有保障
简洁：减少表的数量，降低 join 复杂度

二、jsonb基础操作

2.1 创建和插入

-- 创建包含 jsonb 列的表
create table user_profiles (
    id serial primary key,
    user_id int not null,
    profile jsonb default '{}'::jsonb,
    created_at timestamp default now()
);
-- 插入数据
insert into user_profiles (user_id, profile) values
(1, '{"name": "张三", "age": 28, "tags": ["开发", "后端"], "settings": {"theme": "dark", "notify": true}}'),
(2, '{"name": "李四", "age": 32, "tags": ["产品", "管理"], "settings": {"theme": "light", "notify": false}}'),
(3, '{"name": "王五", "age": 25, "tags": ["前端", "全栈"], "settings": {"theme": "dark", "notify": true}}');

2.2 查询操作符

jsonb 提供了丰富的操作符：

操作符	说明	返回类型	示例
`->`	获取 json 对象字段	jsonb	`profile->'name'` → `"张三"`
`->>`	获取 json 对象字段	text	`profile->>'name'` → `张三`
`->`	获取数组元素（按索引）	jsonb	`profile->'tags'->0` → `"开发"`
`->>`	获取数组元素	text	`profile->'tags'->>0` → `开发`
`#>`	按路径获取	jsonb	`profile#>'{settings,theme}'` → `"dark"`
`#>>`	按路径获取	text	`profile#>>'{settings,theme}'` → `dark`

实际使用示例：

-- 获取用户名（返回 jsonb 类型，带引号）
select profile->'name' from user_profiles where user_id = 1;
-- 结果："张三"
-- 获取用户名（返回 text 类型，不带引号）
select profile->>'name' from user_profiles where user_id = 1;
-- 结果：张三
-- 获取嵌套字段
select profile->'settings'->>'theme' from user_profiles where user_id = 1;
-- 结果：dark
-- 使用路径操作符（更简洁）
select profile#>>'{settings,theme}' from user_profiles where user_id = 1;
-- 结果：dark
-- 获取数组第一个元素
select profile->'tags'->>0 from user_profiles where user_id = 1;
-- 结果：开发

2.3 条件查询

-- 查询年龄大于 30 的用户
select * from user_profiles 
where (profile->>'age')::int > 30;
-- 查询使用深色主题的用户
select * from user_profiles 
where profile#>>'{settings,theme}' = 'dark';
-- 查询标签包含"后端"的用户
select * from user_profiles 
where profile->'tags' ? '后端';
-- 查询同时包含多个标签的用户
select * from user_profiles 
where profile->'tags' ?& array['开发', '后端'];
-- 查询包含任意一个标签的用户
select * from user_profiles 
where profile->'tags' ?| array['前端', '后端'];

2.4 包含查询

@> 操作符用于判断左边的 jsonb 是否包含右边的 jsonb：

-- 查询 settings 中 theme 为 dark 的用户
select * from user_profiles 
where profile @> '{"settings": {"theme": "dark"}}';
-- 查询标签包含"开发"的用户
select * from user_profiles 
where profile @> '{"tags": ["开发"]}';

注意：@> 可以利用 gin 索引，性能很好。

三、jsonb索引详解

3.1 gin索引基础

gin（generalized inverted index）是 jsonb 最常用的索引类型：

-- 创建默认的 gin 索引
create index idx_profile_gin on user_profiles using gin(profile);

这个索引支持以下操作符：

@> 包含
? 键存在
?& 所有键存在
?| 任意键存在

3.2 jsonb_path_ops

如果你只需要 @> 操作符，可以使用更高效的 jsonb_path_ops：

-- 创建 jsonb_path_ops 索引
create index idx_profile_path on user_profiles using gin(profile jsonb_path_ops);

对比：

索引类型	索引大小	支持的操作符
默认 gin	较大	`@>`, `?`, `?&`, `?
jsonb_path_ops	较小（约 1/3）	仅 `@>`

建议：如果只用 @> 查询，优先选择 jsonb_path_ops。

3.3 表达式索引

如果经常查询某个特定字段，可以创建表达式索引：

-- 为 profile->>'name' 创建 b-tree 索引
create index idx_profile_name on user_profiles ((profile->>'name'));
-- 为 age 创建索引（转换为整数）
create index idx_profile_age on user_profiles (((profile->>'age')::int));
-- 查询时可以利用索引
select * from user_profiles where profile->>'name' = '张三';
select * from user_profiles where (profile->>'age')::int > 30;

3.4 索引选择策略

查询模式	推荐索引
`profile @> '{"key": "value"}'`	gin (jsonb_path_ops)
`profile ? 'key'`	gin (默认)
`profile->>'key' = 'value'`	b-tree 表达式索引
`(profile->>'num')::int > 100`	b-tree 表达式索引

四、jsonb数组操作

jsonb 数组操作是实际开发中的高频需求，也是很多人踩坑的地方。

4.1 数组展开

jsonb_array_elements 函数可以将数组展开成多行：

-- 原始数据
select profile->'tags' from user_profiles where user_id = 1;
-- 结果：["开发", "后端"]
-- 展开数组
select jsonb_array_elements(profile->'tags') as tag
from user_profiles where user_id = 1;
-- 结果：
-- "开发"
-- "后端"
-- 展开为文本（去掉引号）
select jsonb_array_elements_text(profile->'tags') as tag
from user_profiles where user_id = 1;
-- 结果：
-- 开发
-- 后端

4.2 保留数组顺序

展开数组时，如果需要保留原始顺序，使用 with ordinality：

select elem, idx
from user_profiles,
     jsonb_array_elements(profile->'tags') with ordinality as t(elem, idx)
where user_id = 1;
-- 结果：
-- elem     | idx
-- "开发"   | 1
-- "后端"   | 2

这个技巧非常重要，后面批量更新时会用到。

4.3 数组聚合

jsonb_agg 函数可以将多行聚合成数组：

-- 将所有用户的名字聚合成数组
select jsonb_agg(profile->>'name') from user_profiles;
-- 结果：["张三", "李四", "王五"]
-- 按顺序聚合
select jsonb_agg(elem order by idx)
from user_profiles,
     jsonb_array_elements(profile->'tags') with ordinality as t(elem, idx)
where user_id = 1;

4.4 数组修改

-- 追加元素到数组末尾
update user_profiles
set profile = jsonb_set(profile, 'postgresql
jsonb
', profile->'tags' || '"运维"'::jsonb)
where user_id = 1;
-- 删除数组中的某个元素
update user_profiles
set profile = jsonb_set(
    profile, 
    'postgresql
jsonb
', 
    (select jsonb_agg(elem) from jsonb_array_elements(profile->'tags') as elem where elem <> '"后端"')
)
where user_id = 1;

五、jsonb批量更新实战

这是本文的重点，也是实际工作中最容易出问题的地方。

5.1 场景描述

假设我们有一个促销配置表，每个商品可以配置多条促销规则：

create table product_promo_config (
    id uuid primary key default gen_random_uuid(),
    product_id uuid not null,
    shop_id uuid not null,
    promo_rules jsonb not null,  -- 促销规则数组
    created_at timestamp default now()
);
-- 插入测试数据
insert into product_promo_config (product_id, shop_id, promo_rules) values
('a1111111-1111-1111-1111-111111111111', 's1111111-1111-1111-1111-111111111111',
 '[{"code": "discount-fixed", "enabled": true, "params": {"rate": 0.8}},
   {"code": "coupon-amount", "enabled": true, "params": {"max": 50}},
   {"code": "gift-random", "enabled": false, "params": {}}]'),
('a2222222-2222-2222-2222-222222222222', 's1111111-1111-1111-1111-111111111111',
 '[{"code": "discount-percent", "enabled": true, "params": {"percent": 10}}]');

现在需求来了：批量修改所有规则的 code 字段，按照映射关系：

旧 code	新 code
discount-fixed	price-discount-fixed
discount-percent	price-discount-percent
coupon-amount	order-coupon-amount
gift-random	activity-gift-random

5.2 错误示范

很多人第一反应是用 jsonb_set 直接改：

-- 错误！只能改数组第一个元素
update product_promo_config
set promo_rules = jsonb_set(promo_rules, '{0,code}', '"price-discount-fixed"')
where promo_rules->0->>'code' = 'discount-fixed';

这样只能改数组的第一个元素，如果一个商品配了多条规则，后面的规则就改不到。

5.3 正确方案

正确的做法是：展开 → 替换 → 聚合。

第一步：创建映射表

create temporary table code_mapping (
    old_code varchar(100) primary key,
    new_code varchar(100) not null
);
insert into code_mapping (old_code, new_code) values
('discount-fixed', 'price-discount-fixed'),
('discount-percent', 'price-discount-percent'),
('coupon-amount', 'order-coupon-amount'),
('coupon-percent', 'order-coupon-percent'),
('gift-random', 'activity-gift-random'),
('gift-specific', 'activity-gift-specific');

第二步：预览更新结果

在执行 update 之前，先用 select 预览：

select 
    ppc.id,
    ppc.promo_rules as old_rules,
    (
        select jsonb_agg(
            case 
                when cm.new_code is not null 
                then jsonb_set(elem, '[code]', to_jsonb(cm.new_code))
                else elem
            end
            order by idx  -- 保持原始顺序
        )
        from jsonb_array_elements(ppc.promo_rules) with ordinality as t(elem, idx)
        left join code_mapping cm on elem->>'code' = cm.old_code
    ) as new_rules
from product_promo_config ppc;

解释这段 sql：

jsonb_array_elements(ppc.promo_rules) with ordinality as t(elem, idx)
将 promo_rules 数组展开成多行
- elem 是每个元素，idx 是原始位置（从 1 开始）
- left join code_mapping cm on elem->>'code' = cm.old_code
和映射表关联，找到对应的新 code
- case when ... then jsonb_set(...) else elem end
如果找到映射就替换，找不到就保持原样
jsonb_agg(... order by idx)
- 聚合回数组，按原始顺序排列

第三步：执行更新

确认预览结果正确后，执行更新：

begin;
update product_promo_config ppc
set promo_rules = (
    select coalesce(
        jsonb_agg(
            case 
                when cm.new_code is not null 
                then jsonb_set(elem, '[code]', to_jsonb(cm.new_code))
                else elem
            end
            order by idx
        ),
        '[]'::jsonb  -- 处理空数组的情况
    )
    from jsonb_array_elements(ppc.promo_rules) with ordinality as t(elem, idx)
    left join code_mapping cm on elem->>'code' = cm.old_code
)
where exists (
    select 1 
    from jsonb_array_elements(ppc.promo_rules) as e
    join code_mapping cm on e->>'code' = cm.old_code
);
-- 验证结果
select id, promo_rules from product_promo_config;
-- 确认无误后提交
commit;

5.4 通用模板

这是 jsonb 数组批量更新的通用模板，可以直接套用：

update your_table t
set json_column = (
    select coalesce(
        jsonb_agg(
            case 
                when mapping.new_val is not null 
                then jsonb_set(elem, '{field_name}', to_jsonb(mapping.new_val))
                else elem
            end
            order by idx  -- 保持原顺序
        ),
        '[]'::jsonb  -- 处理空数组
    )
    from jsonb_array_elements(t.json_column) with ordinality as x(elem, idx)
    left join mapping_table mapping on elem->>'field_name' = mapping.old_val
)
where exists (
    select 1 
    from jsonb_array_elements(t.json_column) as e
    join mapping_table mapping on e->>'field_name' = mapping.old_val
);

六、jsonb性能优化

6.1 避免全表扫描

-- 慢：没有索引支持
select * from user_profiles where profile->>'name' = '张三';
-- 快：创建表达式索引
create index idx_profile_name on user_profiles ((profile->>'name'));
select * from user_profiles where profile->>'name' = '张三';
-- 快：使用 @> 操作符 + gin 索引
create index idx_profile_gin on user_profiles using gin(profile jsonb_path_ops);
select * from user_profiles where profile @> '{"name": "张三"}';

6.2 减少 jsonb 大小

jsonb 越大，查询和更新越慢。建议：

只存必要的字段
避免深层嵌套（建议不超过 3 层）
大文本考虑单独存储

-- 不推荐：把所有东西都塞进 jsonb
profile = '{"name": "...", "avatar_base64": "超长字符串...", "history": [...]}'
-- 推荐：大字段单独存储
profile = '{"name": "...", "avatar_id": "xxx"}'
-- avatar 内容存在单独的表或对象存储

6.3 批量操作优化

-- 慢：逐行更新
update user_profiles set profile = jsonb_set(profile, '{age}', '29') where user_id = 1;
update user_profiles set profile = jsonb_set(profile, '{age}', '33') where user_id = 2;
update user_profiles set profile = jsonb_set(profile, '{age}', '26') where user_id = 3;
-- 快：批量更新
update user_profiles as up
set profile = jsonb_set(up.profile, '{age}', to_jsonb(v.new_age))
from (values (1, 29), (2, 33), (3, 26)) as v(user_id, new_age)
where up.user_id = v.user_id;

6.4 使用 explain 分析

explain analyze 
select * from user_profiles where profile @> '{"settings": {"theme": "dark"}}';
-- 查看是否使用了索引
-- index scan using idx_profile_gin on user_profiles  (cost=...)

七、常见问题与最佳实践

7.1 常见问题

问题	原因	解决方案
查询慢	没有合适的索引	根据查询模式创建 gin 或表达式索引
更新数组只改了第一个	用了 `jsonb_set(col, '{0,field}', ...)`	使用展开-替换-聚合模式
数组顺序乱了	`jsonb_agg` 不保证顺序	加 `with ordinality` + `order by`
空数组报错	`jsonb_agg` 对空集返回 null	用 `coalesce(..., '[]'::jsonb)`
类型转换错误	`->` 返回 jsonb，`->>` 返回 text	注意操作符的返回类型

7.2 最佳实践

设计阶段：

明确哪些字段放 jsonb，哪些放普通列
高频查询的字段考虑提取为普通列
设计合理的 json 结构，避免过深嵌套

开发阶段：

优先使用 @> 操作符（可以利用 gin 索引）
数组操作记得保持顺序
update 前先 select 预览

运维阶段：

监控 jsonb 列的大小
定期 vacuum 清理死元组
关注慢查询日志

7.3 jsonb 操作速查表

-- 取值
col->'key'                    -- 返回 jsonb
col->>'key'                   -- 返回 text
col->0                        -- 数组第一个元素（jsonb）
col->>0                       -- 数组第一个元素（text）
col#>'{a,b,c}'               -- 路径取值（jsonb）
col#>>'{a,b,c}'              -- 路径取值（text）
-- 修改
jsonb_set(col, '{key}', '"value"'::jsonb)           -- 设置字段
jsonb_set(col, '{key}', to_jsonb(variable))         -- 设置字段（变量）
col || '{"new_key": "value"}'::jsonb                -- 合并
col - 'key'                                          -- 删除键
col - 0                                              -- 删除数组第一个元素
-- 数组操作
jsonb_array_elements(col)                           -- 展开数组
jsonb_array_elements(col) with ordinality           -- 展开并保留顺序
jsonb_agg(elem)                                     -- 聚合成数组
jsonb_agg(elem order by idx)                        -- 按顺序聚合
jsonb_array_length(col)                             -- 数组长度
-- 判断
col ? 'key'                   -- 键是否存在
col ?& array['a','b']        -- 所有键是否存在
col ?| array['a','b']        -- 任意键是否存在
col @> '{"key": "val"}'      -- 是否包含
-- 类型
jsonb_typeof(col)            -- 返回类型（object/array/string/number/boolean/null）

八、总结

本文从基础到进阶，系统讲解了 postgresql jsonb 的使用：

基础操作：-> 和 ->> 的区别，条件查询的写法
索引策略：gin 索引、jsonb_path_ops、表达式索引的选择
数组操作：展开、聚合、保持顺序的技巧
批量更新：展开-替换-聚合的通用模式
性能优化：索引选择、批量操作、explain 分析

jsonb 是 postgresql 的杀手级特性之一，掌握它可以让你在很多场景下避免引入额外的中间件。希望这篇文章能帮你在实际工作中少踩坑。

到此这篇关于postgresql中jsonb的使用与踩坑记录的文章就介绍到这了,更多相关postgresql jsonb使用内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

PostgreSQL中JSONB的使用与踩坑指南

2025年12月22日 • Javascript •我要评论