跳到主要内容

JSONB 数据类型

问题​

PostgreSQL 的 JSONB 类型有哪些特性？如何查询和索引 JSONB 数据？JSONB 能替代 MongoDB 吗？

答案​

一、JSON vs JSONB​

对比项JSONJSONB
存储方式文本原样存储二进制解析后存储
写入速度快（不解析）稍慢（需解析）
读取速度慢（每次读取需解析）快（已解析）
保留格式保留空格、键顺序不保留
支持索引不支持支持 GIN 索引
推荐仅存储不查询几乎所有场景
结论

除非需要保留 JSON 原始格式，否则一律使用 JSONB。

二、JSONB 操作符​

提取操作符​

-- 创建示例表
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attrs JSONB  -- 使用 JSONB 存储灵活属性
);

INSERT INTO products (name, attrs) VALUES 
('iPhone 15', '{"brand": "Apple", "price": 7999, "colors": ["black", "blue"], "specs": {"ram": "8GB", "storage": "256GB"}}');

-- -> 提取 JSON 对象（返回 JSONB 类型）
SELECT attrs -> 'brand' FROM products;  -- "Apple"（带引号）

-- ->> 提取文本（返回 TEXT 类型）
SELECT attrs ->> 'brand' FROM products;  -- Apple（不带引号）

-- #> 路径提取（JSONB）
SELECT attrs #> '{specs, ram}' FROM products;  -- "8GB"

-- #>> 路径提取（TEXT）
SELECT attrs #>> '{specs, ram}' FROM products;  -- 8GB

-- 数组元素提取
SELECT attrs -> 'colors' -> 0 FROM products;  -- "black"

条件查询操作符​

-- @> 包含（最常用）
SELECT * FROM products WHERE attrs @> '{"brand": "Apple"}';

-- <@ 被包含
SELECT * FROM products WHERE '{"brand": "Apple", "price": 7999}' <@ attrs;

-- ? 键是否存在
SELECT * FROM products WHERE attrs ? 'brand';

-- ?| 任一键存在
SELECT * FROM products WHERE attrs ?| array['brand', 'color'];

-- ?& 所有键存在
SELECT * FROM products WHERE attrs ?& array['brand', 'price'];

修改操作符​

-- || 合并
UPDATE products SET attrs = attrs || '{"weight": "187g"}' WHERE id = 1;

-- - 删除键
UPDATE products SET attrs = attrs - 'weight' WHERE id = 1;

-- #- 删除路径
UPDATE products SET attrs = attrs #- '{specs, ram}' WHERE id = 1;

-- jsonb_set 设置嵌套值
UPDATE products 
SET attrs = jsonb_set(attrs, '{specs, ram}', '"12GB"')
WHERE id = 1;

三、JSONB 索引​

GIN 索引（最重要）​

-- 通用 GIN 索引（支持 @>, ?, ?|, ?& 操作符）
CREATE INDEX idx_attrs ON products USING GIN (attrs);

-- 路径操作类 GIN 索引（仅支持 @>，更小更快）
CREATE INDEX idx_attrs_path ON products USING GIN (attrs jsonb_path_ops);
索引类型支持操作符索引大小适用场景
GIN (默认)@>, ?, `?, ?&`较大
GIN (jsonb_path_ops)仅 @>较小只需要包含查询

表达式索引​

-- 对特定字段建 B-tree 索引
CREATE INDEX idx_brand ON products ((attrs ->> 'brand'));

-- 对数值字段建索引（注意类型转换）
CREATE INDEX idx_price ON products (((attrs ->> 'price')::int));

-- 查询时使用
SELECT * FROM products WHERE attrs ->> 'brand' = 'Apple';  -- 走 idx_brand
SELECT * FROM products WHERE (attrs ->> 'price')::int > 5000;  -- 走 idx_price
索引选择
  • 查询模式明确（如固定查某个字段）→ 用表达式索引，性能最好
  • 查询模式灵活（不确定查哪些字段）→ 用 GIN 索引

四、JSONB 常用函数​

-- 键值对展开
SELECT * FROM jsonb_each('{"a": 1, "b": 2}');
-- key | value
--  a  | 1
--  b  | 2

-- 获取所有键
SELECT jsonb_object_keys('{"a": 1, "b": 2}');

-- 数组元素展开
SELECT jsonb_array_elements('[1, 2, 3]');

-- 构建 JSONB
SELECT jsonb_build_object('name', '张三', 'age', 25);
-- {"age": 25, "name": "张三"}

-- 聚合为 JSONB 数组
SELECT jsonb_agg(name) FROM products;

-- 聚合为 JSONB 对象
SELECT jsonb_object_agg(name, attrs ->> 'price') FROM products;

-- 类型判断
SELECT jsonb_typeof(attrs -> 'price') FROM products;  -- number
SELECT jsonb_typeof(attrs -> 'colors') FROM products;  -- array

五、JSONB 最佳实践​

场景建议
固定字段用普通列，不要放 JSONB
灵活属性适合 JSONB（如商品属性、配置）
嵌套层级建议不超过 3~4 层
数组元素很多考虑拆为关联表
需要聚合分析JSONB 性能不如普通列

六、JSONB vs MongoDB​

对比项PostgreSQL + JSONBMongoDB
数据模型关系+文档混合纯文档
事务完整 ACID 事务4.0+ 支持多文档事务
JOIN支持不支持（需 $lookup）
灵活性很好更好
索引GIN + 表达式索引丰富的索引类型
分片Citus 扩展原生分片
适用场景80% 关系 + 20% 文档纯文档型数据

常见面试问题​

Q1: 什么场景适合用 JSONB？​

答案：

  1. 灵活属性：商品的自定义属性（不同类目属性不同）
  2. 配置存储：用户设置、系统配置
  3. API 响应缓存：缓存第三方 API 的 JSON 响应
  4. 日志/事件数据：Schema 不固定的半结构化数据
  5. 快速原型：早期开发阶段 Schema 还在演化

Q2: JSONB 的查询性能怎么样？​

答案：

  • 不建索引：和全表扫描无异
  • GIN 索引：@> 包含查询性能很好
  • 表达式索引：特定字段查询接近普通列性能
  • 聚合计算：不如普通列（需要运行时提取）

生产建议：高频查询字段建表达式索引，灵活查询用 GIN 索引。

Q3: JSONB 存储大小有限制吗？​

答案：
单个 JSONB 值最大 256MB（受 TOAST 限制）。但实际使用中，JSONB 太大会影响性能。建议单个 JSONB 值不超过几十 KB，大文档考虑拆分。

相关链接​