JSONB 基础与存储

PostgreSQL 有两种 JSON 类型：JSON 和 JSONB。几乎所有生产场景你都应该用 JSONB。了解为什么，以及如何正确使用它。

JSON vs JSONB 的核心区别

结论：除非你需要保留 JSON 的原始格式（包括重复键和空格），否则始终使用 JSONB。

创建 JSONB 列

CREATE TABLE products (
id         BIGSERIAL PRIMARY KEY,
name       TEXT NOT NULL,
price      NUMERIC(10, 2) NOT NULL,
attributes JSONB,           -- 可变的产品属性
metadata   JSONB NOT NULL DEFAULT '{}'  -- 元数据，默认空对象
);
-- 插入 JSONB 数据
INSERT INTO products (name, price, attributes) VALUES
('MacBook Pro 14"', 6999.00, '{"color": "Silver", "memory": "16GB", "storage": "512GB", "weight": 1.6}'),
('iPhone 15', 3999.00, '{"color": "Black", "storage": "128GB", "5g": true}'),
('AirPods Pro', 1599.00, '{"color": "White", "noise_cancellation": true, "battery_hours": 6}');

基础查询操作符

读取值

-- -> 返回 JSON 类型的值（对象或数组元素）
SELECT attributes -> 'color' FROM products;           -- 返回 "Silver"（带引号）
SELECT attributes -> 'memory' FROM products;          -- 返回 "16GB"（带引号）
-- ->> 返回文本类型（去掉引号）
SELECT attributes ->> 'color' FROM products;          -- 返回 Silver（无引号）
SELECT attributes ->> 'memory' FROM products;         -- 返回 16GB
-- #> 访问嵌套路径（返回 JSON）
SELECT attributes #> '{specs, processor}' FROM products;
-- #>> 访问嵌套路径（返回文本）
SELECT attributes #>> '{specs, processor}' FROM products;

WHERE 条件中使用

-- 等值查询（->> 返回文本，比较文本）
SELECT * FROM products WHERE attributes ->> 'color' = 'Silver';
-- 包含查询（@>：左边是否包含右边）
SELECT * FROM products WHERE attributes @> '{"color": "Silver"}';
SELECT * FROM products WHERE attributes @> '{"5g": true}';
-- 键存在查询
SELECT * FROM products WHERE attributes ? 'noise_cancellation';  -- 键存在
SELECT * FROM products WHERE attributes ?| ARRAY['color', 'size'];  -- 任意键存在
SELECT * FROM products WHERE attributes ?& ARRAY['color', 'storage'];  -- 所有键都存在
-- 数值比较（转为数值类型）
SELECT * FROM products
WHERE (attributes ->> 'battery_hours')::INTEGER >= 5;

JSONB 路径查询（PostgreSQL 12+）

-- jsonb_path_query：更现代的 JSON 路径查询语法
SELECT jsonb_path_query(attributes, '$.color') FROM products;
-- jsonb_path_exists：测试路径是否存在
SELECT * FROM products
WHERE jsonb_path_exists(attributes, '$.5g ? (@ == true)');
-- jsonb_path_query_array：返回所有匹配的结果数组
SELECT jsonb_path_query_array(
'[{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]'::jsonb,
'$[*].name'
);
-- 输出：["Alice", "Bob"]

修改 JSONB 数据

-- jsonb_set：修改或添加键
UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"Space Gray"')
WHERE id = 1;
-- 添加新键
UPDATE products
SET attributes = jsonb_set(attributes, '{warranty_years}', '2', true)
WHERE id = 1;
-- 删除键（|| 合并，减去特定键）
UPDATE products
SET attributes = attributes - 'weight'
WHERE id = 1;
-- 删除嵌套键
UPDATE products
SET attributes = attributes #- '{specs, old_field}'
WHERE id = 1;
-- 合并两个 JSONB 对象（后者覆盖前者的同名键）
UPDATE products
SET attributes = attributes || '{"in_stock": true, "last_updated": "2024-01-15"}'
WHERE id = 1;

JSONB 展开为关系行

-- jsonb_each：把 JSONB 对象展开为键值对行
SELECT
id,
key AS attribute_name,
value AS attribute_value
FROM products,
jsonb_each(attributes);
-- jsonb_object_keys：只返回键名
SELECT id, jsonb_object_keys(attributes) AS key
FROM products;
-- jsonb_to_recordset：把 JSONB 数组展开为表
SELECT *
FROM jsonb_to_recordset('[
{"name": "Alice", "age": 30},
{"name": "Bob", "age": 25}
]'::jsonb) AS t(name TEXT, age INTEGER);

JSON vs JSONB：何时用 JSONB，何时用关系列

✅ 适合 JSONB 的场景：
- 属性数量不固定（电商产品的 SKU 属性）
- Schema 频繁变化（配置存储、用户自定义字段）
- 存储外部 API 响应（Webhook 载荷）
- 半结构化日志
❌ 不适合 JSONB 的场景（用关系列）：
- 每个对象都有相同的固定字段
- 需要频繁 JOIN 或复杂查询
- 字段需要外键约束
- 需要强类型验证（JSONB 里的数字可以是字符串）

下一节：GIN 索引加速 JSONB 查询——没有索引的 JSONB 查询是全表扫描。GIN 索引能让 @> 等包含查询提速 100 倍以上。