PostgreSQL JSON 和 JSONB 功能与不同

文章转载自公众号：AustinDatabases

作者：carol 11

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

POSTGRESQL支持JSON可以追溯到postgresql 9.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中的存储和处理

• data types

• indexes

• operators

• 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的可以单独写一期

中国PostgreSQL分会与腾讯云战略合作协议签订

PostgreSQL 13.0 正式版发布通告

深度报告：开源协议那些事儿

从“非主流”到“潮流”，开源早已值得拥有

Oracle中国正在进行新一轮裁员，传 N+6 补偿

PostgreSQL与MySQL版权比较

新闻|Babelfish使PostgreSQL直接兼容SQL Server应用程序

四年三冠，PostgreSQL再度荣获“年度数据库”

更多新闻资讯，行业动态，技术热点，请关注中国PostgreSQL分会官方网站

https://www.postgresqlchina.com

中国PostgreSQL分会生态产品

https://www.pgfans.cn

中国PostgreSQL分会资源下载站

https://www.postgreshub.cn