postgresql json 使用入门
2019-01-03 21:55:58 
阿炯
json与jsonb区别而两者的主要区别在于效率，json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等；而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同，使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，查询慢，jsonb类型存储稍慢，查询较快(支持许多额外的操作符)。这里主要测试jsonb的增删改查。
json和jsonb共同操作符
| 操作符 | 返回类型 | 数组[1,2,3] | {"a":1,"b":2,"c":3} | {"a":{"b":{"c":1}},"d":[4,5,6]} |
|---|---|---|---|---|
| -> | json | select '[1,2,3]'::jsonb ->2 = 3 | select '{"a":1,"b":2,"c":3}'::jsonb-> 'a'=1 | select '{"a":{"b":{"c":1}},"d":[4,5,6]}'::jsonb ->'a'={"b": {"c": 1}} |
| ->> | text | select '[1,2,3]'::jsonb ->>2 = 3 | select '{"a":1,"b":2,"c":3}'::jsonb->> 'a'=1 | select '{"a":{"b":{"c":1}},"d":[4,5,6]}'::jsonb ->>'a'={"b": {"c": 1}} |
| #> | json | -- | -- | select '{"a":{"b":{"c":1}},"d":[4,5,6]}'::jsonb #> '{a,b}' ={"c": 1} |
| #>> | text | -- | -- | select '{"a":{"b":{"c":1}},"d":[4,5,6]}'::jsonb #> '{a,b}' ={"c": 1} |
jsonb额外操作符
| 操作符 | 右操作数类型 | 描述 | 例子 |
|---|---|---|---|
| @> | jsonb | 左边的 JSON 值是否包含顶层右边JSON路径/值项? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
| <@ | jsonb | 左边的JSON路径/值是否包含在顶层右边JSON值中？ | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
| ? | text | 字符串是否作为顶层键值存在于JSON值中？ | '{"a":1, "b":2}'::jsonb ? 'b' |
| ?| | text[] | 这些数组字符串中的任何一个是否作为顶层键值存在？ | '{"a":1, "b":2, "c":3}'::jsonb ?|array['b',c'] |
| ?& | text[] | 这些数组字符串是否作为顶层键值存在？ | '["a", "b"]'::jsonb ?& array['a', 'b'] |
| || | jsonb | 连接两个jsonb值到新的jsonb值 | '["a", "b"]'::jsonb|| '["c", "d"]'::jsonb |
| - | text | 从左操作数中删除键/值对或字符串元素。基于键值匹配键/值对。 | '{"a": "b"}'::jsonb - 'a' |
| - | integer | 删除指定索引的数组元素（负整数结尾）。如果顶层容器不是一个数组，那么抛出错误。 | '["a", "b"]'::jsonb - 1 |
| #- | text[] | 删除指定路径的域或元素（JSON数组，负整数结尾） | '["a", {"b":1}]'::jsonb #- '{1,b}' |
jsonb增删改
--1.1建表
freeoa=> create table test_jsonb(c_bh char(32),j_jsonb jsonb);
CREATE TABLE
--插入数据
insert into test_jsonb(c_bh,j_jsonb) values(replace(uuid_generate_v4()::text,'-',''),'{"c_xm":"张三","c_mx":{"c_ssdw":"一大队","c_dwbm":"11"}}');
INSERT 0 1
--查看数据
freeoa=# select * from test_jsonb where j_jsonb @> '{"c_xm":"张三","c_mx":{"c_ssdw":"一大队","c_dwbm":"11"}}';
c_bh | j_jsonb
----------------------------------+--------------------------------------------
c217 | {"c_mx": {"c_dwbm": "11", "c_ssdw": "一大队"}, "c_xm": "张三"}
--1.2操作符||可用于添加元素，添加元素'{"c_id":"111"}'
freeoa=# update test_jsonb set j_jsonb = j_jsonb ||'{"c_id":"111"}'::jsonb where c_bh = 'c217';
UPDATE 1
freeoa=# select j_jsonb from test_jsonb where c_bh = 'c217';
j_jsonb
-------------------------------------------------------------------------------
{"c_id": "111", "c_mx": {"c_dwbm": "11", "c_ssdw": "一大队"}, "c_xm": "张三"}
--1.3更新元素（方法1），如果jsonb中有相同的元素则覆盖,使用'||'将'{"c_id":"111"}'更新为112
freeoa=# update test_jsonb set j_jsonb = j_jsonb ||'{"c_id":"112"}'::jsonb where c_bh = 'c217';
UPDATE 1
freeoa=# select j_jsonb from test_jsonb where c_bh = 'c217';
j_jsonb
-------------------------------------------------------------------------------
{"c_id": "112", "c_mx": {"c_dwbm": "11", "c_ssdw": "一大队"}, "c_xm": "张三"}
--更新元素（方法2），使用jsonb_set，将"c_id": "112"更新为123
freeoa=# update test_jsonb set j_jsonb= jsonb_set(j_jsonb,'{c_id}','"123"'::jsonb,false) where c_bh = 'c217';
UPDATE 1
freeoa=# select j_jsonb from test_jsonb where c_bh = 'c217';
j_jsonb
-------------------------------------------------------------------------------
{"c_id": "123", "c_mx": {"c_dwbm": "11", "c_ssdw": "一大队"}, "c_xm": "张三"}
--1.4更新嵌套元素，使用jsonb_set（pg9.5以上才支持），更新c_ssdw为二大队
freeoa=# update test_jsonb set j_jsonb= jsonb_set(j_jsonb,'{c_mx,c_ssdw}','"二大队"'::jsonb,false) where c_bh = 'c217';
UPDATE 1
freeoa=# select j_jsonb from test_jsonb where c_bh = 'c217';
j_jsonb
-------------------------------------------------------------------------------
{"c_id": "123", "c_mx": {"c_dwbm": "11", "c_ssdw": "二大队"}, "c_xm": "张三"}
--1.5删除元素，删除c_id元素
freeoa=# update test_jsonb set j_jsonb = j_jsonb-'c_id' where c_bh = 'c217' ;
UPDATE 1
freeoa=# select j_jsonb from test_jsonb where c_bh = 'c217';
j_jsonb
----------------------------------------------------------------
{"c_mx": {"c_dwbm": "11", "c_ssdw": "二大队"}, "c_xm": "张三"}
jsonb查询
--1.随机文本脚本
freeoa=> create or replace function random_string(INTEGER)
freeoa-> RETURNS TEXT AS
freeoa-> $BODY$
freeoa$> select array_to_string(
freeoa$> array(
freeoa$> select substring(
freeoa$> 'pg社区的作风非常严谨，一个补丁可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，补丁合并到主干已经非常成熟，所以pg的稳定性也是远近闻名的'
freeoa$> from (ceil(random()*73))::int FOR 2
freeoa$> )
freeoa$> from generate_series(1,$1)
freeoa$> ),''
freeoa$> ) $BODY$
freeoa-> LANGUAGE sql VOLATILE;
CREATE FUNCTION
--2.初始化数据：
freeoa=> insert into test_jsonb select replace(uuid_generate_v4()::text,'-',''),('{"a":'||random()*100||', "kxhbsl":"'|| random_string(10) ||'"}')::jsonb from generate_series(1,2000000);
INSERT 0 2000000
freeoa=> insert into test_jsonb select replace(uuid_generate_v4()::text,'-',''),('{"a":'||random()*100||', "kxhbsl":"汤姆是好孩子"}')::jsonb from generate_series(1,10000);
INSERT 0 10000
--3.第一种查询：获取包含'{"kxhbsl": "汤姆是好孩子"}'，全表扫描
freeoa=# explain analyze select j_jsonb->>'kxhbsl',j_jsonb from test_jsonb where j_jsonb @> '{"kxhbsl": "汤姆是好孩子"}';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..53379.78 rows=2010 width=134) (actual time=470.729..490.979 rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_jsonb (cost=0.00..52175.85 rows=838 width=134) (actual time=465.234..480.573 rows=3333 loops=3)
Filter: (j_jsonb @> '{"kxhbsl": "汤姆是好孩子"}'::jsonb)
Rows Removed by Filter: 666667
Planning time: 0.318 ms
Execution time: 506.204 ms
(8 rows)
--j_jsonb字段创建gin索引后，可走索引
freeoa=# create index i_t_test_jsonb_j_jsonb on test_jsonb using gin(j_jsonb);
CREATE INDEX
freeoa=# explain analyze select j_jsonb->>'kxhbsl',* from test_jsonb where j_jsonb @> '{"kxhbsl": "汤姆是好孩子"}';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_jsonb (cost=59.58..6664.09 rows=2010 width=167) (actual time=3.579..17.065 rows=10
000 loops=1)
Recheck Cond: (j_jsonb @> '{"kxhbsl": "汤姆是好孩子"}'::jsonb)
Heap Blocks: exact=481
-> Bitmap Index Scan on i_t_test_jsonb_j_jsonb (cost=0.00..59.08 rows=2010 width=0) (actual time=3.480..3.480 rows=10000 loops=1)
Index Cond: (j_jsonb @> '{"kxhbsl": "汤姆是好孩子"}'::jsonb)
Planning time: 0.429 ms
Execution time: 17.964 ms
(7 rows)
--4.第二种查询，获取包含:'汤姆是好孩子'，全表扫描
freeoa=# explain analyze select j_jsonb->>'kxhbsl',j_jsonb from test_jsonb where j_jsonb -> 'kxhbsl' ? '汤姆是好孩子';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..55473.53 rows=2010 width=134) (actual time=1724.170..1769.543 rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 0
-> Parallel Seq Scan on test_jsonb (cost=0.00..54269.60 rows=838 width=134) (actual time=1723.752..1767.187 rows=10000 loops=1)
Filter: ((j_jsonb -> 'kxhbsl'::text) ? '汤姆是好孩子'::text)
Rows Removed by Filter: 2000000
Planning time: 0.267 ms
Execution time: 1770.422 ms
(8 rows)
--针对jsonb字段的kxhbsl元素创建gin索引。 可走索引
freeoa=# create index i_t_test_jsonb_j_jsonb_kxhbsl on test_jsonb using gin((j_jsonb->'kxhbsl'));
CREATE INDEX
freeoa=# explain analyze select j_jsonb->>'kxhbsl',j_jsonb from test_jsonb where j_jsonb -> 'kxhbsl' ? '汤姆是好孩子';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_jsonb (cost=39.58..6649.12 rows=2010 width=134) (actual time=2.166..13.999 rows=10000 loops=1)
Recheck Cond: ((j_jsonb -> 'kxhbsl'::text) ? '汤姆是好孩子'::text)
Heap Blocks: exact=481
-> Bitmap Index Scan on i_t_test_jsonb_j_jsonb_kxhbsl (cost=0.00..39.08 rows=2010 width=0) (actual time=2.045..2.045 rows=10000 loops=1)
Index Cond: ((j_jsonb -> 'kxhbsl'::text) ? '汤姆是好孩子'::text)
Planning time: 0.221 ms
Execution time: 14.715 ms
(7 rows)
--或者等价写法：
freeoa=# explain analyze select j_jsonb->>'kxhbsl',j_jsonb from test_jsonb where j_jsonb -> 'kxhbsl' @>'"汤姆是好孩子"';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_jsonb (cost=39.58..6649.12 rows=2010 width=134) (actual time=2.080..14.959 rows=10000 loops=1)
Recheck Cond: ((j_jsonb -> 'kxhbsl'::text) @> '"汤姆是好孩子"'::jsonb)
Heap Blocks: exact=481
-> Bitmap Index Scan on i_t_test_jsonb_j_jsonb_kxhbsl (cost=0.00..39.08 rows=2010 width=0) (actual time=1.980..1.980 rows=10000 loops=1)
Index Cond: ((j_jsonb -> 'kxhbsl'::text) @> '"汤姆是好孩子"'::jsonb)
Planning time: 0.199 ms
Execution time: 15.635 ms
(7 rows)
--5.第三种查询，获取'{"kxhbsl": "汤姆是好孩子"}'，全表扫描
freeoa=# explain analyze select * from test_jsonb where j_jsonb->>'kxhbsl' = '汤姆是好孩子';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..56272.50 rows=10050 width=135) (actual time=458.676..476.454 rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_jsonb (cost=0.00..54267.50 rows=4188 width=135) (actual time=453.472..466.544 rows=3333 loops=3)
Filter: ((j_jsonb ->> 'kxhbsl'::text) = '汤姆是好孩子'::text)
Rows Removed by Filter: 666667
Planning time: 0.821 ms
Execution time: 492.763 ms
(8 rows)
--针对这类查询，j_jsonb->>'kxhbsl'返回类型为text，那么可以考虑创建一个btree索引，也可以走索引
freeoa=# create index i_test_jsonb_j_jsonb_btree on test_jsonb using btree((j_jsonb ->> 'kxhbsl') );
CREATE INDEX
freeoa=# explain analyze select * from test_jsonb where j_jsonb->>'kxhbsl' = '汤姆是好孩子';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_jsonb (cost=498.44..24049.15 rows=10050 width=135) (actual time=4.150..8.168 rows=10000 loops=1)
Recheck Cond: ((j_jsonb ->> 'kxhbsl'::text) = '汤姆是好孩子'::text)
Heap Blocks: exact=481
-> Bitmap Index Scan on i_test_jsonb_j_jsonb_btree (cost=0.00..495.93 rows=10050 width=0) (actual time=4.042..4.042 rows=10000 loops=1)
Index Cond: ((j_jsonb ->> 'kxhbsl'::text) = '汤姆是好孩子'::text)
Planning time: 0.684 ms
Execution time: 8.991 ms
(7 rows)
--6.由于j_jsonb->>'kxhbsl'返回为text类型，所以可在其上面做许多操作，比如in，exists等
--查看执行计划，in查询：
freeoa=# explain analyze select * from test_jsonb where j_jsonb->>'kxhbsl' in ('汤姆是好孩子','3');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_jsonb (cost=992.88..35800.76 rows=20100 width=135) (actual time=2.666..5.992 rows=10000 loops=1)
Recheck Cond: ((j_jsonb ->> 'kxhbsl'::text) = ANY ('{汤姆是好孩子,3}'::text[]))
Heap Blocks: exact=481
-> Bitmap Index Scan on i_test_jsonb_j_jsonb_btree (cost=0.00..987.86 rows=20100 width=0) (actual time=2.576..2.576 rows=10000 loops=1)
Index Cond: ((j_jsonb ->> 'kxhbsl'::text) = ANY ('{汤姆是好孩子,3}'::text[]))
Planning time: 0.360 ms
Execution time: 6.856 ms
(7 rows)
三种查询都能得到相同的结果，可以看出第一种针对于jsonb字段的gin索引，适用于jsonb字段所有的元素，而第二种和第三种分别是对单个元素创建的gin和btree索引。
等值查询方面可能单个元素的btree索引占用空间小，且效率较高，如果单独某个元素的查询较为频繁可选择btree索引，而整个jsonb创建gin对所有元素有效。
第一种传入的是一个json，而第二种，第三种传入的是字符串。
jsonb元素值模糊匹配
--1.有时候需要对jsonb的元素值进行模糊匹配
--在前面只有j_jsonb gin索引情况下，like全模糊匹配不能走索引
freeoa=# explain analyze select * from test_jsonb where j_jsonb->>'kxhbsl' like '%好孩子%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..55287.60 rows=201 width=135) (actual time=832.031..857.306 rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_jsonb (cost=0.00..54267.50 rows=84 width=135) (actual time=826.065..844.494 rows=3333 loops=3)
Filter: ((j_jsonb ->> 'kxhbsl'::text) ~~ '%好孩子%'::text)
Rows Removed by Filter: 666667
Planning time: 0.314 ms
Execution time: 873.938 ms
(8 rows)
--由于(j_jsonb ->>'kxhbsl')返回的是text类型，所以考虑再其上面使用pg_trgm,创建gin索引。
freeoa=# create index i_test_jsonb_j_jsonb_gin on test_jsonb using gin((j_jsonb ->>'kxhbsl') gin_trgm_ops);
CREATE INDEX
--查看执行计划，模糊匹配可走索引。
freeoa=# explain analyze select * from test_jsonb where j_jsonb->>'kxhbsl' like '%好孩子%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_jsonb (cost=17.56..782.71 rows=201 width=135) (actual time=3.781..16.256 rows=10000 loops=1)
Recheck Cond: ((j_jsonb ->> 'kxhbsl'::text) ~~ '%好孩子%'::text)
Heap Blocks: exact=481
-> Bitmap Index Scan on i_test_jsonb_j_jsonb_gin (cost=0.00..17.51 rows=201 width=0) (actual time=3.649..3.649 rows=10000 loops=1)
Index Cond: ((j_jsonb ->> 'kxhbsl'::text) ~~ '%好孩子%'::text)
Planning time: 0.575 ms
Execution time: 17.514 ms
(7 rows)
--2.当然还有一种方式就是将该jsonb字段转为text，然后再创建gin索引
--创建gin索引
freeoa=#create index i_jsonb_ops on test_jsonb using gin ((j_jsonb::text) gin_trgm_ops);
CREATE INDEX
--但是这样的模糊匹配，可能匹配到其他元素中包含同样的值，所以需要加上辅助条件：j_jsonb->>'kxhbsl' like '%汤姆是好孩子%'，用来确保是该元素
freeoa=# explain analyze select * from test_jsonb where j_jsonb->>'kxhbsl' like '%好孩子%' and j_jsonb ::text like '%好孩子%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_jsonb (cost=1297.51..2064.17 rows=1 width=135) (actual time=5.318..28.149 rows=10000 loops=1)
Recheck Cond: ((j_jsonb)::text ~~ '%好孩子%'::text)
Filter: ((j_jsonb ->> 'kxhbsl'::text) ~~ '%好孩子%'::text)
Heap Blocks: exact=481
-> Bitmap Index Scan on i_jsonb_ops (cost=0.00..1297.51 rows=201 width=0) (actual time=5.198..5.198 rows=10000 loops=1)
Index Cond: ((j_jsonb)::text ~~ '%好孩子%'::text)
Planning time: 0.479 ms
Execution time: 29.147 ms
(8 rows)
第二种方法效率相对于第一种要低一点，但是所有元素都可使用。
小结
1.在json和jsonb选择上，json更加适合用于存储，jsonb更加适用于检索。
2.可以对整个jsonb字段创建gin索引，同时也可以对jsonb中某个元素创建gin索引，或者btree。btree效率最高。
3.(j_jsonb ->> 'kxhbsl')返回的是一个text类型，所以可以在该属性上创建对应类型的索引，比如btree，gin索引。
4.对于元素值的模糊匹配可以创建单个元素的gin索引，也可以创建整个jsonb字段的gin索引，前者效率较高，后者适用所有元素。
百万行数据测试对比
JSON 和 JSONB 两个类型的区别大。查询速度差7倍，存储空间差26%，写入速度也能差31%；且这还不是理论值——是有人在 100万行真实数据 上跑出来的基准测试结果。来自于“运维渔夫”的文章把这个选择彻底说清楚：什么时候用 JSON，什么时候必须用 JSONB，以及那些文档里不会告诉你的坑。
功能全景
先简述一下两个类型的本质区别
| 维度 | JSON | JSONB |
| 存储格式 | 纯文本（原样保存） | 二进制（解析后保存） |
| 保留格式 | 保留空格、键顺序 | 去掉空格、键顺序不保证 |
| 保留重复键 | 保留 | 去重 |
| 索引支持 | 无 | GIN 索引 |
| 查询运算符 | 只有 -> 和 ->> | @>, ?, ?& 等高级运算符 |
| 写入速度 | 快（无需解析） | 慢（需要解析+转换） |
| 查询速度 | 慢（每次都要重新解析） | 快（已解析，直接操作） |
一句话总结：
JSON = 懒模式：存得快，查得慢
JSONB = 认真模式：存得慢一点，但查起来飞快
JSONB 的独占能力
以下这些强大的查询运算符只有 JSONB 支持：
-- @> 包含检查：这条记录是否包含指定的键值对？
SELECT * FROM users WHERE data @> '{"role": "admin"}';
-- ? 键存在性检查：是否有这个字段？
SELECT * FROM users WHERE data ? 'email_verified';
-- ?& 多键同时存在检查
SELECT * FROM users WHERE data ?& array['email_verified', 'phone_verified'];
如果用 JSON 类型，上面的查询只能写成笨拙的形式：
-- JSON 类型没有 @> 运算符，只能这样写
SELECT * FROM users
WHERE data->>'role' = 'admin';
-- 无法高效检查嵌套结构或数组包含关系
GIN 索引：JSONB 的秘密武器
-- 为 JSONB 列创建 GIN 索引
CREATE INDEX idx_users_data ON users USING GIN (data);
-- 创建后，包含查询从全表扫描变成索引查找
-- 没有索引：扫描100万行 → 有索引：毫秒级返回
EXPLAIN ANALYZE SELECT * FROM users WHERE data @> '{"status": "active"}';
GIN（Generalized Inverted Index）是专门为复合类型设计的倒排索引。它会将 JSONB 文档中的每个键和值都建入索引，使得包含查询、键存在性查询等操作可以走索引而非全表扫描。
实战案例
案例1：百万行基准测试复现
下面这个测试来自 DEV Community 作者 Eugene 开源的真实基准测试。一步步来复现。
第一步：创建测试表
-- JSON 类型的表
CREATE TABLE bench_json (
id SERIAL PRIMARY KEY,
data JSON NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- JSONB 类型的表
CREATE TABLE bench_jsonb (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
第二步：用python生成百万行测试数据
每条数据模拟一个用户配置对象，包含嵌套结构和数组：
import psycopg2
import json
import random
import string
from datetime import datetime
conn = psycopg2.connect("dbname=testdb host=localhost user=postgres")
cur = conn.cursor()
def generate_user_data():
"""生成模拟用户配置的 JSON 数据"""
roles = ["admin", "editor", "viewer", "moderator"]
plans = ["free", "pro", "enterprise"]
# 随机生成 5-15 个偏好设置
preferences = {}
for i in range(random.randint(5, 15)):
key = f"pref_{random.choice(string.ascii_lowercase)}"
value = random.choice([True, False, random.randint(0, 100), None])
preferences[key] = value
data = {
"user_id": f"u_{random.randint(10000, 99999)}",
"username": f"user_{random.randint(10000, 99999)}",
"email": f"user{random.randint(10000, 99999)}@example.com",
"role": random.choice(roles),
"plan": random.choice(plans),
"settings": {
"theme": random.choice(["dark", "light", "auto"]),
"notifications_enabled": random.choice([True, False]),
"language": random.choice(["zh", "en", "ja"]),
"two_factor_enabled": random.choice([True, False])
},
"preferences": preferences,
"tags": [f"tag_{i}" for i in range(random.randint(0, 8))],
"metadata": {
"signup_source": random.choice(["web", "api", "invite"]),
"last_login": datetime.now().isoformat(),
"login_count": random.randint(1, 500)
}
}
return json.dumps(data)
# 批量插入到 JSON 表（每次 10000 条）
batch_size = 10000
total = 1000000
for i in range(0, total, batch_size):
batch = []
for _ in range(batch_size):
batch.append((generate_user_data(),))
cur.executemany(
"INSERT INTO bench_json (data) VALUES (%s::json)",
batch
)
conn.commit()
print(f"Inserted {min(i + batch_size, total)}/{total}")
# 同样插入到 JSONB 表
for i in range(0, total, batch_size):
batch = []
for _ in range(batch_size):
batch.append((generate_user_data(),))
cur.executemany(
"INSERT INTO bench_jsonb (data) VALUES (%s::jsonb)",
batch
)
conn.commit()
print(f"JSONB: Inserted {min(i + batch_size, total)}/{total}")
cur.close()
conn.close()
第三步：性能对比测试
-- ========== 写入性能测试 ==========
-- 结果（实测）：
-- JSON: 8.6 秒 (100万行)
-- JSONB: 11.3 秒 (100万行)
-- 结论：JSON 写入快约 31%（因为不需要解析）
-- ========== 存储空间测试 ==========
SELECT
'JSON' AS type,
pg_size_pretty(pg_total_relation_size('bench_json')) AS size
UNION ALL
SELECT
'JSONB' AS type,
pg_size_pretty(pg_total_relation_size('bench_jsonb')) AS size;
-- 结果：
-- JSON: ~1200 MB
-- JSONB: ~888 MB
-- 结论：JSONB 节省 26% 空间
-- （原因：去除了空白 + 键名去重）
-- ========== 查询性能测试 ==========
-- 测试1：简单键值提取
EXPLAIN ANALYZE SELECT count(*) FROM bench_json WHERE data->>'role' = 'admin';
EXPLAIN ANALYZE SELECT count(*) FROM bench_jsonb WHERE data->>'role' = 'admin';
-- JSONB 比 JSON 快 **6.2倍**
-- 测试2：嵌套字段访问
EXPLAIN ANALYZE SELECT count(*) FROM bench_json
WHERE data->'settings'->>'theme' = 'dark';
EXPLAIN ANALYZE SELECT count(*) FROM bench_jsonb
WHERE data->'settings'->>'theme' = 'dark';
-- JSONB 比 JSON 快 **7.6倍**
-- 测试3：数组操作
EXPLAIN ANALYZE SELECT count(*) FROM bench_json
WHERE data->'tags'->>0 IS NOT NULL;
EXPLAIN ANALYZE SELECT count(*) FROM bench_jsonb
WHERE data->'tags'->>0 IS NOT NULL;
-- JSONB 比 JSON fast **7.3倍**
-- 测试4：复杂多条件查询
EXPLAIN ANALYZE SELECT count(*) FROM bench_json
WHERE data->>'role' = 'admin'
AND data->'settings'->>'two_factor_enabled' = 'true'
AND data->'plan' = 'enterprise';
EXPLAIN ANALYZE SELECT count(*) FROM bench_jsonb
WHERE data->>'role' = 'admin'
AND data->'settings'->>'two_factor_enabled' = 'true'
AND data->'plan' = 'enterprise';
-- JSONB 比 JSON 快 **9.1倍**
性能汇总表
| 操作 | JSON | JSONB | JSONB优势 |
| INSERT 100万行 | 8.6s | 11.3s | JSON快31% |
| 简单键值查询 | ~12ms | ~1.9ms | JSONB快6.2x |
| 嵌套字段查询 | ~18ms | ~2.4ms | JSONB快7.6x |
| 数组元素访问 | ~15ms | ~2.1ms | JSONB快7.3x |
| 多条件组合查询 | ~25ms | ~2.7ms | JSONB快9.1x |
| 部分UPDATE | ~8ms | ~2.3ms | JSONB快71% |
| 磁盘占用 | ~1200MB | ~888MB | JSONB省26% |
案例2：实际业务场景选型
场景A：日志/事件存储 → 选 JSONB
CREATE TABLE application_logs (
id BIGSERIAL PRIMARY KEY,
timestamp TIMESTAMPTZ DEFAULT NOW(),
service_name TEXT NOT NULL,
level TEXT CHECK (level IN ('DEBUG', 'INFO', 'WARN', 'ERROR')),
payload JSONB NOT NULL, -- ← 用 JSONB
-- GIN 索引让日志检索起飞
);
-- 创建 GIN 索引
CREATE INDEX idx_logs_payload ON application_logs USING GIN (payload);
-- 高效查询：找所有 ERROR 级别且包含特定错误码的日志
SELECT timestamp, service_name, payload
FROM application_logs
WHERE level = 'ERROR'
AND payload @> '{"error_code": "AUTH_001"}'
ORDER BY timestamp DESC
LIMIT 50;
-- 查询计划：使用 GIN Index Scan，毫秒级返回
为什么选 JSONB？
日志通常是写一次读多次
经常需要按 payload 内的字段做条件过滤
GIN 索引让亿级日志秒查成为可能
场景B：原始数据暂存 → 可以考虑 JSON
CREATE TABLE raw_webhook_payloads (
id BIGSERIAL PRIMARY KEY,
received_at TIMESTAMPTZ DEFAULT NOW(),
source_app TEXT NOT NULL,
raw_payload JSON NOT NULL, -- ← 用 JSON
processed BOOLEAN DEFAULT FALSE
);
-- 写入速度优先，几乎不做查询
-- 只在异常排查时才去看 raw_payload 的内容
-- 这种场景 JSON 的写入速度优势有意义
为什么可以考虑 JSON？
纯写密集型：webhook 接收后立即处理，很少回查
只写不读（或极少读）：不需要查询优化的红利
需要保留原始格式（含空格、键顺序）：方便跟上游对账
注意：如果后来发现"好像偶尔要查一下"，那还是老老实实用 JSONB 吧。改列类型的代价远大于一开始就选对的代价。
场景C：动态属性/Schema 自由字段 → 必须用 JSONB
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
sku TEXT UNIQUE NOT NULL,
price DECIMAL(10, 2),
-- 不同品类的商品有完全不同的属性
attributes JSONB NOT NULL, -- ← 必须用 JSONB
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- 创建 GIN 索引支持任意属性搜索
CREATE INDEX idx_products_attr ON products USING GIN (attributes);
-- 示例：电子产品有屏幕尺寸、电池容量
INSERT INTO products (name, sku, price, attributes) VALUES (
'iPhone Pro Max',
'IPHONE-PM-256',
9999.00,
'{"screen_size": "6.9 inch", "battery_mAh": 5000, "color": "black", "storage_gb": 256}'
);
-- 示例：服装商品有尺码、材质
INSERT INTO products (name, sku, price, attributes) VALUES (
'羊毛大衣',
'COAT-WOOL-L',
2999.00,
'{"size": "L", "material": "wool", "season": "winter", "weight_g": 800}'
);
-- 跨品类查询：找所有价格 < 5000 且有库存的商品
SELECT name, sku, attributes->>'color' as color
FROM products WHERE price < 5000
AND attributes ? 'stock'; -- 检查是否有库存字段
这种"每个记录的属性结构都不同"的场景，正是 JSONB 的主场。
FAQ / 常见问题
Q1：已经用了 JSON，能改成 JSONB 吗？
可以且简单：
-- 一行搞定
ALTER TABLE my_table ALTER COLUMN data TYPE JSONB USING data::jsonb;
注意事项：
这会对整张表做 重写（REWRITE），大表会锁一段时间
生产环境建议在低峰期执行
如果表特别大（千万级行数），可以用 CONCURRENTLY 友好的方式分批迁移：
-- 大表安全迁移方案
-- 1. 新增一个 JSONB 列
ALTER TABLE my_table ADD COLUMN data_new JSONB;
-- 2. 分批填充数据（每次更新 50000 行）
UPDATE my_table SET data_new = data::jsonb
WHERE data_new IS NULL
LIMIT 50000;
-- 重复执行直到全部迁移完
-- 3. 创建索引
CREATE INDEX idx_data_new ON my_table USING GIN (data_new);
-- 4. 切换应用层到新列，验证无误后删除旧列
-- ALTER TABLE my_table DROP COLUMN data;
-- ALTER TABLE my_table RENAME data_new TO data;
Q2：JSONB 的 GIN 索引会不会让写入变慢？
会有一点影响，但可接受：
| 操作 | 无索引 | 有 GIN 索引 | 开销增量 |
| INSERT | 基准 | +15-25% | 中等 |
| UPDATE（非JSON列） | 基准 | 无额外开销 | 无 |
| UPDATE（JSON列本身） | 基准 | +30-50% | 较高 |
| DELETE | 基准 | +20-30% | 低-中 |
权衡建议：
1.如果 JSONB 列频繁更新（比如每秒几十次），GIN 索引的开销值得关注；
2.如果主要是 INSERT + SELECT（大多数场景），GIN 索引的查询收益远远覆盖写入开销；
3.还有个折中选项：partial index（部分索引），只为常用查询条件建索引。
-- 只为 status = active 的记录建索引
CREATE INDEX idx_active_users_data ON users USING GIN (data) WHERE (data->>'status') = 'active';
这样索引体积更小，写入开销更低，同时覆盖了最常用的查询场景。
Q3：什么时候该用普通列而不是 JSON/JSONB？
这是个好问题。很多人一看到"灵活"两个字就上 JSON，但其实常规字段应该用常规类型。
--不推荐：把所有东西都塞进 JSON
CREATE TABLE bad_design (
id SERIAL PRIMARY KEY,
data JSONB -- 用户名、邮箱、年龄、注册时间...全塞这里
);
--推荐：固定属性用常规列，只有真正的变体属性用 JSONB
CREATE TABLE good_design (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
age INTEGER,
registered_at TIMESTAMPTZ DEFAULT NOW(),
extra_attributes JSONB -- 这里放真正不确定的字段
);
决策树：
这个字段的值格式固定吗？
├─ 是 → 用常规列（TEXT / INTEGER / TIMESTAMP 等）
│ 能加约束（CHECK/UNIQUE/NOT NULL），能建 B-tree 索引
│
└─ 否 → 这个字段的结构会变化吗？
├─ 是 → 用 JSONB（+ GIN 索引）
│
└─ 否，只是纯文本存取 → 用 JSON 或直接用 TEXT
Q4：JSONB 的 `jsonb_path_query` 怎么用？好用吗？
PostgreSQL 提供了 SQL/JSON Path 标准（Postgres 12+），支持类似 XPath 的路径表达式查询：
-- 找所有 settings.theme = dark 且 tags 数组长度 > 3 的用户
SELECT id, data FROM bench_jsonb
WHERE jsonb_path_exists(data, '$.settings.theme == "dark" && $.tags.size() > 3');
-- 更复杂的：递归查找任意层级的 error 字段
SELECT id, jsonb_path_query_first(data, '$.**.error') as first_error
FROM application_logs WHERE jsonb_path_exists(data, '$.**.error');
优点：
表达能力强，一行搞定复杂条件
支持过滤器（?()）、递归（**）、数组切片等
缺点：
语法学习成本较高
性能在某些情况下不如 @> + GIN 组合
对已有 PromQL/SQL 基础的人来说不够直觉
建议：日常简单查询用 @> / ->> 就够了，遇到特别复杂的嵌套查询再考虑 jsonb_path_query。
Q5：MySQL 的 JSON 类型和 PostgreSQL 的 JSONB 一样吗？
不完全一样，主要差异：
| 特性 | MySQL-v8 JSON | Postgres JSONB | |
| 存储格式 | 二进制（类似 JSONB） | 二进制 | |
| 索引方式 | Multi-valued Index (8.0+) | GIN 索引 | |
| 查询语法 | -> / ->> (兼容PG) | -> / ->> / @> / ? | |
| 高级运算符 | 较少（无 @> 包含检查） | 丰富 | |
| 路径查询 | JSON_CONTAINS 等 | jsonb_path_query (SQL/JSON Path) | |
| 更新操作 | JSON_SET / JSON_REMOVE | `\ | \= (concat) / -` (delete) |
| 成熟度/生态 | 够用但社区反馈较少 | 非常成熟，大量生产验证 |
在做技术选型时单就 JSON 能力来说，Postgres 的 JSONB 明显更强。这也是很多团队从 MySQL 迁移到 PG 的原因之一。
写在最后
JSON 还是 JSONB？答案其实很简单：
1.99% 的场景，直接用 JSONB，别纠结；
2.那 1% 的情况是：纯写密集型 + 几乎不读 + 需要保留原始格式。
别为了省那 31% 的写入时间而选了 JSON，然后在查询时付出 7 倍的性能代价。
本文源自：互联网，感谢原作者。