PostgreSQL JSON 和 JSONB 功能与不同

POSTGRESQL 对json的支持相对某些数据库是非常给力的, json数据的存储和使用在目前系统的开发信息的传递是主导的. 但POSTGRESQL 支持JSON 的方式有两种 JSON 和  JSONB ,到底有什么不同,在项目中应该用那个,都是需要说明的.

POSTGRESQL 支持JSON可以追溯到 postgresql9.2 （2012年）当时仅仅是能存储JSON格式的数据，JSONB是在POSTGRESQL 9.4开始支持的（2014）年，JSONB 是通过分解的二进制格式来存储JSON的数据，JSONB支持索引查找JSONB中的数据，需要对JSON内部的数据进行查找，则应该使用JSONB 格式来存储和调用数据。

在POSTGRESQL 12 （2019）,提供了更强大的SQL/JSON标准，并且提供JSONPATH 查询语句，提供了更有效查询JsonB数据的方式

那么问题是什么时间使用JSON 什么时间使用JSONB，一句话就可以解决，如果你拿POSTGRESQL 中的JOSN当成一个整体，不需要对里面的数据进行处理，那么JSON读取的速度会比JSONB快， 但如果你对里面的数据进行分析和部分查找，则就需要使用JSONB格式。

上图是来自一篇2017年的文字，关于MYSQL ，PG, MONGODB在处理JSON数据的吞吐量的比较，所以POSTGRESQL 处理JSON 也是有一套的 ，如果输入量太大并且瞬时的吞吐量要求高，那么还是求助MOGNODB比较好，MYSQL这里就不提了。

下面我们就从以下几点来看JOSN数据在POSTGRESQL 中的存储和处理

1 data *

2 indexes

3operators

4 functions

1  数据类型

create table json_test (id integer primary key,
                        json_t json,
jsonb_t jsonb);

创建一个表，其中包含JSON 和 JSONB 两种类型

下面简单的进行JSON 数据的输入，输入的内容在JSON,JOSNB是一致的

insert into json_test (id,json_t,jsonb_t) values (1,'5','5');

insert into json_test (id,json_t,jsonb_t) values (2,'[1,2,"foo",null]','[1,2,"foo",null]');

insert into json_test (id,json_t,jsonb_t) values (3,'{"act":"act","foo":"foo"}','{"act":"act","foo":"foo"}');

下面是对比JSON & JSONB 之间的不同

1  查询指定值是否在JSON串中

select * from json_test where jsonb_t @> '"foo"'::jsonb;

select * from json_test where json_t @> '"foo"'::jsonb;

我们可以看到，上面的查询中JSONB 可以使用 @> 来查询JSON 串中是否有指定值，而JSON则不支持

select * from json_test where jsonb_t @> '[2,1]'::jsonb;

2  索引

在对JSON的支持中，POSTGRESQL 可以使用的索引有BTREE ,GIN,HASH 等INDEX

1 GIN 索引在JSON 中主要的作用在查询你JOSN中数据的包含值，索引是否可以在JOSNB中起到作用，通过索引来加速JSON的数据的查找。

create index on json_test using gin(jsonb_t);

 insert into json_test (id,json_t,jsonb_t) values (4,'{"act":"act"}','{"name":"Simon","tags":["em","ac","pp"]}');

explain select jsonb_t->'name' from json_test where jsonb_t @> '{"name":"Simon"}';

可以看到建立索引后，JSONB是可以通过索引来进行数据查找。

GIN 索引所面对的查询的方式之一 就是判断值是否在JSON串中

1  JSON 中是否存在这个KEY

select * from json_test where jsonb_t ? 'tags';

如果查询的不是顶层的数据，则GIN索引就没有办法帮助通过索引的方式来查询。

select * from json_test where jsonb_t->'tags' ? 'em';

BTREE  Index 

CREATE INDEX idx_tags ON json_test USING btree (jsonb_t);
 Btree index 主要支持的操作为 =  < >  >=  <= 等比较的操作符号

3 operators

POSTGRESQL JOSN的操作符比较多，让人眼花缭乱，而JOSN 和JOSNB之间的区别也在于一些操作符的支持

如 

<  less than

>  greater than

<=  less than or equal to 

>=  greater than or equal to 

=   equal

<>  not equal 

官方文档中也有相关说明

JSON JOSNB 支持的操作符号

仅仅在JSONB中支持的操作符

关于JSONB 的 function 的可以单独写一期。