PostgreSQL 中 JSON 与 JSONB 的区别

JSON 和 JSONB 是 PostgreSQL 提供的两种 JSON 数据类型，主要区别在于存储方式和查询性能：

• JSON：存储为文本格式，保留原始输入的空格和键顺序，解析速度较慢但插入速度快。
• JSONB：存储为二进制格式，解析速度快，支持索引，但不保留空格和键顺序。

嵌套查询的区别

JSON 类型的嵌套查询

JSON 类型在查询时需要实时解析，性能较差：

SELECT * FROM table_name 
WHERE json_column->>'nested_key' = 'value';

JSONB 类型的嵌套查询

JSONB 类型支持更高效的查询操作，可以使用操作符和函数：

SELECT * FROM table_name 
WHERE jsonb_column @> '{"nested_key": "value"}';

执行计划分析

无索引情况下的执行计划

对于 JSON 类型：

EXPLAIN ANALYZE SELECT * FROM table_name 
WHERE json_column->>'nested_key' = 'value';

通常会显示全表扫描，性能较差。

对于 JSONB 类型：

EXPLAIN ANALYZE SELECT * FROM table_name 
WHERE jsonb_column @> '{"nested_key": "value"}';

虽然也是全表扫描，但由于二进制格式解析更快，性能优于 JSON。

有索引情况下的执行计划

为 JSONB 创建 GIN 索引：

CREATE INDEX idx_jsonb ON table_name USING GIN (jsonb_column);

再次执行查询并分析执行计划：

EXPLAIN ANALYZE SELECT * FROM table_name 
WHERE jsonb_column @> '{"nested_key": "value"}';

执行计划将显示使用了 GIN 索引，查询性能显著提升。

性能优化建议

1. 对于需要频繁查询的 JSON 数据，优先使用 JSONB 类型。
2. 为 JSONB 列创建 GIN 索引以加速查询。
3. 使用 @> 操作符进行包含查询，效率高于路径查询。
4. 对于大型 JSON 文档，考虑使用 jsonb_path_ops 运算符类创建更高效的索引：

CREATE INDEX idx_jsonb_path ON table_name 
USING GIN (jsonb_column jsonb_path_ops);

实际应用示例

查询嵌套在 JSONB 中的数组元素：

SELECT * FROM table_name 
WHERE jsonb_column @> '{"array_key": [{"id": 1}]}';

提取嵌套值并排序：

SELECT id, jsonb_column->>'name' AS name 
FROM table_name 
ORDER BY jsonb_column->>'created_at';

通过合理选择数据类型和创建适当的索引，可以显著提升 PostgreSQL 中 JSON 数据的查询性能。