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

1. 前言

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

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

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

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

2. JSON 数据类型

JSON 数据类型几乎已经是现在Web开发的标配了，MySQL在5.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 数据类型：json与jsonb。二者在使用上几乎无差异，主要区别是 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支持多种匹配运算符，常见的有：

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 的知识点真的很多，本小节介绍了常用的，如果你有兴趣，可以查阅一番官网。