json,jsonb区别

json和jsonb，而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，查询慢，jsonb类型存储稍慢，查询较快(支持许多额外的操作符)。

关于json和jsonb存储和解析效率连接

这里主要测试jsonb的增删改查

json和jsonb共同操作符

jsonb额外操作符

jsonb增删改

1. --1.1建表
2. a => create table test_jsonb(c_bh char(32),j_jsonb jsonb);
3. CREATE TABLE
4.  
5. --插入数据
6. insert into test_jsonb(c_bh,j_jsonb) values(replace(uuid_generate_v4()::text,'-',''),'{"c_xm":"张三","c_mx":{"c_ssdw":"一大队","c_dwbm":"11"}}');
7. INSERT 0 1
8. --查看数据
9. a =# select * from test_jsonb where j_jsonb @> '{"c_xm":"张三","c_mx":{"c_ssdw":"一大队","c_dwbm":"11"}}';
10. c_bh | j_jsonb
11. ----------------------------------+--------------------------------------------
12. c217c624152943ab93f502117514f432 | {"c_mx": {"c_dwbm": "11", "c_ssdw": "一大队"}, "c_xm": "张三"}
13. (1 row)
14. --1.2操作符||可用于添加元素，添加元素'{"c_id":"111"}'
15. a =# update test_jsonb set j_jsonb = j_jsonb ||'{"c_id":"111"}'::jsonb where c_bh = 'c217c624152943ab93f502117514f432';
16. UPDATE 1
17. a =# select j_jsonb from test_jsonb where c_bh = 'c217c624152943ab93f502117514f432';
18. j_jsonb
19. -------------------------------------------------------------------------------
20. {"c_id": "111", "c_mx": {"c_dwbm": "11", "c_ssdw": "一大队"}, "c_xm": "张三"}
21. (1 row)
22.  
23.  
24. --1.3更新元素（方法1），如果jsonb中有相同的元素则覆盖,使用'||'将'{"c_id":"111"}'更新为112
25. a =# update test_jsonb set j_jsonb = j_jsonb ||'{"c_id":"112"}'::jsonb where c_bh = 'c217c624152943ab93f502117514f432';
26. UPDATE 1
27. a =# select j_jsonb from test_jsonb where c_bh = 'c217c624152943ab93f502117514f432';
28. j_jsonb
29. -------------------------------------------------------------------------------
30. {"c_id": "112", "c_mx": {"c_dwbm": "11", "c_ssdw": "一大队"}, "c_xm": "张三"}
31. (1 row)
32. --更新元素（方法2），使用jsonb_set，将"c_id": "112"更新为123
33. a =# update test_jsonb set j_jsonb= jsonb_set(j_jsonb,'{c_id}','"123"'::jsonb,false) where c_bh = 'c217c624152943ab93f502117514f432';
34. UPDATE 1
35. a =# select j_jsonb from test_jsonb where c_bh = 'c217c624152943ab93f502117514f432';
36. j_jsonb
37. -------------------------------------------------------------------------------
38. {"c_id": "123", "c_mx": {"c_dwbm": "11", "c_ssdw": "一大队"}, "c_xm": "张三"}
39. (1 row)
40.  
41.  
42. --1.4更新嵌套元素，使用jsonb_set（pg9.5以上才支持），更新c_ssdw为二大队
43. a =# update test_jsonb set j_jsonb= jsonb_set(j_jsonb,'{c_mx,c_ssdw}','"二大队"'::jsonb,false) where c_bh = 'c217c624152943ab93f502117514f432';
44. UPDATE 1
45. a =# select j_jsonb from test_jsonb where c_bh = 'c217c624152943ab93f502117514f432';
46. j_jsonb
47. -------------------------------------------------------------------------------
48. {"c_id": "123", "c_mx": {"c_dwbm": "11", "c_ssdw": "二大队"}, "c_xm": "张三"}
49. (1 row)
50.  
51.  
52. --1.5删除元素，删除c_id元素
53. a =# update test_jsonb set j_jsonb = j_jsonb-'c_id' where c_bh = 'c217c624152943ab93f502117514f432' ;
54. UPDATE 1
55. a =# select j_jsonb from test_jsonb where c_bh = 'c217c624152943ab93f502117514f432';
56. j_jsonb
57. ----------------------------------------------------------------
58. {"c_mx": {"c_dwbm": "11", "c_ssdw": "二大队"}, "c_xm": "张三"}
59. (1 row)

jsonb查询

1. --1.随机文本脚本
2. a => create or replace function random_string(INTEGER)
3. a -> RETURNS TEXT AS
4. a -> $BODY$
5. a $> select array_to_string(
6. a $> array(
7. a $> select substring(
8. a $> 'pg社区的作风非常严谨，一个补丁可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，补丁合并到主干已经非常成熟，所以pg的稳定性也是远近闻名的'
9. a $> from (ceil(random()*73))::int FOR 2
10. a $> )
11. a $> from generate_series(1,$1)
12. a $> ),''
13. a $> ) $BODY$
14. a -> LANGUAGE sql VOLATILE;
15. CREATE FUNCTION
16.  
17. --2.初始化数据：
18. a => insert into test_jsonb select replace(uuid_generate_v4()::text,'-',''),('{"a":'||random()*100||', "kxhbsl":"'|| random_string(10) ||'"}')::jsonb from generate_series(1,2000000);
19. INSERT 0 2000000
20. a => insert into test_jsonb select replace(uuid_generate_v4()::text,'-',''),('{"a":'||random()*100||', "kxhbsl":"索尼是大法官"}')::jsonb from generate_series(1,10000);
21. INSERT 0 10000
22.  
23. --3.第一种查询：获取包含'{"kxhbsl": "索尼是大法官"}'，全表扫描
24. a =# explain analyze select j_jsonb->>'kxhbsl',j_jsonb from test_jsonb where j_jsonb @> '{"kxhbsl": "索尼是大法官"}';
25. QUERY PLAN
26. -------------------------------------------------------------------------------------------------------------
27. Gather (cost=1000.00..53379.78 rows=2010 width=134) (actual time=470.729..490.979 rows=10000 loops=1)
28. Workers Planned: 2
29. Workers Launched: 2
30. -> Parallel Seq Scan on test_jsonb (cost=0.00..52175.85 rows=838 width=134) (actual time=465.234..480.57
31. 3 rows=3333 loops=3)
32. Filter: (j_jsonb @> '{"kxhbsl": "索尼是大法官"}'::jsonb)
33. Rows Removed by Filter: 666667
34. Planning time: 0.318 ms
35. Execution time: 506.204 ms
36. (8 rows)
37.  
38. --j_jsonb字段创建gin索引后，可走索引
39. a =# create index i_t_test_jsonb_j_jsonb on test_jsonb using gin(j_jsonb);
40. CREATE INDEX
41. a =# explain analyze select j_jsonb->>'kxhbsl',* from test_jsonb where j_jsonb @> '{"kxhbsl": "索尼是大法官"}';
42. QUERY PLAN
43. -------------------------------------------------------------------------------------------------------------
44. Bitmap Heap Scan on test_jsonb (cost=59.58..6664.09 rows=2010 width=167) (actual time=3.579..17.065 rows=10
45. 000 loops=1)
46. Recheck Cond: (j_jsonb @> '{"kxhbsl": "索尼是大法官"}'::jsonb)
47. Heap Blocks: exact=481
48. -> Bitmap Index Scan on i_t_test_jsonb_j_jsonb (cost=0.00..59.08 rows=2010 width=0) (actual time=3.480..
49. 3.480 rows=10000 loops=1)
50. Index Cond: (j_jsonb @> '{"kxhbsl": "索尼是大法官"}'::jsonb)
51. Planning time: 0.429 ms
52. Execution time: 17.964 ms
53. (7 rows)
54.  
55.  
56. --4.第二种查询，获取包含:'索尼是大法官'，全表扫描
57. a =# explain analyze select j_jsonb->>'kxhbsl',j_jsonb from test_jsonb where j_jsonb -> 'kxhbsl' ? '索尼是大法官';
58. QUERY PLAN
59. -------------------------------------------------------------------------------------------------------------
60. Gather (cost=1000.00..55473.53 rows=2010 width=134) (actual time=1724.170..1769.543 rows=10000 loops=1)
61. Workers Planned: 2
62. Workers Launched: 0
63. -> Parallel Seq Scan on test_jsonb (cost=0.00..54269.60 rows=838 width=134) (actual time=1723.752..1767.
64. 187 rows=10000 loops=1)
65. Filter: ((j_jsonb -> 'kxhbsl'::text) ? '索尼是大法官'::text)
66. Rows Removed by Filter: 2000000
67. Planning time: 0.267 ms
68. Execution time: 1770.422 ms
69. (8 rows)
70.  
71. --针对jsonb字段的kxhbsl元素创建gin索引。 可走索引
72. a =# create index i_t_test_jsonb_j_jsonb_kxhbsl on test_jsonb using gin((j_jsonb->'kxhbsl'));
73. CREATE INDEX
74. a =# explain analyze select j_jsonb->>'kxhbsl',j_jsonb from test_jsonb where j_jsonb -> 'kxhbsl' ? '索尼是大法官';
75. QUERY PLAN
76. -------------------------------------------------------------------------------------------------------------
77. Bitmap Heap Scan on test_jsonb (cost=39.58..6649.12 rows=2010 width=134) (actual time=2.166..13.999 rows=10
78. 000 loops=1)
79. Recheck Cond: ((j_jsonb -> 'kxhbsl'::text) ? '索尼是大法官'::text)
80. Heap Blocks: exact=481
81. -> Bitmap Index Scan on i_t_test_jsonb_j_jsonb_kxhbsl (cost=0.00..39.08 rows=2010 width=0) (actual time=
82. 2.045..2.045 rows=10000 loops=1)
83. Index Cond: ((j_jsonb -> 'kxhbsl'::text) ? '索尼是大法官'::text)
84. Planning time: 0.221 ms
85. Execution time: 14.715 ms
86. (7 rows)
87. --或者等价写法：
88. a =# explain analyze select j_jsonb->>'kxhbsl',j_jsonb from test_jsonb where j_jsonb -> 'kxhbsl' @>'"索尼是大法官"';
89. QUERY PLAN
90. -------------------------------------------------------------------------------------------------------------
91. Bitmap Heap Scan on test_jsonb (cost=39.58..6649.12 rows=2010 width=134) (actual time=2.080..14.959 rows=10
92. 000 loops=1)
93. Recheck Cond: ((j_jsonb -> 'kxhbsl'::text) @> '"索尼是大法官"'::jsonb)
94. Heap Blocks: exact=481
95. -> Bitmap Index Scan on i_t_test_jsonb_j_jsonb_kxhbsl (cost=0.00..39.08 rows=2010 width=0) (actual time=
96. 1.980..1.980 rows=10000 loops=1)
97. Index Cond: ((j_jsonb -> 'kxhbsl'::text) @> '"索尼是大法官"'::jsonb)
98. Planning time: 0.199 ms
99. Execution time: 15.635 ms
100. (7 rows)
101.  
102. --5.第三种查询，获取'{"kxhbsl": "索尼是大法官"}'，全表扫描
103. a =# explain analyze select * from test_jsonb where j_jsonb->>'kxhbsl' = '索尼是大法官';
104. QUERY PLAN
105. -------------------------------------------------------------------------------------------------------------
106. Gather (cost=1000.00..56272.50 rows=10050 width=135) (actual time=458.676..476.454 rows=10000 loops=1)
107. Workers Planned: 2
108. Workers Launched: 2
109. -> Parallel Seq Scan on test_jsonb (cost=0.00..54267.50 rows=4188 width=135) (actual time=453.472..466.5
110. 44 rows=3333 loops=3)
111. Filter: ((j_jsonb ->> 'kxhbsl'::text) = '索尼是大法官'::text)
112. Rows Removed by Filter: 666667
113. Planning time: 0.821 ms
114. Execution time: 492.763 ms
115. (8 rows)
116. --针对这类查询，j_jsonb->>'kxhbsl'返回类型为text，那么可以考虑创建一个btree索引，也可以走索引
117. a =# create index i_test_jsonb_j_jsonb_btree on test_jsonb using btree((j_jsonb ->> 'kxhbsl') );
118. CREATE INDEX
119. a =# explain analyze select * from test_jsonb where j_jsonb->>'kxhbsl' = '索尼是大法官';
120. QUERY PLAN
121. -------------------------------------------------------------------------------------------------------------
122. Bitmap Heap Scan on test_jsonb (cost=498.44..24049.15 rows=10050 width=135) (actual time=4.150..8.168 rows=
123. 10000 loops=1)
124. Recheck Cond: ((j_jsonb ->> 'kxhbsl'::text) = '索尼是大法官'::text)
125. Heap Blocks: exact=481
126. -> Bitmap Inde