【postgresql】JSONB类型：JSON存储和操作的神兵利器

_BugMan

543人浏览 · 2026-03-26 10:59:17

_BugMan · 2026-03-26 10:59:17 发布

1.简介

JSON是个字符串，字符串天生就存在一个问题：

不好灵活的对其中内容进行各种操作。

但在实际应用中又有很多场景需要对JSON的内容进行各种操作，比如：

访问/删除/新增某个键值
判断某个键值是否存在
等等…

要是对于一个字符串来说，要实现上面各种花式操作，就只有去遍历，然后字符匹配，然后操作，这明显又慢又复杂，JSONB就是postgresql给出的高效且便捷操作JSON的方案。

2.基本操作

数据准备：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (name, data) VALUES 
('张三', '{
    "age": 25,
    "email": "zhangsan@example.com",
    "status": "active",
    "address": {
        "city": "北京",
        "district": "朝阳",
        "zip": "100000"
    },
    "tags": ["developer", "python", "postgresql"],
    "orders": [
        {"order_id": 1, "amount": 100, "status": "completed"},
        {"order_id": 2, "amount": 250, "status": "pending"}
    ],
    "preferences": {
        "newsletter": true,
        "notifications": false
    }
}'::jsonb),
('李四', '{
    "age": 30,
    "email": "lisi@example.com",
    "status": "active",
    "address": {
        "city": "上海",
        "district": "浦东",
        "zip": "200000"
    },
    "tags": ["designer", "ui", "ux"],
    "orders": [
        {"order_id": 3, "amount": 500, "status": "completed"}
    ],
    "preferences": {
        "newsletter": true,
        "notifications": true
    }
}'::jsonb),
('王五', '{
    "age": 28,
    "status": "inactive",
    "address": {
        "city": "广州",
        "district": "天河"
    },
    "tags": ["manager", "sales"],
    "orders": [],
    "preferences": {
        "newsletter": false,
        "notifications": true
    }
}'::jsonb),
('赵六', '{
    "age": 35,
    "email": "zhaoliu@example.com",
    "status": "vip",
    "address": {
        "city": "深圳",
        "district": "南山",
        "zip": "518000"
    },
    "tags": ["entrepreneur", "tech", "ai"],
    "orders": [
        {"order_id": 4, "amount": 1000, "status": "completed"},
        {"order_id": 5, "amount": 2000, "status": "completed"},
        {"order_id": 6, "amount": 500, "status": "refunded"}
    ],
    "preferences": {
        "newsletter": true,
        "notifications": false
    }
}'::jsonb);

查询操作：

操作描述	SQL 示例
使用 `->` 操作符获取字段 (返回 JSONB)	`SELECT id, data->'name' as name_json FROM users;`
使用 `->>` 操作符获取字段 (返回文本)	`SELECT id, data->>'age' as age_text FROM users;`
获取嵌套对象	`SELECT id, data->'address'->>'city' as city FROM users;`
使用 `#>>` 获取深层嵌套的内容	`SELECT id, data#>>'{address,district}' as district FROM users;`
WHERE 条件查询	`SELECT * FROM users WHERE data->>'status' = 'active';`
数值比较查询	`SELECT * FROM users WHERE (data->>'age')::int > 28;`
包含查询 (`@>`)	`SELECT * FROM users WHERE data @> '{"status": "active"}';`
键存在性查询 (`?`)	`SELECT * FROM users WHERE data ? 'email';`
所有键都存在 (`?&`)	`SELECT * FROM users WHERE data ?& array['name', 'email', 'age'];`
任意键存在 (`?\|`)	`SELECT * FROM users WHERE data ?\| array['phone', 'email'];`
访问数组第一个元素	`SELECT id, data->'tags'->0 as first_tag FROM users;`
获取数组长度	`SELECT id, jsonb_array_length(data->'orders') as order_count FROM users;`
展开数组为多行	`SELECT id, jsonb_array_elements(data->'tags') as tag FROM users;`
带序号展开数组

修改操作：

操作描述	SQL 示例
合并 JSONB 对象	`UPDATE users SET data = data \|\| '{"updated": true}'::jsonb WHERE id = 1;`
删除指定键	`UPDATE users SET data = data - 'updated' WHERE id = 1;`
删除嵌套字段	`UPDATE users SET data = data #- '{address,zip}' WHERE id = 2;`
更新字段值	`UPDATE users SET data = jsonb_set(data, '{age}', '26') WHERE id = 1;`
更新嵌套字段值	`UPDATE users SET data = jsonb_set(data, '{address,city}', '"杭州"') WHERE id = 1;`
追加标签到数组

聚合操作：

操作描述	SQL 示例
聚合所有用户数据	`SELECT jsonb_agg(data) as all_users FROM users;`
按状态聚合	`SELECT data->>'status' as status, jsonb_agg(data) as users FROM users GROUP BY data->>'status';`
创建键值对对象	`SELECT jsonb_object_agg(name, data->>'email') as user_emails FROM users WHERE data ? 'email';`
统计每个城市的用户数

常用函数：

操作描述	SQL 示例
检查字段类型	`SELECT id, jsonb_typeof(data->'age') as age_type, jsonb_typeof(data->'tags') as tags_type, jsonb_typeof(data->'orders') as orders_type FROM users;`
展开 JSONB 对象为键值对	`SELECT key, value FROM jsonb_each((SELECT data FROM users WHERE id = 1));`
获取所有键	`SELECT jsonb_keys(data) as field_names FROM users WHERE id = 1;`
移除 NULL 值	`SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": 3}'::jsonb) as cleaned;`
JSONPath 查询 - 查找年龄大于 20 的用户	`SELECT * FROM users WHERE jsonb_path_exists(data, '$.age ? (@ > 20)');`
JSONPath 查询 - 获取所有订单金额	`SELECT id, jsonb_path_query(data, '$.orders[*].amount') as amounts FROM users;`
JSONB 转记录

3.数据结构

JSONB就是postgresql给出来的对JSON高效操作的解决方案。
Postgresql JsonB=为速度而生的JSON类型。
postgresql solution：

organize data in a tree
sort to improve query speed
use binary encoding to compress space

json本身就是个树状结构：

在这里插入图片描述

所以只要用树形结构来存json就能很方便对其进行各种操作，postgresql就是用N叉树来组织JSONB这种数据类型的。除了用树形结构来组织JSON数据，postgresql还用排序和压缩来加快了查询速度：

排序，将键值按顺序排序，这样就可以用二分查找来快速定位键值的位置的
压缩，将排序好的数据进行压缩，减少数据占用的内存空间

整个JSONB的底层结构长这样：

-- 创建一个简单的 JSONB 数据
SELECT '{
    "name": "iPhone",
    "price": 5999,
    "tags": ["electronics", "apple"],
    "specs": {
        "color": "black",
        "storage": 128
    }
}'::jsonb;

在这里插入图片描述

其实上面的图就是想说JSONB的结构就是分为三块：

整体信息
位置信息（偏移量+数据类型描述）
真实数据存储区

4.索引

索引是为了快速查找而维系的一种数据结构，要快速查找键值对，会想到什么？是的，ES的倒排索引，postgresql就是采用类"倒排索引"的思想，PostgreSQL 中 JSONB 的索引主要依赖GIN (Generalized Inverted Index，通用倒排索引)。如果了解ES的倒排索引，这就没什么好说的。

在这里插入图片描述

整个过程就是，当为 JSONB 创建 GIN 索引时，PostgreSQL 会：

提取元素：将 JSONB 分解为可索引的元素
生成键：为每个元素创建索引键
建立倒排：元素 → 行ID 的映射
创建索引：

-- 创建 GIN 索引
CREATE INDEX idx_products_attributes ON products USING gin(attributes);

元素提取规则：

-- 对于 JSONB 值：{"brand": "Apple", "price": 5999, "tags": ["electronics", "mobile"]}

-- GIN 索引会提取以下可索引元素：
-- 1. 路径值：'Apple' (来自 brand 字段)
-- 2. 数值：'5999' (作为字符串)
-- 3. 数组元素：'electronics', 'mobile'
-- 4. 键名：'brand', 'price', 'tags' (如果使用 jsonb_ops)

-- 不同的操作符类决定提取哪些元素