介绍 PostgreSQL 的 JSON 字段类型及其使用方法。

PostgreSQL JSON 类型概述

PostgreSQL 提供了两种 JSON 数据类型：

表格

类型	特点	适用场景
json	存储原始文本，解析时验证格式	不需要频繁查询/索引 JSON 内容
jsonb	二进制存储，去重、排序，支持索引	需要高效查询、索引、更新操作

推荐：绝大多数情况下使用 jsonb，性能更好且支持更多操作。

---

基础操作示例

1. 创建表

sql

复制

-- 使用 jsonb 类型
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

-- 插入数据
INSERT INTO products (name, attributes) VALUES 
('iPhone 15', '{"color": "blue", "storage": "256GB", "price": 6999}'),
('MacBook Pro', '{"color": "silver", "cpu": "M3", "ram": "16GB", "price": 14999}');

2. 查询操作

sql

复制

-- 查询所有属性
SELECT attributes FROM products;

-- 提取特定字段（-> 返回jsonb，->> 返回text）
SELECT 
    name,
    attributes->>'color' as color,      -- 返回 text: "blue"
    attributes->'price' as price_json   -- 返回 jsonb: 6999
FROM products;

-- 条件查询
SELECT * FROM products 
WHERE attributes->>'color' = 'blue';

-- 嵌套查询
SELECT * FROM products 
WHERE attributes @> '{"storage": "256GB"}';  -- @> 包含操作符

3. 更新操作

sql

复制

-- 更新整个 JSON
UPDATE products 
SET attributes = '{"color": "black", "storage": "512GB", "price": 7999}'
WHERE id = 1;

-- 更新特定字段
UPDATE products 
SET attributes = jsonb_set(
    attributes, 
    '{price}', 
    '7999'::jsonb
) WHERE id = 1;

-- 添加新字段
UPDATE products 
SET attributes = attributes || '{"warranty": "2年"}'::jsonb
WHERE id = 1;

-- 删除字段
UPDATE products 
SET attributes = attributes - 'warranty'
WHERE id = 1;

---

高级查询与操作

1. 数组与迭代

sql

复制

-- JSON 数组操作
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    tags JSONB
);

INSERT INTO events (tags) VALUES 
('["database", "postgres", "json"]');

-- 展开数组
SELECT jsonb_array_elements(tags) FROM events;

-- 检查数组是否包含元素
SELECT * FROM events WHERE tags @> '["postgres"]';

2. 聚合与统计

sql

复制

-- 统计不同颜色的产品数量
SELECT 
    attributes->>'color' as color,
    COUNT(*) 
FROM products 
GROUP BY attributes->>'color';

-- 计算平均价格（需转换类型）
SELECT AVG((attributes->>'price')::numeric) 
FROM products;

3. 路径查询

sql

复制

-- 使用 #> 操作符进行路径查询
-- 假设数据结构: {"specs": {"cpu": {"cores": 8, "brand": "Apple"}}}

SELECT attributes#>'{specs,cpu,brand}' FROM products;

-- 使用 #>> 返回 text
SELECT attributes#>>'{specs,cpu,cores}' FROM products;

---

索引优化

sql

复制

-- GIN 索引（推荐用于 jsonb）
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 针对特定键的索引
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- 路径索引
CREATE INDEX idx_products_price ON products ((attributes->>'price'));

---

常用操作符速查

表格

操作符	说明	示例
->	获取 JSON 对象字段（返回 jsonb）	data->'key'
->>	获取 JSON 对象字段（返回 text）	data->>'key'
#>	按路径获取（返回 jsonb）	data#>'{a,b}'
#>>	按路径获取（返回 text）	data#>>'{a,b}'
@>	包含（左侧包含右侧）	data @> '{"a":1}'
<@	被包含	'{"a":1}' <@ data
?	是否存在键	data ? 'key'
`?	`	是否存在任意键	data ?| array['a','b']
?&	是否都存在	data ?& array['a','b']
||	合并	data1 || data2
-	删除键/索引	data - 'key'

---

实际应用场景

场景 1：动态配置存储

sql

复制

CREATE TABLE app_configs (
    app_name VARCHAR(50) PRIMARY KEY,
    config JSONB NOT NULL
);
-- 存储不同应用的不同配置结构，无需修改表结构

场景 2：日志/事件数据

sql

复制

CREATE TABLE system_logs (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT NOW(),
    event_type VARCHAR(50),
    payload JSONB  -- 存储变化的结构化数据
);

场景 3：电商产品属性

sql

复制

-- 不同品类有不同属性，无需为每个属性建字段
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50),
    base_price DECIMAL,
    attributes JSONB  -- 服装: {size, color, material}, 电子产品: {cpu, ram, storage}
);

---

最佳实践

1. 始终优先使用 jsonb - 除非有特殊需求需要保留原始格式（如空格、键顺序）

2. 为常用查询字段建立索引 - 使用 GIN 索引或表达式索引

3. 避免存储过大 JSON - 单个 JSON 字段建议不超过 1MB

4. 关键字段考虑单独建列 - 如果某字段频繁用于 JOIN 或作为外键，建议单独建列

5. 使用参数化查询 - 防止 JSON 注入攻击

需要了解更具体的某个方面，比如性能优化、与 ORM 框架（如 MyBatis、JPA）的集成，或者特定业务场景的设计方案吗？