第十一章文档型数据库风格操作 - 第三节实战：电商商品信息灵活字段管理 - B40yd

第三节实战：电商商品信息灵活字段管理

目标：应用本章学习的混合 Schema 设计模式和 jsonpath 查询技术，为一个电商平台设计一个既能支持高效核心查询，又能灵活存储不同品类商品独特属性的 products 表。

场景分析

电商平台的商品信息是典型的“半结构化”数据。所有商品都有一些共同的核心属性，如名称（name）、价格（price）、库存（stock）。但不同品类的商品又有其独特的属性：

服装：有 color (颜色), size (尺码), material (材质)。
电子产品：有 brand (品牌), specs (规格，如 CPU、内存), warranty_period (保修期)。
书籍：有 author (作者), publisher (出版社), isbn (国际标准书号)。

使用传统的关系模型，我们可能需要为每个品类创建一张独立的表，或者创建一个包含大量 NULL 列的“超级大表”，这两种方案都缺乏灵活性和可扩展性。

🏛️ 第一步：采用混合 Schema 设计

我们遵循本章介绍的最佳实践，设计一个 products 表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    -- 核心、通用、需要频繁过滤和排序的字段
    name TEXT NOT NULL,
    category TEXT NOT NULL, -- 商品品类
    price NUMERIC(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    -- 动态、品类特有的属性
    attributes JSONB
);

-- 为核心字段创建 B-Tree 索引
CREATE INDEX ON products(category);
CREATE INDEX ON products(price);

-- 为 JSONB 属性字段创建 GIN 索引
CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes);

✍️ 第二步：插入不同品类的商品数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 插入一件T恤
INSERT INTO products (name, category, price, stock, attributes) VALUES
('Classic Cotton T-Shirt', 'Apparel', 29.99, 100,
 '{
    "brand": "BasicWear",
    "colors": ["Red", "Blue", "Black"],
    "sizes": ["S", "M", "L"],
    "material": "100% Cotton"
 }'
);

-- 插入一台笔记本电脑
INSERT INTO products (name, category, price, stock, attributes) VALUES
('ProBook X1', 'Electronics', 1199.00, 50,
 '{
    "brand": "TechCorp",
    "specs": {
        "cpu": "Intel i7",
        "ram_gb": 16,
        "storage_gb": 512
    },
    "warranty_period_months": 12
 }'
);

-- 插入一本书
INSERT INTO products (name, category, price, stock, attributes) VALUES
('The Art of SQL', 'Books', 49.50, 200,
 '{
    "author": "Jane Smith",
    "publisher": "DB Publishing",
    "isbn": "978-3-16-148410-0",
    "pages": 450
 }'
);

🔍 第三步：执行多维度混合查询

现在，我们可以结合使用标准 SQL 和 jsonpath 来执行复杂的商品筛选。

查询 1：查找所有价格低于 50 美元的书籍

这个查询可以高效地利用 category 和 price 字段上的 B-Tree 索引。

1
2
3
SELECT name, price, attributes->>'author' AS author
FROM products
WHERE category = 'Books' AND price < 50.00;

查询 2：查找所有 “TechCorp” 品牌的电子产品

这个查询会先用 B-Tree 索引筛选 category，然后在结果集上用 GIN 索引高效匹配 attributes。

1
2
3
4
5
SELECT name, price, attributes->'specs' AS specifications
FROM products
WHERE
    category = 'Electronics'
    AND attributes @> '{"brand": "TechCorp"}';

查询 3：查找所有提供 “Red” 色的服装

这是一个 JSONB 数组的查询。使用 @> 同样高效。

1
2
3
4
5
SELECT name, price
FROM products
WHERE
    category = 'Apparel'
    AND attributes @> '{"colors": ["Red"]}';

查询 4：使用 `jsonpath` 查找所有内存大于等于 16GB 的电脑

当需要对 JSONB 内部的数值进行比较时，jsonpath 就派上了用场。

1
2
3
4
5
SELECT name, price, attributes->'specs'
FROM products
WHERE
    category = 'Electronics'
    AND jsonb_path_exists(attributes, '$.specs ? (@.ram_gb >= 16)');

$.specs: 导航到 specs 对象。
?(): 应用过滤器。
@.ram_gb >= 16: 检查 ram_gb 属性是否大于等于 16。

🔄 第四步：更新动态属性

JSONB 的灵活性在更新商品属性时体现得淋漓尽致。

场景：为 “ProBook X1” 添加一个新的 `ports` 属性

我们无需 ALTER TABLE，只需一个 UPDATE 语句和 || 合并操作符。

1
2
3
UPDATE products
SET attributes = attributes || '{"ports": ["USB-C", "HDMI", "USB-A"]}'::jsonb
WHERE product_id = 2;

场景：将所有 “BasicWear” 品牌服装的材质更新为 “Organic Cotton”

使用 jsonb_set 函数进行深度更新。

1
2
3
4
5
UPDATE products
SET attributes = jsonb_set(attributes, '{material}', '"Organic Cotton"')
WHERE
    category = 'Apparel'
    AND attributes @> '{"brand": "BasicWear"}';

📌 小结

本实战案例完美地展示了 PostgreSQL 混合数据模型的威力：

结构与灵活的平衡：通过将核心字段和动态属性分离，我们设计了一个既稳健又可扩展的商品数据模型。
查询能力的融合：我们能够无缝地结合使用 B-Tree 索引（用于关系列）和 GIN 索引（用于 JSONB 列），实现高效的多维度查询。
jsonpath 的价值：对于 JSONB 内部的复杂逻辑判断（如数值比较），jsonpath 提供了简洁而强大的解决方案。
维护的便捷性：添加或修改非核心属性变得异常简单，极大地提升了业务迭代的敏捷性。

这种设计模式是 PostgreSQL 作为多模型数据库强大能力的集中体现，非常适合电商、内容管理、物联网等需要处理大量半结构化数据的现代应用。

文章目录

第三节 实战：电商商品信息灵活字段管理