一、JSON和JSONB

       Postgresql相比较与其它关系型数据库，很大的一个优势在于它内置了很多中特殊的数据类型，像inet、MAC，本文所介绍的json和jsonb类型也是Postgresql特有的2种数据类型，其实他们的本质都是存储jon格式的数据，但是不同的是json格式是按照原样的json字符串进行存储，包括字符串中的重复键、空格、顺序也都会原样保存；而jsonb格式会对原样的json字符串进行解析，会删除重复的键以及空格，而且也不会保证键的顺序，在存储时会转换成二进制。因此，json数据存储时较快，但是读取时稍慢；而jsonb数据存储时稍慢，但是读取时较快。

       除此之外，json和jsonb还有一些其它方面的区别：

（1）两者所用的操作和操作符大部分相同，但是jsonb会有一些特定的操作符，如“=”用来判断两个jsonb对象是否相等，“@>”用来判断左边的操作数是否包含右边的操作数。

（2） 我们可以在jsonb类型的字段上直接创建索引，但是在json类型的字段上只能创建函数索引，无法创建直接索引。

       下面，笔者就上述这些内容进行验证。首先，先看下笔者用到的两个数据表：

stock_analysis_data=# \d+ custmer
                                                       Table "public.custmer"
 Column |         Type          | Collation | Nullable |               Default               | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id     | integer               |           | not null | nextval('custmer_id_seq'::regclass) | plain    |              | 
 name   | character varying(40) |           |          |                                     | extended |              | 
 tag    | json  

• custmer表 —— 具有一个json类型的字段tag

stock_analysis_data=# \d+ account
                                                       Table "public.account"
 Column |         Type          | Collation | Nullable |               Default               | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id     | integer               |           | not null | nextval('account_id_seq'::regclass) | plain    |              | 
 name   | character varying(40) |           |          |                                     | extended |              | 
 tag    | jsonb                 |           |          |                                     | extended |              | 

• account表——具有一个jsonb类型的字段tag

二、插入JSON数据

• 插入json标准格式数据

       我们先向custmer表中插入一条标准json格式的数据，以验证json类型数据的插入效果。

stock_analysis_data=# insert into custmer (name,tag) values ('tom','{"age":17,"job":"开发"}'); 
INSERT 0 1

       接下来，我们查看整个custmer表里面的数据：

stock_analysis_data=# select * from custmer;
 id | name |           tag           
----+------+-------------------------
  1 | tom  | {"age":17,"job":"开发"}
(1 row)

       然后，向account插入一条数据。

stock_analysis_data=# insert into account (name,tag) values ('tom','{"age":17,"job":"开发"}'); INSERT 0 1

       查看插入后的数据：

stock_analysis_data=# insert into account (name,tag) values ('tom','{"age":17,"job":"开发"}');
INSERT 0 1

       我们发现，从account表里面查询出来的数据，tag字段也是一个标准的json格式，实际上json和jsonb两者的主要差异在于存储的数据结构，jsonb是以二进制方式存储的。但是，对于我们使用客户端进行查询来说，查询出来的jsonb格式的数据也会最终以json标准格式呈现出来，他们的存储结构的异同，对我们是无感的。

• 插入时重复键、空格、和顺序处理  

       接下来我们验证下json格式和jsonb格式对于json数据中重复键、空格和顺序的处理。我们向向custmer表中插入包含空格和重复键的数据。

stock_analysis_data=# insert into custmer (name,tag) values ('tom','{"age":17,"j o b":"开  发","age":19}');
INSERT 0 1
stock_analysis_data=# select * from custmer;
 id | name |               tag                
----+------+----------------------------------
  3 | tom  | {"age":17,"j o b":"开  发","age":19}
(1 row)

       从后面的查询到的数据中，我们可以看到：笔者插入的json格式的数据，存在重复键age，而且job字段的键和值都存在空格，而json格式的字段的确没有对重复键和空格进行处理，原样进行的存储。

       接下来，笔者向account表中也插入同样的数据，并进行查询：

stock_analysis_data=# insert into account (name,tag) values ('tom','{"age":17,"j o b":"开  发","age":19}');
INSERT 0 1
stock_analysis_data=# select * from account;
 id | name |              tag               
----+------+--------------------------------
  2 | tom  | {"age": 19, "j o b": "开  发"}
(1 row)

       细心的朋友已经发现，为什么json键值对里面的空格没有去掉？不是说jsob会去掉空格吗？实际上，isonb里面的去空格，并不是指键值对内部的去空格，而是去掉键值对之间的空格，比如下面的例子，笔者在第一个age和job之间加了几个空格，查出来之后，发现已经消失。

stock_analysis_data=# insert into account (name,tag) values ('tom','{"age":17,    "j o b":"开  发","age":19}');
INSERT 0 1
stock_analysis_data=# select * from account;
 id | name |              tag               
----+------+--------------------------------
  3 | tom  | {"age": 19, "j o b": "开  发"}

      而且我们还发现，jsob去重时默认保留的是重复的最后一个键值对，而且对json中键值对的顺序进行了排序。

• 插入错误格式的json数据

       json和jsonb存储json字符串，相比较于直接使用text等字符串类型存储json字符串，最大的一个特点就是，前者会进行json格式的校验，而后者不会校验，比如下面的例子，笔者直接插入了一个错误格式(后面两个键值对之间缺少逗号)的json字符串到custmer表，最终插入失败：

stock_analysis_data=# insert into account (name,tag) values ('tom','{"age":17,"job":"开发""age":19}');
ERROR:  invalid input syntax for type json
LINE 1: insert into account (name,tag) values ('tom','{"age":17,"job...
                                                     ^
DETAIL:  Expected "," or "}", but found ""age"".
CONTEXT:  JSON data, line 1: {"age":17,"job":"开发""age"..

三、常见的JSON/JSONB操作符或者函数

       Postgresql提供了一系列的json/jsonb相关的操作符或者函数，可以使我们能够很方便的操作json/jsonb，下面，笔者就一部分常用的操作符或者函数进行介绍。

• ->操作符

作用：通过键取出json/jsonb中的值。

stock_analysis_data=# select tag->'age' from account where name='tom';
 ?column? 
----------
 19

• ->>操作符

作用：通过键取出json/jsonb中的值，但是返回的结果会自动转成text类型。

stock_analysis_data=# select tag->>'age' from account where name='tom';
 ?column? 
----------
 19

• #>操作符

作用：对于嵌套的json数据，可以按照路径进行值得获取。

        在验证这个操作符使用之前，我们先需要先向数据库中插入一个嵌套的json数据，插入后的数据如下所示：

stock_analysis_data=# select * from account where name='jerry';
 id | name  |                  tag                   
----+-------+----------------------------------------
  4 | jerry | {"info": {"age": 18, "job": "架构师"}}
(1 row)

       我们现在要做的，就是使用#>操作符来获取age对应的值：

stock_analysis_data=# select tag#>'{info,age}' from account where name='jerry';
 ?column? 
----------
 18
(1 row)

• =操作符

作用：只能用于jsonb，判断两个jsonb的数据是不是相等。

stock_analysis_data=# select * from account where tag='{"age": 19, "j o b": "开  发"}'::jsonb;
 id | name |              tag               
----+------+--------------------------------
  3 | tom  | {"age": 19, "j o b": "开  发"}

需要注意的是，jsonb数据是顺序敏感的，键值对的顺序不同时，两个jsonb的数据不相同。

• @>操作符和<@操作符

作用：只能用于jsonb，@>操作符的作用是判断左操作数的jsonb对象是否包含右操作数的jsonb对象，而<@作用和其相反。

stock_analysis_data=# select * from account where tag@>'{"age": 19}'::jsonb;
 id | name |              tag               
----+------+--------------------------------
  3 | tom  | {"age": 19, "j o b": "开  发"}

• to_json函数

作用：把其它类型的对象转换成json对象

       在验证它的使用之前，我们先创建一个自定义的TYPE:

stock_analysis_data=# create type info as(age integer,job text);
CREATE TYPE

      使用to_json向account表里面的tag字段添加info类型数据，并自动转成jsonb格式：

stock_analysis_data=# insert into account (name,tag) values ('tom',to_json('(19,"开发")'::info));
INSERT 0 1
stock_analysis_data=# insert into account (name,tag) values ('jerry',to_json('(35,"架构师")'::info));
INSERT 0 1
stock_analysis_data=# select * from account;
 id | name  |             tag              
----+-------+------------------------------
  5 | tom   | {"age": 19, "job": "开发"}
  6 | jerry | {"age": 35, "job": "架构师"}
(2 rows)

四、JSON/JSONB数据的修改

       针对json/jsonb字段进行修改，可以使用||操作符：

stock_analysis_data=# update account set tag=tag||'{"age":20}' where name='tom';
UPDATE 1

       再有，如果是jsonb格式的数据，可以使用jsonb_set的方法：

stock_analysis_data=# update account set tag=jsonb_set(tag,'{age}','28')  where name='tom';
UPDATE 1
stock_analysis_data=# select * from account where name='tom';
 id | name |            tag             
----+------+----------------------------
  5 | tom  | {"age": 28, "job": "开发"}
(1 row)

jsonb的函数原型如下：

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

• target —— 目标jsonb格式的字段
• path —— json格式中目标值的路径
• new_value —— 新的值，注意这里如果目标的值类型为数字时，这里也要加上单引号。

五、JSON/JSONB数据的删除

        删除json/jsonb里面的键值对，可以使用‘-’操作符：

stock_analysis_data=# update account set tag=tag-'age'  where name='tom';
UPDATE 1
stock_analysis_data=# select * from account where name='tom';
 id | name |       tag       
----+------+-----------------
  5 | tom  | {"job": "开发"}
(1 row)

六、为JSON/JSONB添加索引 

       上文中已经提到，JSON类型没有提供相关的比较函数，是无法直接创建索引的，只能使用函数索引。但是JSONB类型可以直接在上面建立索引，可以是BTree索引，也可以是GIN索引。但是BTree索引不关心JSON内部的数据，只是简单将JSONB的值顺序排列，在实际的应该场景中基本不会用到，我们通常说的再JSONB类型中创建的索引是指GIN索引。

• 为JSON字段增加函数索引

       下面，笔者先演示再custmer表中，创建函数索引的方法，在此之前，我们要做些准备功能，先向表中插入200W条数据。

stock_analysis_data=# select count(*) from custmer;
  count  
---------
 2097152
(1 row)

       在这200W条数据中，有10条数据的tag字段中，age=21，在插入索引之前，先执行查询查看耗时：

stock_analysis_data=# explain (analyze,verbose) select * from custmer where tag->>'age'='21';
                                                             QUERY PLAN                                                    
          
---------------------------------------------------------------------------------------------------------------------------
----------
 Gather  (cost=1000.00..34755.80 rows=10486 width=47) (actual time=0.338..1683.812 rows=10 loops=1)
   Output: id, name, tag
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.custmer  (cost=0.00..32707.20 rows=4369 width=47) (actual time=1098.140..1657.965 rows=3
 loops=3)
         Output: id, name, tag
         Filter: ((custmer.tag ->> 'age'::text) = '21'::text)
         Rows Removed by Filter: 699047
         Worker 0: actual time=1650.803..1650.816 rows=7 loops=1
         Worker 1: actual time=1643.537..1643.538 rows=0 loops=1
 Planning Time: 0.049 ms
 Execution Time: 1683.847 ms
(12 rows)

Time: 1684.334 ms (00:01.684) 

       我们发现，整个查询，在没有走索引的情况下耗时1683ms。现在，我们使用json_extract_path_text建立一个函数索引。

stock_analysis_data=# create index myfuncinx on custmer using btree(json_extract_path_text(tag,'age'));
CREATE INDEX
Time: 3879.206 ms (00:03.879)

       再利用json_extract_path_text函数执行查询：

stock_analysis_data=# explain (analyze,verbose) select * from custmer where json_extract_path_text(tag,'age')='21';
                                                         QUERY PLAN                                                        
 
---------------------------------------------------------------------------------------------------------------------------
-
 Bitmap Heap Scan on public.custmer  (cost=197.69..17322.43 rows=10486 width=47) (actual time=0.082..0.094 rows=10 loops=1)
   Output: id, name, tag
   Recheck Cond: (json_extract_path_text(custmer.tag, VARIADIC '{age}'::text[]) = '21'::text)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on myfuncinx  (cost=0.00..195.07 rows=10486 width=0) (actual time=0.064..0.065 rows=10 loops=1)
         Index Cond: (json_extract_path_text(custmer.tag, VARIADIC '{age}'::text[]) = '21'::text)
 Planning Time: 2.450 ms
 Execution Time: 0.147 ms
(8 rows)

Time: 3.063 ms

        可以看到，本次查询走了索引，而且总耗时3ms，是原来的1/500。

• 为JSONB创建GIN索引

       接下来，笔者演示一下在JSONB字段上创建GIN索引的方法，以及比较创建索引前后的查询效率。在此之前，我们先要向account表里面插入200W条测试数据。

stock_analysis_data=# select  count(*) from account;
  count  
---------
 2097152
(1 row)

        200W条数据中，有6条数据的tag字段，job为‘项目经理’，我们找到这6条数据。

        首先，在未添加索引的情况下查找：

stock_analysis_data=# explain (analyze,verbose) select * from account where tag @>'{"job":"项目经理"}';
                                                            QUERY PLAN                                                     
       
---------------------------------------------------------------------------------------------------------------------------
-------
 Gather  (cost=1000.00..30692.37 rows=2097 width=43) (actual time=0.336..565.635 rows=6 loops=1)
   Output: id, name, tag
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.account  (cost=0.00..29482.67 rows=874 width=43) (actual time=354.463..542.827 rows=2 lo
ops=3)
         Output: id, name, tag
         Filter: (account.tag @> '{"job": "项目经理"}'::jsonb)
         Rows Removed by Filter: 699049
         Worker 0: actual time=525.154..525.161 rows=4 loops=1
         Worker 1: actual time=538.161..538.162 rows=0 loops=1
 Planning Time: 0.062 ms
 Execution Time: 565.678 ms
(12 rows)

Time: 566.094 ms

        发现整个查找过程花费了566ms的时间。接下来，我们在tag上创建GIN索引。jsonb的GIN索引分为两种：

（1）使用默认的jsonb_ops操作符创建。

（2）使用jsonb_path_ops操作符创建。

       两者的区别是：前者的jsonb数据中每个key和value都是作为一个单独的索引项，而后者则为每个键值对只做一个索引项。举个例子说：对于{"job":"项目经理"}这个json，jsonb_ops会为"job"，“项目经理”创建两个索引，而jsonb_path_ops只会为“job”和"项目经理"组合成一个Hash值来作为一个索引项。因为少了很多索引项，所以jsonb_path_ops索引会比jsonb_ops索引小，而且性能也有所提升。

       下面，我们分别创建两种GIN索引，并演示查询。首先创建默认的jsonb_ops索引：

stock_analysis_data=# create index myjsongininx on account using gin(tag);
CREATE INDEX
Time: 2018.663 ms (00:02.019

stock_analysis_data=# explain (analyze,verbose)   select * from account where tag @>'{"job":"项目经理"}';
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.account  (cost=1140.25..7158.99 rows=2097 width=43) (actual time=0.118..0.131 rows=6 loops=1)
   Output: id, name, tag
   Recheck Cond: (account.tag @> '{"job": "项目经理"}'::jsonb)
   Heap Blocks: exact=3
   ->  Bitmap Index Scan on myjsongininx  (cost=0.00..1139.73 rows=2097 width=0) (actual time=0.101..0.102 rows=6 loops=1)
         Index Cond: (account.tag @> '{"job": "项目经理"}'::jsonb)
 Planning Time: 0.089 ms
 Execution Time: 0.171 ms
(8 rows)

Time: 0.723 ms

        可以看到查询的性能得到了大幅度的提升。接着再使用jsonb_path_ops创建一个GIN索引（先要删除之前的索引），并进行查询。

stock_analysis_data=# create index myjsongininx2 on account using gin(tag jsonb_path_ops);
CREATE INDEX
Time: 1097.963 ms (00:01.098)
stock_analysis_data=# explain (analyze,verbose) select * from account where tag @>'{"job":"项目经理"}';
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.account  (cost=440.25..6458.99 rows=2097 width=43) (actual time=0.027..0.039 rows=6 loops=1)
   Output: id, name, tag
   Recheck Cond: (account.tag @> '{"job": "项目经理"}'::jsonb)
   Heap Blocks: exact=3
   ->  Bitmap Index Scan on myjsongininx2  (cost=0.00..439.73 rows=2097 width=0) (actual time=0.013..0.013 rows=6 loops=1)
         Index Cond: (account.tag @> '{"job": "项目经理"}'::jsonb)
 Planning Time: 0.081 ms
 Execution Time: 0.072 ms
(8 rows)
Time: 0.639 ms

        jsonb_path_ops索引同样大幅度提升了查询效率，而且它的查询耗时还要比jsonb_ops索引要小点。