PostgreSQL JSON 数据查询教程

PostgreSQL 从 9.2 版本开始支持 JSON 数据类型，并在后续版本中引入了性能更优的 JSONB 类型。这使得 PostgreSQL 在保持关系型数据库强大事务能力的同时，也能灵活处理半结构化数据。本文将在搬瓦工 VPS 上详细讲解 PostgreSQL JSON/JSONB 的存储、查询、索引和实际应用，帮助你在一个数据库中同时处理结构化和非结构化数据。

一、JSON 与 JSONB 的区别

• JSON：以文本形式存储，保留原始格式（包括空格和键顺序）。每次查询都需要重新解析，查询性能较差。
• JSONB：以二进制格式存储，不保留空格和键顺序，但支持索引、查询速度更快。写入时有额外的解析开销。

推荐：绝大多数场景使用 JSONB，除非需要保留 JSON 原始格式。

二、创建包含 JSONB 的表

-- 创建产品表，属性用 JSONB 存储
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2),
    attrs JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入示例数据
INSERT INTO products (name, category, price, attrs) VALUES
('ThinkPad X1 Carbon', '笔记本', 9999.00, '{
    "brand": "Lenovo",
    "cpu": "i7-1365U",
    "ram": "16GB",
    "storage": "512GB SSD",
    "screen": "14英寸",
    "weight": 1.12,
    "ports": ["USB-C", "USB-A", "HDMI", "耳机口"],
    "features": {"fingerprint": true, "backlit_keyboard": true}
}'),
('MacBook Air M3', '笔记本', 8999.00, '{
    "brand": "Apple",
    "cpu": "M3",
    "ram": "16GB",
    "storage": "256GB SSD",
    "screen": "13.6英寸",
    "weight": 1.24,
    "ports": ["MagSafe", "USB-C", "耳机口"],
    "features": {"touch_id": true, "backlit_keyboard": true}
}'),
('iPhone 15 Pro', '手机', 7999.00, '{
    "brand": "Apple",
    "cpu": "A17 Pro",
    "ram": "8GB",
    "storage": "256GB",
    "screen": "6.1英寸",
    "weight": 0.187,
    "colors": ["原色钛金属", "蓝色钛金属", "白色钛金属"],
    "features": {"face_id": true, "wireless_charging": true}
}');

三、基础查询操作符

3.1 提取 JSON 值

-- -> 操作符：提取 JSON 对象字段，返回 JSON 类型
SELECT name, attrs->'brand' AS brand FROM products;

-- ->> 操作符：提取 JSON 对象字段，返回文本类型
SELECT name, attrs->>'brand' AS brand FROM products;

-- #> 操作符：按路径提取，返回 JSON
SELECT name, attrs#>'{features,fingerprint}' FROM products;

-- #>> 操作符：按路径提取，返回文本
SELECT name, attrs#>>'{features,fingerprint}' FROM products;

3.2 条件查询

-- 查询特定品牌的产品
SELECT name, price FROM products WHERE attrs->>'brand' = 'Apple';

-- 查询 RAM 为 16GB 的产品
SELECT name, price FROM products WHERE attrs->>'ram' = '16GB';

-- 数值比较（需要类型转换）
SELECT name, price FROM products WHERE (attrs->>'weight')::numeric < 1.2;

-- @> 包含操作符：检查 JSONB 是否包含指定内容
SELECT name FROM products WHERE attrs @> '{"brand": "Apple"}';

-- ? 存在操作符：检查键是否存在
SELECT name FROM products WHERE attrs ? 'colors';

-- ?| 任一键存在
SELECT name FROM products WHERE attrs ?| array['colors', 'ports'];

-- ?& 所有键都存在
SELECT name FROM products WHERE attrs ?& array['cpu', 'ram', 'storage'];

3.3 数组操作

-- 查询数组元素
SELECT name, attrs->'ports'->0 AS first_port FROM products;

-- 展开 JSON 数组
SELECT name, jsonb_array_elements_text(attrs->'ports') AS port
FROM products WHERE attrs ? 'ports';

-- 查询数组中包含特定元素的记录
SELECT name FROM products WHERE attrs->'ports' ? 'HDMI';

四、JSONB 修改操作

-- 添加或更新键值
UPDATE products SET attrs = attrs || '{"warranty": "3年"}' WHERE id = 1;

-- 删除键
UPDATE products SET attrs = attrs - 'warranty' WHERE id = 1;

-- 按路径删除嵌套键
UPDATE products SET attrs = attrs #- '{features,fingerprint}' WHERE id = 1;

-- 按路径设置值（PostgreSQL 14+）
UPDATE products SET attrs = jsonb_set(attrs, '{ram}', '"32GB"') WHERE id = 1;

-- 在数组中追加元素
UPDATE products SET attrs = jsonb_set(
    attrs,
    '{ports}',
    (attrs->'ports') || '"SD卡槽"'
) WHERE id = 1;

五、JSONB 索引优化

JSONB 支持 GIN 索引，可以大幅提升查询性能。

5.1 通用 GIN 索引

-- 创建 GIN 索引（支持 @>、?、?|、?& 操作符）
CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

-- 支持 jsonb_path_ops 模式（更小更快，仅支持 @> 操作符）
CREATE INDEX idx_products_attrs_path ON products USING GIN (attrs jsonb_path_ops);

5.2 针对特定键的索引

-- 为特定字段创建 B-tree 索引
CREATE INDEX idx_products_brand ON products ((attrs->>'brand'));

-- 为数值字段创建索引
CREATE INDEX idx_products_weight ON products (((attrs->>'weight')::numeric));

5.3 索引效果验证

-- 使用 EXPLAIN ANALYZE 验证索引是否被使用
EXPLAIN ANALYZE SELECT * FROM products WHERE attrs @> '{"brand": "Apple"}';
EXPLAIN ANALYZE SELECT * FROM products WHERE attrs->>'brand' = 'Lenovo';

六、JSON 聚合与构造

-- 构建 JSON 对象
SELECT jsonb_build_object('name', name, 'price', price, 'brand', attrs->>'brand')
FROM products;

-- 聚合为 JSON 数组
SELECT category, jsonb_agg(jsonb_build_object('name', name, 'price', price)) AS products_list
FROM products GROUP BY category;

-- 将行转换为 JSON
SELECT to_jsonb(p.*) FROM products p WHERE id = 1;

-- JSON 键值对展开
SELECT key, value FROM products, jsonb_each(attrs) WHERE id = 1;

-- 仅展开文本值
SELECT key, value FROM products, jsonb_each_text(attrs) WHERE id = 1;

七、JSONPath 查询（PostgreSQL 12+）

-- 使用 JSONPath 查询
SELECT name FROM products WHERE attrs @? '$.features.face_id';

-- 带条件的 JSONPath
SELECT name, price FROM products
WHERE attrs @@ '$.weight < 1.2';

-- jsonb_path_query 提取数据
SELECT name, jsonb_path_query(attrs, '$.ports[*]') AS port
FROM products WHERE id = 1;

-- jsonb_path_exists 检查路径是否存在
SELECT name FROM products
WHERE jsonb_path_exists(attrs, '$.features.wireless_charging');

八、实际应用场景

8.1 用户配置存储

CREATE TABLE user_settings (
    user_id INTEGER PRIMARY KEY,
    settings JSONB DEFAULT '{
        "theme": "light",
        "language": "zh-CN",
        "notifications": {"email": true, "push": true},
        "privacy": {"profile_public": false}
    }'
);

-- 更新单个设置
UPDATE user_settings
SET settings = jsonb_set(settings, '{theme}', '"dark"')
WHERE user_id = 1;

8.2 日志事件存储

CREATE TABLE event_logs (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_events_type_time ON event_logs (event_type, created_at);
CREATE INDEX idx_events_payload ON event_logs USING GIN (payload);

-- 查询特定用户的登录事件
SELECT * FROM event_logs
WHERE event_type = 'login' AND payload @> '{"user_id": 12345}'
ORDER BY created_at DESC LIMIT 10;

九、性能注意事项

• 避免超大 JSONB 文档：单个 JSONB 字段不建议超过几 MB，否则会影响 TOAST 存储和查询性能。
• 合理使用索引：GIN 索引会增加写入开销，只为高频查询的字段建索引。
• 关系列与 JSONB 列互补：频繁用于 WHERE、JOIN、ORDER BY 的字段应提取为独立列，灵活可变的属性放入 JSONB。
• 批量更新：频繁的部分更新（jsonb_set）会导致整行重写，批量场景考虑缓冲后一次写入。

总结

PostgreSQL 的 JSONB 功能让你在关系型数据库中优雅地处理半结构化数据，避免了同时维护 SQL 数据库和 NoSQL 数据库的复杂性。搭配 GIN 索引和 JSONPath，JSONB 查询性能完全可以满足生产需求。更多 PostgreSQL 教程可参考 流复制配置 和 全文搜索。选购搬瓦工 VPS 请查看 全部方案，使用优惠码 NODESEEK2026 享受 6.77% 折扣，通过 bwh81.net 进入官网。