跳至主要內容

PostgreSQL JSON 能力

SunnyFan大约 13 分钟约 3751 字

PostgreSQL JSON 能力

简介

PostgreSQL 的 JSON/JSONB 能力，让关系型数据库在保留事务一致性和 SQL 表达力的同时，也能处理半结构化数据。JSON 类型保留原始文本格式，JSONB 类型以二进制格式存储并支持索引操作，两者在查询能力和性能上有显著差异。

对于商品扩展属性、动态配置、事件负载、审计快照等场景，JSONB 能显著提升建模灵活性；但如果把它当作"万能字段"，也很容易带来约束缺失、索引困难和查询复杂化问题。核心原则是：核心字段关系化，扩展字段 JSONB 化，不要走极端。

JSON vs JSONB 对比

特性JSONJSONB
存储格式原始文本二进制（解析后的树结构）
写入速度快（不解析）较慢（需要解析）
查询速度慢（每次查询需重新解析）快（预解析，可直接操作）
索引支持不支持 GIN 索引支持 GIN 索引
空白保留保留原始空白和键顺序去除多余空白，不保留键顺序
推荐度仅需存储不需要查询时使用绝大多数场景推荐使用

JSONB 操作符详解

基础操作符

-- 创建测试表
CREATE TABLE product_catalog (
    id              BIGSERIAL PRIMARY KEY,
    sku_code        TEXT NOT NULL UNIQUE,
    product_name    TEXT NOT NULL,
    attributes      JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

-- 插入数据
INSERT INTO product_catalog (sku_code, product_name, attributes) VALUES
('KB001', '机械键盘',
 '{"brand":"Sunny","switch":"red","wireless":false,"price":299,"tags":["gaming","rgb"],"specs":{"layout":"87key","weight":"800g"}}'),
('KB002', '无线键盘',
 '{"brand":"Sunny","switch":"brown","wireless":true,"price":399,"tags":["wireless","office"],"specs":{"layout":"75key","weight":"600g"}}'),
('HP001', '蓝牙耳机',
 '{"brand":"Sunny","battery_hours":24,"wireless":true,"price":199,"tags":["bt","anc"],"specs":{"driver":"40mm","impedance":"32ohm"}}'),
('HP002', '头戴式耳机',
 '{"brand":"Sunny","battery_hours":30,"wireless":true,"price":599,"tags":["bt","hi-fi"],"specs":{"driver":"50mm","impedance":"250ohm"}}');

-- 操作符 -> 和 ->> 的区别
SELECT
    id,
    product_name,
    attributes -> 'brand' AS brand_json,      -- 返回 JSON 类型
    attributes ->> 'brand' AS brand_text,      -- 返回 TEXT 类型
    attributes -> 'specs' AS specs_json,       -- 返回嵌套 JSON
    attributes -> 'specs' ->> 'layout' AS layout  -- 嵌套取值
FROM product_catalog;

-- -> 返回 JSON，可以继续用操作符链式调用
-- ->> 返回 TEXT，用于比较、排序、显示

查询操作符

-- 等值过滤（TEXT 比较）
SELECT id, product_name
FROM product_catalog
WHERE attributes ->> 'brand' = 'Sunny';

-- 类型转换过滤
SELECT id, product_name
FROM product_catalog
WHERE (attributes ->> 'wireless')::boolean = true;

-- 数值范围过滤
SELECT id, product_name, attributes ->> 'price' AS price
FROM product_catalog
WHERE (attributes ->> 'price')::numeric > 300;

-- @> 包含操作符（JSONB 特有）
-- 检查 JSON 文档是否包含另一个 JSON 文档
SELECT id, product_name
FROM product_catalog
WHERE attributes @> '{"tags": ["anc"]}';

SELECT id, product_name
FROM product_catalog
WHERE attributes @> '{"brand": "Sunny", "wireless": true}';

-- ? 操作符：检查键是否存在
SELECT id, product_name
FROM product_catalog
WHERE attributes ? 'battery_hours';

-- ?| 操作符：任一键存在
SELECT id, product_name
FROM product_catalog
WHERE attributes ?| ARRAY['battery_hours', 'switch'];

-- ?& 操作符：所有键都存在
SELECT id, product_name
FROM product_catalog
WHERE attributes ?& ARRAY['brand', 'price', 'tags'];

-- @? 操作符：jsonpath 查询（PostgreSQL 12+）
SELECT id, product_name
FROM product_catalog
WHERE attributes @? '$.tags[*] ? (@ == "rgb")';

-- @@ 操作符：jsonpath 谓词
SELECT id, product_name
FROM product_catalog
WHERE attributes @@ '$.price > 300';

-- jsonpath 高级查询（PostgreSQL 12+）
SELECT id, product_name,
       jsonb_path_query(attributes, '$.specs.*') AS spec_values
FROM product_catalog;

索引策略

GIN 索引

-- GIN 索引：JSONB 查询的基础索引
-- 支持 @>、?、?|、?&、@@ 等操作符
CREATE INDEX idx_product_catalog_attr_gin
ON product_catalog USING GIN (attributes);

-- 默认 GIN 索引使用 jsonb_ops 操作符类
-- 支持 @>、?、?|、?& 但不支持 -> 和 ->> 的快速查找

-- jsonb_path_ops 操作符类（PostgreSQL 12+）
-- 只支持 @> 操作符，但索引更小、查询更快
CREATE INDEX idx_product_catalog_attr_path
ON product_catalog USING GIN (attributes jsonb_path_ops);

-- GIN 索引性能测试
EXPLAIN ANALYZE
SELECT id, product_name
FROM product_catalog
WHERE attributes @> '{"brand": "Sunny"}';
-- 应该看到 Bitmap Index Scan on idx_product_catalog_attr_gin

-- 无索引时走全表扫描
DROP INDEX idx_product_catalog_attr_gin;
EXPLAIN ANALYZE
SELECT id, product_name
FROM product_catalog
WHERE attributes @> '{"brand": "Sunny"}';
-- Seq Scan on product_catalog

表达式索引

-- 表达式索引：针对特定键做精确过滤
-- 适合高频等值查询
CREATE INDEX idx_product_catalog_brand
ON product_catalog ((attributes ->> 'brand'));

CREATE INDEX idx_product_catalog_price
ON product_catalog (((attributes ->> 'price')::numeric));

-- 验证表达式索引
EXPLAIN ANALYZE
SELECT id, product_name
FROM product_catalog
WHERE attributes ->> 'brand' = 'Sunny';
-- Index Scan using idx_product_catalog_brand

EXPLAIN ANALYZE
SELECT id, product_name
FROM product_catalog
WHERE (attributes ->> 'price')::numeric > 300;
-- Index Scan using idx_product_catalog_price

生成列（Generated Columns）+ 索引

-- PostgreSQL 12+ 支持生成列
-- 将 JSONB 中的高频查询字段提取为生成列，再建索引
ALTER TABLE product_catalog
ADD COLUMN brand TEXT GENERATED ALWAYS AS (attributes ->> 'brand') STORED;

ALTER TABLE product_catalog
ADD COLUMN price NUMERIC GENERATED ALWAYS AS ((attributes ->> 'price')::numeric) STORED;

-- 为生成列建索引（比表达式索引更直观）
CREATE INDEX idx_product_catalog_brand_gen ON product_catalog (brand);
CREATE INDEX idx_product_catalog_price_gen ON product_catalog (price);

-- 查询时直接使用生成列
SELECT id, product_name, brand, price
FROM product_catalog
WHERE brand = 'Sunny' AND price > 300;

-- 生成列的优势：
-- 1. 查询语义更清晰（直接用列名而非 JSONB 表达式）
-- 2. 可以作为外键、约束的目标列
-- 3. 执行计划更简单

JSONB 更新操作

-- 局部更新字段（jsonb_set）
-- jsonb_set(target, path, new_value, create_missing)
UPDATE product_catalog
SET attributes = jsonb_set(attributes, '{switch}', '"blue"', true)
WHERE sku_code = 'KB001';
-- 将 switch 从 "red" 改为 "blue"
-- true 表示如果键不存在则创建

-- 嵌套更新
UPDATE product_catalog
SET attributes = jsonb_set(attributes, '{specs,weight}', '"750g"', true)
WHERE sku_code = 'KB001';

-- 增加键值（|| 合并操作符）
UPDATE product_catalog
SET attributes = attributes || '{"layout":"87key","color":"black"}'::jsonb
WHERE sku_code = 'KB001';

-- 删除键（- 操作符）
UPDATE product_catalog
SET attributes = attributes - 'color'
WHERE sku_code = 'KB001';

-- 删除多个键
UPDATE product_catalog
SET attributes = attributes - '{layout,color}'::text[]
WHERE sku_code = 'KB001';

-- 删除嵌套键
UPDATE product_catalog
SET attributes = attributes - '{specs,weight}'::text[]
WHERE sku_code = 'KB001';

-- 数组操作：追加元素
UPDATE product_catalog
SET attributes = jsonb_set(
    attributes,
    '{tags}',
    (attributes -> 'tags') || '"mechanical"'::jsonb
)
WHERE sku_code = 'KB001';

-- 数组操作：删除元素
UPDATE product_catalog
SET attributes = jsonb_set(
    attributes,
    '{tags}',
    (SELECT jsonb_agg(elem)
     FROM jsonb_array_elements(attributes -> 'tags') AS elem
     WHERE elem != '"rgb"')
)
WHERE sku_code = 'KB001';

数组展开与聚合

-- 展开 tags 数组（一对多）
SELECT id,
       product_name,
       jsonb_array_elements_text(attributes -> 'tags') AS tag
FROM product_catalog;
-- 每个产品的每个 tag 都是一行

-- 统计每个 tag 出现的次数
SELECT
    jsonb_array_elements_text(attributes -> 'tags') AS tag,
    COUNT(*) AS product_count
FROM product_catalog
GROUP BY tag
ORDER BY product_count DESC;

-- 将 tags 数组与其他列联合查询
SELECT pc.id, pc.product_name, tag.tag
FROM product_catalog pc,
     LATERAL jsonb_array_elements_text(pc.attributes -> 'tags') AS tag
WHERE tag = 'gaming';

-- jsonb_each：遍历所有键值对
SELECT key, value
FROM jsonb_each(
    '{"brand":"Sunny","price":299,"tags":["gaming","rgb"]}'::jsonb
);

-- jsonb_object_keys：只获取所有键
SELECT jsonb_object_keys(attributes) AS key_name
FROM product_catalog
WHERE id = 1;

JSONB 约束与校验

JSONB 的灵活性是把双刃剑，缺少约束可能导致脏数据。PostgreSQL 提供了多种约束方式：

-- CHECK 约束：确保必需字段存在
ALTER TABLE product_catalog
ADD CONSTRAINT chk_attributes_brand
CHECK (attributes ? 'brand');

ALTER TABLE product_catalog
ADD CONSTRAINT chk_attributes_price
CHECK ((attributes ->> 'price')::numeric > 0);

-- CHECK 约束：确保 tags 是数组
ALTER TABLE product_catalog
ADD CONSTRAINT chk_attributes_tags
CHECK (jsonb_typeof(attributes -> 'tags') = 'array');

-- 使用触发器做更复杂的校验
CREATE OR REPLACE FUNCTION validate_product_attributes()
RETURNS TRIGGER AS $$
BEGIN
    -- 确保 price 是正数
    IF (NEW.attributes ->> 'price') IS NOT NULL THEN
        IF (NEW.attributes ->> 'price')::numeric <= 0 THEN
            RAISE EXCEPTION 'price must be positive';
        END IF;
    END IF;

    -- 确保 brand 不为空字符串
    IF TRIM(NEW.attributes ->> 'brand') = '' THEN
        RAISE EXCEPTION 'brand must not be empty';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_attributes
BEFORE INSERT OR UPDATE ON product_catalog
FOR EACH ROW
EXECUTE FUNCTION validate_product_attributes();

JSONB 高级查询模式

jsonpath 查询（PostgreSQL 12+）

-- jsonpath 语法详解
-- PostgreSQL 12 引入的 SQL/JSON 路径语言，比传统操作符更强大

-- 基础路径表达式
-- $   : 根元素
-- .key : 访问对象字段
-- [*]  : 遍历数组所有元素
-- [0]  : 访问数组第一个元素
-- ?()  : 过滤器表达式
-- @    : 当前元素（在过滤器中使用）

-- 查找 price 大于 300 的产品
SELECT id, product_name, attributes @@ '$.price > 300' AS expensive
FROM product_catalog;

-- 查找 tags 数组中包含 "rgb" 的产品
SELECT id, product_name
FROM product_catalog
WHERE attributes @? '$.tags[*] ? (@ == "rgb")';

-- 提取 specs 中的所有值
SELECT id, product_name,
       jsonb_path_query(attributes, '$.specs.*') AS spec_value
FROM product_catalog;

-- 嵌套条件查询：specs.driver 为 "50mm" 且 price > 400
SELECT id, product_name, attributes
FROM product_catalog
WHERE attributes @@ '$.specs.driver == "50mm" && $.price > 400';

-- 使用 jsonb_path_query_array 聚合结果
SELECT id, product_name,
       jsonb_path_query_array(attributes, '$.tags[*] ? (@ starts with "bt")') AS bt_tags
FROM product_catalog;

-- 使用 .keyvalue() 获取所有键值对
SELECT id,
       kv.key AS attr_key,
       kv.value AS attr_value
FROM product_catalog,
     jsonb_path_query(attributes, '$.keyvalue()') AS kv;

-- 日期时间 jsonpath 查询
SELECT id, product_name
FROM product_catalog
WHERE attributes @@ '$.created_at.datetime() > "2026-01-01".datetime()';

JSONB 窗口函数与分析

-- JSONB 字段参与窗口计算
SELECT id, product_name,
       (attributes ->> 'price')::numeric AS price,
       AVG((attributes ->> 'price')::numeric) OVER (
           PARTITION BY attributes ->> 'brand'
       ) AS avg_price_by_brand,
       RANK() OVER (
           ORDER BY (attributes ->> 'price')::numeric DESC
       ) AS price_rank
FROM product_catalog
WHERE attributes ? 'price';

-- JSONB 数组展开后做聚合统计
WITH expanded_tags AS (
    SELECT id, product_name,
           (attributes ->> 'price')::numeric AS price,
           jsonb_array_elements_text(attributes -> 'tags') AS tag
    FROM product_catalog
)
SELECT tag,
       COUNT(*) AS product_count,
       ROUND(AVG(price), 2) AS avg_price,
       MIN(price) AS min_price,
       MAX(price) AS max_price
FROM expanded_tags
GROUP BY tag
ORDER BY product_count DESC;

-- 使用 JSONB 构建聚合结果
SELECT
    attributes ->> 'brand' AS brand,
    jsonb_agg(DISTINCT jsonb_build_object(
        'name', product_name,
        'price', (attributes ->> 'price')::numeric
    )) AS products
FROM product_catalog
GROUP BY attributes ->> 'brand';

-- 将行数据转为 JSONB（用于 API 返回）
SELECT jsonb_build_object(
    'id', id,
    'name', product_name,
    'sku', sku_code,
    'attrs', attributes
) AS product_json
FROM product_catalog
WHERE id = 1;

-- 批量转为 JSON 数组
SELECT jsonb_agg(
    jsonb_build_object(
        'id', id,
        'name', product_name,
        'price', attributes ->> 'price'
    )
) AS all_products
FROM product_catalog;

JSONB 与事务一致性

-- JSONB 更新的事务安全
-- PostgreSQL 的 MVCC 机制确保 JSONB 更新的原子性

BEGIN;

-- 乐观锁：使用版本号防止并发冲突
UPDATE product_catalog
SET attributes = jsonb_set(
    attributes || '{"version": 2}',
    '{price}',
    to_jsonb((attributes ->> 'price')::numeric + 50)
)
WHERE id = 1
  AND (attributes ->> 'version')::int = 1;
-- 如果 version 不匹配，更新 0 行，应用层重试

COMMIT;

-- JSONB 字段做审计日志
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id BIGINT NOT NULL,
    action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    old_data JSONB,
    new_data JSONB,
    changed_fields JSONB,  -- 只记录变更的字段
    changed_at TIMESTAMP DEFAULT NOW(),
    changed_by TEXT DEFAULT current_user
);

-- 审计触发器
CREATE OR REPLACE FUNCTION audit_product_changes()
RETURNS TRIGGER AS $$
DECLARE
    changed JSONB;
BEGIN
    IF TG_OP = 'UPDATE' THEN
        -- 计算变更字段
        SELECT jsonb_object_agg(key, value)
        INTO changed
        FROM jsonb_each(NEW.attributes)
        WHERE key NOT IN (SELECT key FROM jsonb_each(OLD.attributes))
           OR value != (OLD.attributes -> key);

        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_fields)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', OLD.attributes, NEW.attributes, changed);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', OLD.attributes);
        RETURN OLD;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, action, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', NEW.attributes);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_product
AFTER INSERT OR UPDATE OR DELETE ON product_catalog
FOR EACH ROW EXECUTE FUNCTION audit_product_changes();

JSONB 性能优化

-- 1. TOAST 与大 JSONB 文档
-- PostgreSQL 对大字段使用 TOAST（The Oversized-Attribute Storage Technique）
-- JSONB 超过约 2KB 会被压缩存储，查询时需要解压

-- 查看 TOAST 压缩情况
SELECT id,
       pg_column_size(attributes) AS jsonb_size,
       pg_column_size(attributes::text) AS text_size,
       ROUND(100.0 * pg_column_size(attributes) / NULLIF(pg_column_size(attributes::text), 0), 1) AS compression_pct
FROM product_catalog;

-- 2. 部分索引（只为包含特定键的行建索引）
CREATE INDEX idx_product_wireless_only
ON product_catalog (((attributes ->> 'wireless')::boolean))
WHERE attributes ? 'wireless';

-- 3. 复合索引（JSONB 键 + 普通列）
CREATE INDEX idx_product_brand_created
ON product_catalog ((attributes ->> 'brand'), created_at);

-- 4. 多表达式索引（覆盖多条件查询）
CREATE INDEX idx_product_brand_wireless
ON product_catalog ((attributes ->> 'brand'), ((attributes ->> 'wireless')::boolean));

-- 5. JSONB 查询性能对比
EXPLAIN ANALYZE
-- 无索引：Seq Scan
SELECT * FROM product_catalog WHERE attributes @> '{"brand":"Sunny"}';

-- GIN 索引：Bitmap Index Scan
CREATE INDEX idx_gin_test ON product_catalog USING GIN (attributes);
EXPLAIN ANALYZE
SELECT * FROM product_catalog WHERE attributes @> '{"brand":"Sunny"}';

-- 表达式索引：Index Scan（更精确）
CREATE INDEX idx_expr_brand ON product_catalog ((attributes ->> 'brand'));
EXPLAIN ANALYZE
SELECT * FROM product_catalog WHERE attributes ->> 'brand' = 'Sunny';
JSONB 性能优化要点：
- 单个 JSONB 文档建议不超过 10KB（超过 TOAST 压缩增加查询开销）
- 高频等值查询用表达式索引（->> + 类型转换）
- 包含查询（@>）用 GIN jsonb_path_ops 索引
- 数组包含查询（tags 数组）用 GIN 默认索引
- 生成列 + 普通索引 = 最佳可读性和性能
- 大 JSONB 文档更新会复制整行（MVCC），高频更新场景慎用

JSONB 与其他数据库 JSON 能力对比

JSON 能力对比：
| 特性              | PostgreSQL          | MySQL 8.0          | SQL Server 2019    |
|------------------|--------------------|--------------------|--------------------|
| 数据类型          | JSON / JSONB       | JSON               | NVARCHAR(MAX)      |
| 二进制存储        | JSONB              | 无（始终文本）       | 无                  |
| 索引支持          | GIN / 表达式/生成列 | 函数索引 / 多值索引 | 计算列索引          |
| 路径查询          | jsonpath (PG 12+)  | ->> / JSON_EXTRACT | JSON_VALUE / QUERY |
| 部分更新          | jsonb_set / ||     | JSON_SET           | JSON_MODIFY        |
| 生成列            | 支持               | 支持               | 计算列              |
| 查询性能          | JSONB 最优         | 中等               | 中等               |

PostgreSQL JSONB 的优势：
- JSONB 二进制存储，查询无需重复解析
- GIN 索引对 @> 包含查询有专门优化
- jsonpath 语法最接近 SQL 标准
- 生成列可以无缝桥接关系化与半结构化数据

优点

缺点

总结

PostgreSQL JSONB 最适合处理"扩展性强但又希望保留 SQL 分析能力"的场景。实践中建议把核心主键、状态、时间等关键字段仍然保持关系化，把动态属性、可选属性或快照内容放到 JSONB，这样既灵活又不至于失去治理能力。

关键知识点

  • JSONB 比 JSON 更适合检索、更新和索引
  • -> 返回 JSON，->> 返回 TEXT
  • @> 常用于"包含某些键值"的过滤查询
  • 高频查询的 JSONB 键值通常需要表达式索引或 GIN 索引支持

项目落地视角

  • 商品扩展属性、用户偏好、配置项很适合 JSONB
  • 审计快照、事件载荷也常用 JSONB 保留原始上下文
  • 高频过滤键（如 brand、region、tenant）应单独做表达式索引
  • 核心查询字段仍然建议保留普通列，避免所有条件都压在 JSONB 上

常见误区

  • 为了灵活，把所有字段都塞到 JSONB 中：导致无法建约束、索引效率低、SQL 难维护
  • 不建索引直接拿大 JSONB 做大量过滤：全表扫描性能极差
  • 忽略类型转换：->> 'price' 返回 TEXT，比较前需要 ::numeric 转换
  • 在 JSONB 中保存关键业务字段却缺少约束：导致脏数据无法被发现

进阶路线

  • 深入学习 jsonpath、GIN、表达式索引和部分索引配合方式
  • 结合生成列（generated columns）优化高频 JSONB 键查询
  • 学习 PostgreSQL TOAST、存储布局对大 JSONB 文档的影响
  • 将 JSONB 与逻辑复制、CDC、审计系统结合使用

适用场景

  • 商品扩展属性、动态表单、用户画像
  • 配置中心快照、业务事件明细、审计记录
  • 需要 SQL 能力但字段结构变化较快的业务模型
  • 关系模型与半结构化数据混合存储场景

落地建议

  • 核心字段关系化，扩展字段 JSONB 化，不要走极端
  • 高频查询的 JSONB 键值必须配索引并验证执行计划
  • 为 JSONB 字段建立 schema 约定和字段说明文档
  • 对关键 JSONB 更新逻辑增加测试，防止路径写错或类型漂移

排错清单

  • 检查查询使用的是 -> 还是 ->>，类型是否正确
  • 检查 JSONB 过滤是否命中 GIN/表达式索引
  • 检查 jsonb_set 路径是否正确、是否误覆盖原字段
  • 检查 JSONB 是否承载了本应关系化建模的关键字段

复盘问题

  • 当前模型中的 JSONB 字段，是为了灵活还是因为懒得建模？
  • 哪些 JSONB 键已经变成高频查询条件，是否应提升为普通列？
  • 现有索引是否真的支撑了最常见的 JSONB 查询？
  • 如果要做审计或 BI 分析，JSONB 字段是否足够可解释？

延伸阅读

Copyright © 2026 SunnyFan