JSON数据类型操作：从基础到进阶
(5) feilong.org 修订于2026-07-13 09:30:15 PostgreSQL教程什么是JSON数据类型？
PostgreSQL自版本9.4起引入了json和jsonb两种原生JSON数据类型，用于存储结构化非关系型数据。二者的核心区别在于：json保留原始格式的空白字符和顺序，而jsonb以二进制形式存储并优化查询性能。
基础操作
1. 创建JSON字段
使用jsonb类型创建表时需注意其特性：
|
1 2 3 4 |
CREATE TABLE user_profile ( id SERIAL PRIMARY KEY, data JSONB NOT NULL DEFAULT '{}'::JSONB ); |
此示例定义了一个包含默认空对象的JSONB字段，适用于存储用户配置信息。
2. 插入与更新数据
插入操作支持直接写入JSON结构：
|
1 2 |
INSERT INTO user_profile (data) VALUES ('{"name": "Alice", "age": 30, "email": "alice@example.com"}'::JSONB); |
更新时可使用
|
1 |
->> |
操作符提取字段值：
|
1 2 3 |
UPDATE user_profile SET data = JSONB_SET(data, '{age}', '35'::TEXT) WHERE id = 1; |
3. 查询数据
通过
|
1 |
@> |
运算符实现嵌套查询：
|
1 2 |
SELECT * FROM user_profile WHERE data @> '{"name": "Alice"}'; |
此语句筛选出包含"name"字段为"Alice"的记录，支持路径匹配语法如
|
1 |
'{address,city}' |
。
进阶操作
1. 处理嵌套结构
PostgreSQL提供
|
1 |
-> |
和
|
1 |
#> |
操作符分别获取键值和嵌套字段：
|
1 2 3 |
SELECT data->'name' AS name, data#>> '{address,city}' AS city FROM user_profile; |
此例提取了嵌套在address对象中的city字段，结果为文本类型。
2. 数组操作
JSONB支持数组类型的存储与查询：
|
1 2 3 |
SELECT data->'tags'::TEXT[] AS tags FROM user_profile WHERE data @> '{"tags": [ "tech", "ai" ]}'; |
使用
|
1 |
@> |
运算符匹配包含指定数组元素的记录，适用于标签分类场景。
3. 索引优化
为提升查询效率，可创建GiST索引：
|
1 |
CREATE INDEX idx_user_data ON user_profile USING GIST (data); |
此索引支持
|
1 |
@> |
、
|
1 |
&& |
等运算符，显著加速复杂条件查询的执行速度。
最佳实践
1. 数据一致性：使用jsonb_build_object()函数构建结构化数据，避免直接拼接字符串。
2. 类型转换：通过
|
1 |
::JSONB |
强制类型转换确保数据格式正确性。
3. 性能权衡：对于频繁更新的字段，优先选择jsonb而非json类型。
结语
PostgreSQL的JSON数据类型为混合数据模型提供了强大支持，结合其丰富的操作符和索引机制，开发者可灵活应对从简单键值存储到复杂嵌套结构的场景需求。掌握这些技术不仅提升数据库设计的灵活性，更能充分发挥关系型数据库在现代应用中的价值。
更新网址：https://feilong.org/postgresql-json-operations
最初发布：20260713 09:30:15 feilong.org 于广州
加入收藏夹，查看更方便。