实战7:PostgreSQL JSON数据类型大探
在正式的小节学习之前,我们先来探讨问题,你究竟是否有必要使用类似于MongoDB
这样的文档性?
这些年,No
以及New
都刮起过一番浪潮,而终究还是岿然不动,不仅没有被打垮,反而变得更加大。Postgre
号称世界上最先进的关系,很早的时候便已经开始文档性数据类型了,而且在9.3
以后的每版本,都提供了更多的新特性。
Postgre 最重要的文档性数据类型就是JSON
了,与 MongoDB 的BSON
相比较,Postgre 或许更加强大,因为它能与原有的关系性范式兼容,给存储与维护带来了更多的可行性和便利性。
Postgre 的JSON
类型十分强大,不仅基本的增删查改,判断,还索引和;当然 MongoDB 也有无可替代的特性,不仅强大,而且天然分布式。如果你对文档数据存储的特性并没有太高的要求,且需要与原来的关系兼容,那么 Postgre 或许是你更好的选择。
JSON 数据类型几乎已经是现在Web
开发的标配了,在
5.7
以后也提供了它的,不过即使到现在, 对于 JSON 的也有限,而 Postgre 对 JSON 的十分强大。
Postgre 在 9.3 版本对 JSON 做了显著增强外,在 9.4 引入了JSONB
类型,JSONB 类型是 JSON 类型的二进制版,不仅存储空间更小,更好,而且还索引,在 12 这个大版本中,直接引入了 JSON path 特性来方便的操作 JSON 数据,让 JSON 的操作更加方便和有效。
接下来,就让我们一起来学习 Postgre 的 JSON 类型吧。
: 本文使用的 Postgre 版本为12.1
。
Postgre 两种 JSON 数据类型:json
与jsonb
。二者在使用上几乎无差异,主要区别是 json 存储的是文本格式,而 jsonb 存储的是二进制格式。因此:
我们来实操一下二者的使用吧。
首先,我们看一下 json:
SELECT '{"username":"pedro","age":23}'::json;
json
-------------------------------
{"username":"pedro","age":23}
在 Postgre 中::
符号用于类型转换,该语句将字符串'{"username":"pedro","age":23}'
,通过类型转换为json
,得到了 json 数据结果。
前面,我们谈到 json 以文本格式存储数据,且插入较快,那么是不是真的如此了?
SELECT '{"username":"pedro", "age": 23}'::json;
json
----------------------------------------
{"username":"pedro", "age": 23}
Time: 0.221 ms
从结果可以看出 json 确实以文本格式存储了数据,多余的空格依旧存在,那么再看 jsonb:
SELECT '{"username":"pedro", "age": 23}'::jsonb;
jsonb
----------------------------------
{"age": 23, "username": "pedro"}
Time: 0.265 ms
可以看到,jsonb 处理多余的空格,因此消耗的时候多了那么一点,在实际的测试中,json 的插入确实比 jsonb 要高。
由于 json 和 jsonb 的操作几乎一致,但 jsonb 更为增大,更多的特性,因此我们以 jsonb 为例,来看一看它是如何进行增删查改的。
首先,我们新建测试表:
CREATE TABLE movie (
id serial PRIMARY KEY,
info jsonb
);
在 movie 表中,id 是自增的主键,而 info 字段是我们的主角,数据类型是jsonb。
由于 id 是 serial 类型,即自增,因此我们只需插入 info 数据即可:
INSERT INTO movie (info)
VALUES('{ "title": "我是路人甲", "rate": 7.4, "category": ["剧情","喜剧"]}'),
('{ "title": "铁拳","rate": 7.1, "category": ["剧情","动作","运动"]}');
在数据插入的时候,会地将字符串转化为 jsonb 类型存储,当然如果插入的数据不满足 json 格式会报错。
Postgre 我们以 json 路径的形式来 json 数据,如 info 下的 title 字段:
SELECT info->'title' FROM movie;
?column?
--------------
"我是路人甲"
"铁拳"
上面,我们使用了->
名的方式,访问到了title
,当然你也可以这样访问:
SELECT info->>'title' FROM movie;
?column?
------------
我是路人甲
铁拳
->
与->>
二者是有区别的,->
返回的是 jsonb 类型,而->>
返回的是文本类型。
我们还可以通过下标来返回数组对象:
SELECT info->'category'-> from movie;
?column?
----------
"剧情"
"剧情"
Postgre 还路径数组的形式来访问数据:
SELECT info#>array['category','1'] from movie;
?column?
----------
"喜剧"
"动作"
我们也可以通过 Update 指令,来 json 字段:
UPDATE movie SET info = info || '{"showtime": 2015.0}'::jsonb WHERE id = ;
id | info
----+----------------------------------------------------------------------------------------
1 | {"rate": 7.4, "title": "我是路人甲", "category": ["剧情", "喜剧"], "showtime": 2015.0}
jsonb ||
操作符来合并 jsonb 字段,但json
类型由于是文本格式,所以这种方式,你只能重新 SET 新的文本。
通过-
我们可以 jsonb 中的某个字段:
UPDATE movie SET info = info - 'showtime'WHERE id = ;
id | info
----+--------------------------------------------------------------------
1 | {"rate": 7.4, "title": "我是路人甲", "category": ["剧情", "喜剧"]}
我们可以直接通过 Delete 指令来记录,但是一般不能所有记录,因此我们需要搭配 Where 来。
那么 Where 如何来过滤jsonb
字段里面的值了?
jsonb
多种匹配运算符,常见的有:
如我们可以使用@>
来为我是路人甲
的电影评分:
SELECT info->'rate' FROM movie WHERE info @> '{"title":"我是路人甲"}';
?column?
----------
7.4
因此,我们也可以使用这样的方式来:
DELETE FROM movie WHERE info @> '{"title":"我是路人甲"}';
注意: Postgre 的 JSON 数据类型操作实则很复杂,需要大量的篇章来介绍,我们无法在实战小节来覆盖,如果你感兴趣,可以阅读官方文档,或者 。
前面我们说到,与 json 类型相比,jsonb 额外索引,这也是为什么推荐你使用 jsonb 的原因,因为数据量一旦大起来,没有索引
的会十分缓慢。
jsonb 创建索引也十分简单,以上面的 movie 表为例:
CREATE INDEX movie_info_gin_index ON movie USING gin(info);
movie_info_gin_index
是索引,gin(info)
括号里面的 info 表示使用 movie 表中的 info 字段创建索引。
jsonb 上的 gin 索引操作有一定的限制,它以下几个操作符:
例如以下将会使用索引:
包含 title 为 铁拳的记录。
SELECT info FROM movie WHERE info @> '{"title":"铁拳"}';
包含 title 的记录。
SELECT info FROM movie WHERE info ? 'title';
包含 title 或 category 的记录。
SELECT info FROM movie WHERE info ?| array['title','category'];
但是如果你使用->>
操作符,则不会走索引。
SELECT info FROM movie WHERE info->>'title' = '铁拳';
若要->>
索引,你必须为它也建立单独的索引,如下:
CREATE INDEX movie_info_title_index ON movie USING btree((info ->> 'title'));
关于 Postgre JSON 的介绍到这里也将告一段落了,我们总结一下: