目录

我用一个 Postgres 表替换了 MongoDB，这是我学到的经验

在数据库的世界里，NoSQL 和 SQL 的争论从未停止。但今天，我想和大家分享一个可能会颠覆你对数据库选择思维的技巧：Postgres 的 JSONB。

CREATE TABLE MongoDB (
  _id UUID PRIMARY KEY,
  data JSONB
);

没错，就是这么简单。如果我告诉你，90% 的 NoSQL 用例都可以用这个简单的 Postgres 表结构来解决，你会不会感到惊讶？让我们一起探索为什么这个看似简单的方案可能是你未曾发现的数据库利器。

为什么我们总觉得需要 NoSQL？

我们都经历过这样的场景：深夜，你正在为数据库迁移而焦头烂额，质疑着自己的职业选择。产品经理说"再加一个字段"，你就得编写新的迁移脚本，仿佛回到了数据库原始时代。

这时，你可能会想：“要是用 MongoDB 就好了，模式灵活、无需迁移、文档存储…”

但事实是：你可能不需要切换到 MongoDB。你真正需要的是 Postgres 的 JSONB。

JSONB：Postgres 中被低估的英雄

JSONB 不只是简单地将 JSON 存储在 Postgres 列中。它是经过优化的 JSON 数据类型，具有强大的功能和性能特点：

  • 二进制存储格式（B 代表 Binary，而不是蜜蜂🐝）
  • 支持 GIN 索引，使查询速度飞快
  • 丰富的原生操作符，让处理 JSON 数据变得简单
  • SQL 的强大功能与 NoSQL 的灵活性完美结合

这就像是 MongoDB 和 Postgres 的优点被融合在了一起，创造出了一个数据库超级英雄。

令人惊叹但鲜为人知的 JSONB 功能

强大的操作符

-- 包含操作符 @>
-- "这个 JSON 是否包含这个结构？"
SELECT * FROM users
WHERE preferences @> '{"theme": "dark"}';

-- 存在操作符 ?
-- "这个键是否存在？"
SELECT * FROM products
WHERE attributes ? 'wireless';

-- 箭头操作符 -> 和 ->>
-- -> 返回 JSON，->> 返回文本
SELECT
  data->>'name' AS name,
  data->'address'->>'city' AS city
FROM users;

-- 路径操作符 #>
-- 深入导航嵌套的 JSON
SELECT * FROM events
WHERE data #> '{user,settings,notifications}' = 'true';

对 JSON 路径进行索引

这才是真正的技术亮点。你可以在 JSON 中的特定路径上创建索引：

-- 为特定字段创建索引
CREATE INDEX idx_user_email ON users ((data->>'email'));

-- 为存在查询创建索引
CREATE INDEX idx_attributes ON products USING GIN (attributes);

-- 为包含查询创建索引
CREATE INDEX idx_preferences ON users USING GIN (preferences);

有了这些索引，你的 JSON 查询性能将大大提升，甚至可能超过某些 NoSQL 解决方案。

JSON 内的全文搜索

这个功能简直令人惊叹：

-- 为 JSON 字段添加全文搜索
CREATE INDEX idx_content_search ON articles
USING GIN (to_tsvector('english', data->>'content'));

-- 强大的搜索查询
SELECT * FROM articles
WHERE to_tsvector('english', data->>'content') @@ plainto_tsquery('postgres jsonb amazing');

实际应用场景

让我们看一些实际应用，展示 JSONB 如何解决真实世界的问题。

用户偏好设置

假设你正在构建一个 SaaS 产品，需要存储用户偏好设置：

-- 混合方法：结构化 + 灵活性
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  preferences JSONB DEFAULT '{}',
  metadata JSONB DEFAULT '{}'
);

-- 插入带偏好设置的用户
INSERT INTO users (email, preferences) VALUES (
  'john@example.com',
  '{
    "theme": "dark",
    "notifications": {
      "email": true,
      "push": false,
      "frequency": "daily"
    },
    "features": {
      "beta": true,
      "advancedAnalytics": false
    }
  }'
);

-- 查询使用暗色主题且开启邮件通知的用户
SELECT email FROM users
WHERE preferences @> '{"theme": "dark", "notifications": {"email": true}}';

-- 更新嵌套的偏好设置
UPDATE users
SET preferences = jsonb_set(
  preferences,
  '{notifications,push}',
  'true'
)
WHERE email = 'john@example.com';

事件日志系统

这是 JSONB 真正闪耀的地方：

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type TEXT NOT NULL,
  user_id UUID,
  occurred_at TIMESTAMPTZ DEFAULT NOW(),
  data JSONB NOT NULL
);

-- 为快速事件类型 + 数据查询创建索引
CREATE INDEX idx_events_type_data ON events (event_type)
WHERE event_type IN ('purchase', 'signup', 'feedback');
CREATE INDEX idx_events_data ON events USING GIN (data);

-- 插入不同类型的事件，每种都有不同的数据结构
INSERT INTO events (event_type, user_id, data) VALUES
('signup', 'user-123', '{
  "source": "google",
  "campaign": "summer-2024",
  "referrer": "blog-post"
}'),
('purchase', 'user-123', '{
  "items": [
    {"sku": "PROD-1", "quantity": 2, "price": 49.99},
    {"sku": "PROD-2", "quantity": 1, "price": 19.99}
  ],
  "discount": "SUMMER20",
  "total": 99.97
}'),
('feedback', 'user-123', '{
  "type": "feature_request",
  "title": "Add dark mode",
  "priority": "high",
  "tags": ["ui", "accessibility"]
}');

-- 查找使用特定折扣码的所有购买
SELECT * FROM events
WHERE event_type = 'purchase'
AND data @> '{"discount": "SUMMER20"}';

-- 计算过去30天的总收入
SELECT SUM((data->>'total')::NUMERIC) AS total_revenue
FROM events
WHERE event_type = 'purchase'
AND occurred_at >= NOW() - INTERVAL '30 days';

动态属性的产品目录

这个例子可能会让 MongoDB 开发者开始反思：

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  attributes JSONB DEFAULT '{}'
);

-- 插入具有完全不同属性的产品
INSERT INTO products (name, price, attributes) VALUES
('iPhone 15', 999.00, '{
  "brand": "Apple",
  "storage": "256GB",
  "color": "Blue",
  "5g": true,
  "screen": {
    "size": "6.1 inches",
    "type": "OLED",
    "resolution": "2532x1170"
  }
}'),
('Nike Air Max', 120.00, '{
  "brand": "Nike",
  "size": "10",
  "color": "Black/White",
  "material": "Mesh",
  "style": "Running"
}'),
('The Pragmatic Programmer', 39.99, '{
  "author": "David Thomas",
  "isbn": "978-0135957059",
  "pages": 352,
  "publisher": "Addison-Wesley",
  "edition": "2nd"
}');

-- 查找所有支持5G的产品
SELECT name, price FROM products WHERE attributes @> '{"5g": true}';

-- 按品牌查找产品
SELECT * FROM products WHERE attributes->>'brand' = 'Apple';

-- 复杂查询：查找屏幕大于6英寸的所有产品
SELECT name, attributes->'screen'->>'size' AS screen_size
FROM products
WHERE (attributes->'screen'->>'size')::FLOAT > 6.0;

JSONB 的最佳应用场景

以下是 JSONB 特别适合的场景：

  1. 用户偏好/设置：每个用户的需求各不相同，不必创建几十个布尔值列。

  2. 事件日志：不同事件类型需要不同的数据结构，JSONB 能完美适应。

  3. 产品目录：不同类型的产品有不同的属性（书籍有ISBN，鞋子有尺码，手机有屏幕分辨率）。

  4. API 响应缓存：直接存储第三方 API 的响应数据。

  5. 表单提交：特别是当你构建允许自定义字段的表单系统时。

  6. 功能标志和配置：

CREATE TABLE feature_flags (
  key TEXT PRIMARY KEY,
  config JSONB
);

INSERT INTO feature_flags VALUES
('new_dashboard', '{
  "enabled": true,
  "rollout_percentage": 25,
  "whitelist_users": ["user-123", "user-456"],
  "blacklist_countries": ["XX"],
  "start_date": "2024-01-01",
  "end_date": null
}');

何时应该使用传统列结构

当然，JSONB 并非万能的。以下情况下，传统的列结构可能更合适：

  1. 外键关系：无法在外键约束中引用 JSONB 字段
  2. 频繁的聚合操作：JSONB 字段上的 SUM、AVG、COUNT 操作速度较慢
  3. 频繁更新单个字段：更新 JSONB 中的一个字段会重写整个 JSON 文档
  4. 严格的类型安全：当你确实需要确保数据类型正确时

最佳实践是采用混合方法：

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),  -- 真正的外键
  total NUMERIC(10,2) NOT NULL,       -- 便于快速聚合
  status TEXT NOT NULL,               -- 用于索引查找
  created_at TIMESTAMPTZ DEFAULT NOW(),
  line_items JSONB,                   -- 灵活的商品详情
  metadata JSONB                      -- 其他所有信息
);

从 MongoDB 迁移到 Postgres/JSONB

如果你决定从 MongoDB 迁移到 Postgres 的 JSONB，这里有一个简单的迁移策略：

# 伪代码示例
import psycopg2
from pymongo import MongoClient

# 连接两个数据库
mongo = MongoClient('mongodb://localhost:27017/')
postgres = psycopg2.connect("postgresql://...")

# 优雅地迁移数据
for doc in mongo.mydb.mycollection.find():
    postgres.execute(
        "INSERT INTO my_table (id, data) VALUES (%s, %s)",
        (str(doc['_id']), Json(doc))
    )

尝试这个神奇查询

创建这个表并运行下面的查询，你会惊讶于 JSONB 的强大：

-- 创建表
CREATE TABLE magic (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- 插入嵌套的复杂数据
INSERT INTO magic (data) VALUES
('{"user": {"name": "Alice", "scores": [10, 20, 30], "preferences": {"level": "expert"}}}'),
('{"user": {"name": "Bob", "scores": [5, 15, 25], "preferences": {"level": "beginner"}}}');

-- 令人惊叹的查询：查找平均分 > 15 且为专家级别的用户
SELECT
  data->'user'->>'name' AS name,
  (SELECT AVG(value::INT) FROM jsonb_array_elements_text(data->'user'->'scores') AS value) AS avg_score
FROM magic
WHERE data @> '{"user": {"preferences": {"level": "expert"}}}'
AND (
  SELECT AVG(value::INT)
  FROM jsonb_array_elements_text(data->'user'->'scores') AS value
) > 15;

JSONB 操作备忘单

-- 操作符
@>  -- 包含
<@  -- 被包含于
?   -- 键存在
?|  -- 任意键存在
?&  -- 所有键存在
||  -- 连接
-   -- 删除键/元素
#-  -- 按路径删除

-- 函数
jsonb_set()         -- 更新路径上的值
jsonb_insert()      -- 在路径上插入值
jsonb_strip_nulls() -- 移除空值
jsonb_pretty()      -- 格式化为人类可读形式
jsonb_agg()         -- 聚合为数组
jsonb_object_agg()  -- 聚合为对象

-- 性能提示
1. 对 @> 和 ? 操作符使用 GIN 索引
2. 对特定字段的 ->> 操作使用 btree 索引
3. 为常见查询使用部分索引
4. 嵌套深度不要超过 3-4 层
5. JSONB 文档大小保持在 1MB 以下

结语：实事求是

我并不是说 MongoDB 不好。它有自己的适用场景。但在选择单独的 NoSQL 数据库之前，值得问问自己：Postgres 的 JSONB 能否满足需求？

很多情况下，答案是肯定的。而且，你还能保留：

  • ACID 事务
  • 需要时的关系查询能力
  • 现有的 Postgres 知识和工具链
  • 减少一个需要管理的数据库系统
  • 成本节约（Postgres 是开源免费的！）

在我的项目中，我们广泛使用 JSONB 来存储用户反馈元数据、自定义字段和集成配置。它既提供了类似 MongoDB 的灵活性，又保留了 Postgres 的可靠性和强大功能。这真的是两全其美的解决方案。

# mongodb # 数据库
Logo
葡萄城开发者空间

葡萄城是专业的软件开发技术和低代码平台提供商，聚焦软件开发技术，以“赋能开发者”为使命，致力于通过表格控件、低代码和BI等各类软件开发工具和服务

更多推荐

cover

C++ 波澜壮阔 40 年：引用、内联函数与现代空指针，效率跃升三基石

avatar 葡萄城开发者空间
cover

Spring Boot 机制五： Bean 生命周期与后置处理器（BeanPostProcessor）源码深度剖析

avatar 葡萄城开发者空间
cover

【Python基础：语法第四课】列表和元组——Python 里的“爱情”：列表善变，元组长情

avatar 葡萄城开发者空间