PostgreSQL中JSON/JSONB用于结构化数据的合理性及优选场景问询

一、JSON与JSONB的核心通用场景 #

JSON的适用场景 #

• 仅需要存储原始JSON格式数据，不需要对内部字段做查询、修改操作的场景，比如日志存储、原始API响应缓存——JSON会保留原始空格、键的顺序，适合需要还原原始数据的场景。
• 数据写入频率远高于读取频率，且读取时仅需完整取出整个JSON的场景。

JSONB的适用场景 #

• 需要对JSON内部字段进行查询、过滤、排序的场景，比如根据JSON里的某个字段筛选数据。
• 需要为JSON内部字段创建GIN/GIST索引来加速查询的场景。
• 需要对JSON数据进行部分修改（比如更新某个键的值、添加新键），而不需要重新写入整个JSON的场景。
• 存储非结构化/半结构化数据，且需要兼顾关系型数据库事务特性的场景。

二、JSONB优于新建表的场景 #

存在不少场景下，用JSONB比新建独立表更合适，典型的包括：

• 字段结构频繁变化的场景：比如产品迭代快，需要频繁新增或调整非核心属性，如果每次都修改表结构（加字段、改字段），会带来DDL锁、历史数据兼容等问题，用JSONB可以灵活扩展属性，无需修改表结构。
• 多态数据结构的场景：同一表中的记录需要存储不同结构的数据，比如一个events表，里面有用户注册、商品下单、页面浏览等不同类型的事件，每种事件的附加字段差异极大，用JSONB存事件详情，比建多个子表或加大量空值字段更高效。
• 快速原型开发阶段：项目初期需求不确定，用JSONB可以快速实现数据存储，无需提前设计复杂的表结构，待需求稳定后再考虑是否拆分表。
• 少量非核心扩展字段：比如用户表，核心字段（id、name、email）用常规列存储，而一些非核心的个性化设置（比如主题偏好、通知开关）用JSONB存储，既保证核心字段的查询效率，又灵活支持扩展。

三、JSONB的实际可行场景示例：电商商品属性存储 #

电商平台的商品品类繁多，不同品类的属性差异极大：

• 电子产品：需要存储内存、处理器型号、屏幕尺寸、电池容量等属性
• 服装：需要存储尺码、面料、颜色、版型等属性
• 图书：需要存储作者、出版社、ISBN、页数等属性

如果用传统的关系型表结构，要么建多个品类表（维护成本高），要么在主表加大量可空字段（冗余且不灵活），而用JSONB可以完美解决这个问题：

1. 创建商品表 #

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    attributes JSONB NOT NULL
);

2. 插入不同品类的商品数据 #

-- 插入电子产品
INSERT INTO products (name, category, price, attributes)
VALUES ('XX手机', '电子产品', 3999.00, '{
    "内存": "8GB",
    "处理器": "骁龙8Gen2",
    "屏幕尺寸": "6.7英寸",
    "电池容量": "5000mAh"
}');

-- 插入服装
INSERT INTO products (name, category, price, attributes)
VALUES ('XX牛仔裤', '服装', 199.00, '{
    "尺码": "30",
    "面料": "牛仔布",
    "颜色": "深蓝色",
    "版型": "修身"
}');

-- 插入图书
INSERT INTO products (name, category, price, attributes)
VALUES ('XX编程指南', '图书', 89.00, '{
    "作者": "张三",
    "出版社": "技术出版社",
    "ISBN": "978-7-121-45678-9",
    "页数": 520
}');

3. 灵活查询数据 #

-- 查询所有内存≥8GB的电子产品
SELECT name, price, attributes
FROM products
WHERE category = '电子产品' AND attributes @> '{"内存": "8GB"}';

-- 查询所有深蓝色的服装
SELECT name, price, attributes
FROM products
WHERE category = '服装' AND attributes ->> '颜色' = '深蓝色';

4. 为属性字段创建索引加速查询 #

-- 创建GIN索引，支持@>、?、?&、?|等操作符
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

这个场景中，JSONB既保留了关系型数据库的优势（事务、核心字段的约束与索引），又灵活支持了不同品类商品的非结构化属性存储，比新建多个表或用大量可空字段的方案更高效、易维护。

内容的提问来源于stack exchange，提问作者kouroshtajalliepour