数据库 / 实战7:PostgreSQL JSON数据类型大探

实战7:PostgreSQL JSON 数据类型大探险

1. 前言

在正式的小节学习之前,我们先来探讨一个问题,你究竟是否有必要使用类似于MongoDB这样的文档性数据库?

这些年,NoSQL以及NewSQL都刮起过一番浪潮,而SQL终究还是岿然不动,不仅没有被打垮,反而变得更加大。PostgreSQL号称世界上最先进的关系数据库,很早的时候便已经开始支持文档性数据类型了,而且在9.3以后的每一个版本,都提供了更多的新特性。

PostgreSQL 最重要的文档性数据类型就是JSON了,与 MongoDB 的BSON相比较,PostgreSQL 或许更加强大,因为它能与原有的关系性范式兼容,给数据库存储与维护带来了更多的可行性和便利性。

PostgreSQL 的JSON类型功能十分强大,不仅支持基本的增删查改,属性判断,还支持索引和搜索;当然 MongoDB 也有无可替代的特性,不仅功能强大,而且天然分布式。如果你对文档数据存储的特性并没有太高的要求,且需要与原来的关系数据库兼容,那么 PostgreSQL 或许是你更好的选择。

2. JSON 数据类型

JSON 数据类型几乎已经是现在Web开发的标配了,MySQL5.7以后也提供了它的支持,不过即使到现在,MySQL 对于 JSON 的支持也有限,而 PostgreSQL 对 JSON 的支持十分强大。

PostgreSQL 在 9.3 版本对 JSON 做了显著功能增强外,在 9.4 引入了JSONB类型,JSONB 类型是 JSON 类型的二进制版,不仅存储空间更小,性能更好,而且还支持索引,在 12 这个大版本中,直接引入了 JSON path 特性来方便的操作 JSON 数据,让 JSON 的操作更加方便和有效。

接下来,就让我们一起来学习 PostgreSQL 的 JSON 类型吧。

提示: 本文使用的 PostgreSQL 版本为12.1

3. json 与 jsonb

PostgreSQL 支持两种 JSON 数据类型:jsonjsonb。二者在使用上几乎无差异,主要区别是 json 存储的是文本格式,而 jsonb 存储的是二进制格式。因此:

  • json 在插入时不需要额外处理,而 jsonb 需要处理为二进制,所以 json 的插入比 jsonb 要快;
  • jsonb 以二进制来存储已经解析好的数据,在检索的时候不需要再额外处理,因此检索的性能比 json 要好;
  • 另外 jsonb 支持索引,若无特殊需求,推荐使用 jsonb。

我们来实操一下二者的使用吧。

3.1 使用 json

首先,我们看一下 json:

SELECT '{"username":"pedro","age":23}'::json;
             json
-------------------------------
 {"username":"pedro","age":23}

在 PostgreSQL 中::符号用于类型转换,该语句将字符串'{"username":"pedro","age":23}',通过类型转换为json,得到了 json 数据结果。

前面,我们谈到 json 以文本格式存储数据,且插入较快,那么是不是真的如此了?

SELECT '{"username":"pedro",    "age":     23}'::json;
                  json
----------------------------------------
 {"username":"pedro",    "age":     23}
Time: 0.221 ms

3.2 使用 jsonb

从结果可以看出 json 确实以文本格式存储了数据,多余的空格依旧存在,那么再看 jsonb:

SELECT '{"username":"pedro",    "age":     23}'::jsonb;
              jsonb
----------------------------------
 {"age": 23, "username": "pedro"}
Time: 0.265 ms

可以看到,jsonb 处理多余的空格,因此消耗的时候多了那么一点,在实际的测试中,json 的插入性能确实比 jsonb 要高。

4. JSON 类型增删查改

由于 json 和 jsonb 的操作几乎一致,但 jsonb 更为增大,支持更多的特性,因此我们以 jsonb 为例,来看一看它是如何进行增删查改的。

首先,我们新建测试表:

CREATE TABLE movie (
  id serial PRIMARY KEY,
  info jsonb
);

在 movie 表中,id 是自增的主键,而 info 字段是我们的主角,数据类型是jsonb。

4.1 增

由于 id 是 serial 类型,即自增,因此我们只需插入 info 数据即可:

INSERT INTO movie (info)
VALUES('{ "title": "我是路人甲", "rate": 7.4, "category": ["剧情","喜剧"]}'),
('{ "title": "铁拳","rate": 7.1, "category": ["剧情","动作","运动"]}');

在数据插入的时候,数据库会自动地将字符串转化为 jsonb 类型存储,当然如果插入的数据不满足 json 格式会报错。

4.2 查

4.2.1 json 路径操作符查询

PostgreSQL 支持我们以 json 路径的形式来查询 json 数据,如查询 info 下的 title 字段:

SELECT info->'title' FROM movie;
  ?column?
--------------
 "我是路人甲"
 "铁拳"

上面,我们使用了->加上属性名的方式,访问到了title,当然你也可以这样访问:

SELECT info->>'title' FROM movie;
  ?column?
------------
 我是路人甲
 铁拳

->->>二者是有区别的,->返回的是 jsonb 类型,而->>返回的是文本类型。

我们还可以通过下标来返回数组对象:

SELECT info->'category'->0 from movie;
 ?column?
----------
 "剧情"
 "剧情"

4.2.2 json 路径数组查询

PostgreSQL 还支持路径数组的形式来访问数据:

SELECT info#>array['category','1'] from movie;
 ?column?
----------
 "喜剧"
 "动作"

4.3 改

4.3.1 添加 json 字段

我们也可以通过 Update 指令,来添加 json 字段:

UPDATE movie SET info = info || '{"showtime": 2015.0}'::jsonb WHERE id = 1;
 id |                                          info
----+----------------------------------------------------------------------------------------
  1 | {"rate": 7.4, "title": "我是路人甲", "category": ["剧情", "喜剧"], "showtime": 2015.0}

jsonb 支持||操作符来合并 jsonb 字段,但json类型由于是文本格式,所以不支持这种方式,你只能重新 SET 新的文本。

4.3.2 删除 json 字段

通过-我们可以删除 jsonb 中的某个字段:

UPDATE movie SET info = info - 'showtime'WHERE id = 1;
 id |                                info
----+--------------------------------------------------------------------
  1 | {"rate": 7.4, "title": "我是路人甲", "category": ["剧情", "喜剧"]}

4.4 删

我们可以直接通过 Delete 指令来删除记录,但是一般不能删除所有记录,因此我们需要搭配 Where 来删除。

那么 Where 如何来过滤jsonb字段里面的值了?

4.4.1 jsonb 匹配运算符

jsonb支持多种匹配运算符,常见的有:

匹配运算符 作用 说明
= 等值比较 比较两个 json 是否相等
@> 包含关系判定符 判断 json 中是有含有某些字段
<@ 被包含关系判定符 判断 json 是否被另一个 json 包含
? 键值存在判定符 判断 json 中是否存在某个键

4.4.2 使用匹配运算符

如我们可以使用@>来查询名称为我是路人甲的电影评分:

SELECT info->'rate' FROM movie WHERE info @> '{"title":"我是路人甲"}';
 ?column?
----------
 7.4

因此,我们也可以使用这样的方式来删除:

DELETE FROM movie WHERE info @> '{"title":"我是路人甲"}';

注意: PostgreSQL 的 JSON 数据类型操作实则很复杂,需要大量的篇章来介绍,我们无法在一个实战小节来覆盖,如果你感兴趣,可以阅读官方文档,或者 PostgreSQL中文网

5. jsonb 索引

前面我们说到,与 json 类型相比,jsonb 额外支持索引,这也是为什么推荐你使用 jsonb 的原因,因为数据量一旦大起来,没有索引

的查询会十分缓慢。

5.1 创建 jsonb 索引

jsonb 创建索引也十分简单,以上面的 movie 表为例:

CREATE INDEX movie_info_gin_index ON movie USING gin(info);

movie_info_gin_index是索引名称,gin(info)括号里面的 info 表示使用 movie 表中的 info 字段创建索引。

5.2 索引操作

jsonb 上的 gin 索引操作有一定的限制,它支持以下几个操作符:

  • 包含关系判定符@>:判断 json 中是有含有某些字段
  • 键值存在判定符?:判断 json 中是否存在某个键
  • 一组键值均存在判定符?&:判断 json 中是否存在一组键
  • 一组键值任意一个存在判定符?|:判断 json 中是否存在一组键中的任意一个键

5.3 使用索引

例如以下查询将会使用索引:

查询包含 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'];

5.4 额外索引

但是如果你使用->>操作符,则不会走索引。

SELECT info FROM movie WHERE info->>'title' = '铁拳';

若要支持->>索引,你必须为它也建立单独的索引,如下:

CREATE INDEX movie_info_title_index ON movie USING btree((info ->> 'title'));

6. 小结

关于 PostgreSQL JSON 的介绍到这里也将告一段落了,我们总结一下:

  • jsonb 的支持明显优于 json,推荐你在第一位上选择jsonb
  • PostgreSQL 在 json 上的支持完全能够媲美 MongoDB 等 NoSQL 数据库,你完全可以尝试一下。
  • PostgreSQL JSON 的知识点真的很多,本小节介绍了常用的,如果你有兴趣,可以查阅一番官网