Javascript

Postgresql杂谈 05—Postgresql中的JSON和JSONB类型

本文主要是介绍Postgresql杂谈 05—Postgresql中的JSON和JSONB类型,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

一、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索引要小点。

这篇关于Postgresql杂谈 05—Postgresql中的JSON和JSONB类型的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!