What is JSONB in PostgreSQL?

PostgreSQL 是一个强大的对象关系数据库管理系统，擅长处理结构化和半结构化数据，尤其是在支持 JSONB 方面。JSONB（二进制 JSON）允许高效地存储和查询 JSON 数据，使其成为需要快速访问结构化信息的应用程序的理想选择。

在本文中，我们将详细解释 JSONB，说明如何操作它，并通过实际示例解释 JSONB 在 PostgreSQL 中的重要性。

关于 JSON 请看: JavaScript JSON

什么是 JSONB？

• 二进制优化格式：JSONB（Binary JSON）是 PostgreSQL 中 JSON 数据的二进制表示形式，不保留空格、键顺序和重复键（仅保留最后一次出现的值）。
• vs 标准 JSON 类型：
  • json：存储原始文本，保留格式细节（如空格、键顺序），但每次查询需重新解析，性能较低。
  • jsonb：预解析为二进制，查询速度更快，支持索引，适合生产环境（除非需严格保留原始 JSON 格式）。
• 核心优势：
  1. Speed：查询和修改操作更快。
  2. Indexing：支持 GIN 索引，提升复杂查询性能。尤其是数据集较大的情况。
  3. Efficient Storage： 以二进制形式存储，优化数据的空间利用率。
  4. Flexible Querying：支持丰富的操作符和函数，便于处理嵌套数据结构。

创建和插入 JSONB 数据

让我们创建一个使用 JSONB 管理产品信息的数据库。你可以使用 ::jsonb 类型转换将数据插入 JSONB 列。以下是向 products 表插入产品信息的方法。 name 列存储值 ’ Smartphone ’ 。

CREATE TABLE products (
    id serial PRIMARY KEY,
    name text,
    details jsonb
);

INSERT INTO products (name, details)
VALUES
    ('Smartphone', '{"features": {"camera": "12MP", "battery": "4000mAh"}, "tags": ["electronics", "mobile"]}'::jsonb);

输出：

• 这个示例生成了一个包含产品信息的 products 表，包含三个列：id、name 和 details（JSONB 类型）。
• details 列的类型为 JSONB，存储有关产品功能和标签的嵌套 JSON 数据。
• 插入数据时，使用 ::jsonb 将 JSON 字符串转换为 JSONB 类型。
• 运行上述 SQL 语句后，PostgreSQL 将将产品信息插入表中，并返回 INSERT 0 1，确认已添加一行。

关键操作符与函数

JSON 和 JSONB 在 PostgreSQL 中支持多种函数和操作符，以执行多种操作和功能。一些常见操作符包括：

💡 注意：@> 支持深度嵌套查询（如 {"tags": [{"term":"paris"}]}），而 ? 仅检查顶层键/元素。

性能优化：GIN 索引

JSONB 的核心性能依赖 GIN 索引，两种操作符类：

创建索引示例：

-- 默认操作符类
CREATE INDEX idx_default ON products USING GIN (details);

-- 优化路径查询（推荐多数场景）
CREATE INDEX idx_path ON products USING GIN (details jsonb_path_ops);

优化技巧：

• 针对特定路径（如 details->'tags'）创建表达式索引：

  CREATE INDEX idx_tags ON products USING GIN ((details->'tags'));

• 避免对大型 JSONB 文档频繁更新（行锁开销大），建议单文档 < 100KB。

最佳实践

• 何时用 JSONB：
  • 需动态模式（如用户自定义字段、传感器数据）。
  • 高频查询嵌套数据（如 WHERE details->'features'->'camera' = '12MP'）。
• 何时避免：
  • 需严格保留 JSON 原始格式（用 json 类型）。
  • 全文检索需求（结合 tsvector 或专用搜索引擎）。
• 设计建议：
  • 保持文档结构相对固定（如约定顶层键 features/tags）。
  • 用 jsonb_path_ops 索引替代默认索引（除非需 ? 操作符）。
  • 避免存储超大 JSON（>1MB），拆分为关联表更高效。

高级特性：jsonpath

• SQL/JSON 路径表达式：支持 JavaScript 风格语法（如 $.features.camera）。
• 关键能力：
  • 递归搜索：.**{level}（如 $.**{1 to 2}.port 搜索 1~2 层嵌套的 port 字段）。
  • 过滤条件：@? '$.tags[*] ? (@ == "USB-C")'。
• 索引支持：GIN 索引自动优化 @?/@@ 操作符。

🌰 示例：

SELECT * FROM products 
WHERE details @@ '$.features.ports[*] == "USB-C"'; -- 比 @> 更灵活

在 PostgreSQL 18 和 PostgreSQL 12 中的区别

下标操作支持

PostgreSQL 18 新增了完整的 jsonb 下标操作支持，允许使用类似数组的语法直接访问和修改 jsonb 对象：

-- 提取对象值
SELECT ('{"a": 1}'::jsonb)['a'];

-- 修改 jsonb 值
UPDATE table_name SET jsonb_field['key'] = '1';

-- 嵌套访问
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

PostgreSQL 12 完全没有此功能，这是两个版本间最显著的区别。

PL/Python 支持

• PostgreSQL 18 仅支持 jsonb_plpython3u 扩展
• PostgreSQL 12 支持多个版本：jsonb_plpythonu、jsonb_plpython2u 和 jsonb_plpython3u

这表明 PG 18 已完全放弃对 Python 2 的支持，只专注于 Python 3

Unicode 处理

• PostgreSQL 18 引入了对非 UTF8 数据库中 Unicode 转义（\uXXXX）的支持
• PostgreSQL 12 在非 UTF8 数据库中不支持此功能

索引优化方面

• PostgreSQL 18 对 GIN 索引的构建和查询性能进行了优化
• PostgreSQL 12 的 GIN 索引性能相对较低

总结

JSONB 是 PostgreSQL 处理半结构化数据的王牌：

• ✅ 性能碾压 json：二进制存储 + GIN 索引（尤其 jsonb_path_ops）。
• ✅ 查询能力强大：@> 深度包含检查 + jsonpath 复杂路径过滤。
• ✅ 生产就绪：适合 API 后端、日志分析、动态表单等场景。

避坑提示：

1. 非 UTF8 数据库慎用 Unicode 转义（\uXXXX）。
2. 超大数字可能被截断（jsonb 用 numeric 类型存储，范围有限）。
3. 索引选择需权衡：jsonb_path_ops 性能更优，但不支持 ? 操作符。

官方文档是终极指南：PostgreSQL JSONB 文档（重点阅读 8.14.3 节与 8.14.4 节）。

适用场景：微服务配置中心、IoT 传感器数据、电商商品属性、实时日志分析。
性能基准：在 1000 万行数据中，GIN 索引将 @> 查询从秒级降至毫秒级。

参考资料

• Data Types - JSON Types - PostgreSQL 18 Documentation

• Data Types - JSON Types - PostgreSQL 12 Documentation(unsupported - version)

• What is JSONB in PostgreSQL? - GeeksforGeeks

• RFC 7159 - The JavaScript Object Notation (JSON) Data Interchange Format