實(shí)戰(zhàn)7：PostgreSQL JSON 數(shù)據(jù)類型大探險(xiǎn)

1. 前言

在正式的小節(jié)學(xué)習(xí)之前，我們先來探討一個問題，你究竟是否有必要使用類似于MongoDB這樣的文檔性數(shù)據(jù)庫？

這些年，NoSQL以及NewSQL都刮起過一番浪潮，而SQL終究還是巋然不動，不僅沒有被打垮，反而變得更加大。PostgreSQL號稱世界上最先進(jìn)的關(guān)系數(shù)據(jù)庫，很早的時(shí)候便已經(jīng)開始支持文檔性數(shù)據(jù)類型了，而且在9.3以后的每一個版本，都提供了更多的新特性。

PostgreSQL 最重要的文檔性數(shù)據(jù)類型就是JSON了，與 MongoDB 的BSON相比較，PostgreSQL 或許更加強(qiáng)大，因?yàn)樗芘c原有的關(guān)系性范式兼容，給數(shù)據(jù)庫存儲與維護(hù)帶來了更多的可行性和便利性。

PostgreSQL 的JSON類型功能十分強(qiáng)大，不僅支持基本的增刪查改，屬性判斷，還支持索引和搜索；當(dāng)然 MongoDB 也有無可替代的特性，不僅功能強(qiáng)大，而且天然分布式。如果你對文檔數(shù)據(jù)存儲的特性并沒有太高的要求，且需要與原來的關(guān)系數(shù)據(jù)庫兼容，那么 PostgreSQL 或許是你更好的選擇。

2. JSON 數(shù)據(jù)類型

JSON 數(shù)據(jù)類型幾乎已經(jīng)是現(xiàn)在Web開發(fā)的標(biāo)配了，MySQL在5.7以后也提供了它的支持，不過即使到現(xiàn)在，MySQL 對于 JSON 的支持也有限，而 PostgreSQL 對 JSON 的支持十分強(qiáng)大。

PostgreSQL 在 9.3 版本對 JSON 做了顯著功能增強(qiáng)外，在 9.4 引入了JSONB類型，JSONB 類型是 JSON 類型的二進(jìn)制版，不僅存儲空間更小，性能更好，而且還支持索引，在 12 這個大版本中，直接引入了 JSON path 特性來方便的操作 JSON 數(shù)據(jù)，讓 JSON 的操作更加方便和有效。

接下來，就讓我們一起來學(xué)習(xí) PostgreSQL 的 JSON 類型吧。

提示： 本文使用的 PostgreSQL 版本為12.1。

3. json 與 jsonb

PostgreSQL 支持兩種 JSON 數(shù)據(jù)類型：json與jsonb。二者在使用上幾乎無差異，主要區(qū)別是 json 存儲的是文本格式，而 jsonb 存儲的是二進(jìn)制格式。因此：

• json 在插入時(shí)不需要額外處理，而 jsonb 需要處理為二進(jìn)制，所以 json 的插入比 jsonb 要快；
• jsonb 以二進(jìn)制來存儲已經(jīng)解析好的數(shù)據(jù)，在檢索的時(shí)候不需要再額外處理，因此檢索的性能比 json 要好；
• 另外 jsonb 支持索引，若無特殊需求，推薦使用 jsonb。

我們來實(shí)操一下二者的使用吧。

3.1 使用 json

首先，我們看一下 json：

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

             json
-------------------------------
 {"username":"pedro","age":23}

在 PostgreSQL 中::符號用于類型轉(zhuǎn)換，該語句將字符串'{"username":"pedro","age":23}'，通過類型轉(zhuǎn)換為json，得到了 json 數(shù)據(jù)結(jié)果。

前面，我們談到 json 以文本格式存儲數(shù)據(jù)，且插入較快，那么是不是真的如此了？

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

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

3.2 使用 jsonb

從結(jié)果可以看出 json 確實(shí)以文本格式存儲了數(shù)據(jù)，多余的空格依舊存在，那么再看 jsonb：

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

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

可以看到，jsonb 處理多余的空格，因此消耗的時(shí)候多了那么一點(diǎn)，在實(shí)際的測試中，json 的插入性能確實(shí)比 jsonb 要高。

4. JSON 類型增刪查改

由于 json 和 jsonb 的操作幾乎一致，但 jsonb 更為增大，支持更多的特性，因此我們以 jsonb 為例，來看一看它是如何進(jìn)行增刪查改的。

首先，我們新建測試表：

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

在 movie 表中，id 是自增的主鍵，而 info 字段是我們的主角，數(shù)據(jù)類型是jsonb。

4.1 增

由于 id 是 serial 類型，即自增，因此我們只需插入 info 數(shù)據(jù)即可：

INSERT INTO movie (info)
VALUES('{ "title": "我是路人甲", "rate": 7.4, "category": ["劇情","喜劇"]}'),
('{ "title": "鐵拳","rate": 7.1, "category": ["劇情","動作","運(yùn)動"]}');

在數(shù)據(jù)插入的時(shí)候，數(shù)據(jù)庫會自動地將字符串轉(zhuǎn)化為 jsonb 類型存儲，當(dāng)然如果插入的數(shù)據(jù)不滿足 json 格式會報(bào)錯。

4.2 查

4.2.1 json 路徑操作符查詢

PostgreSQL 支持我們以 json 路徑的形式來查詢 json 數(shù)據(jù)，如查詢 info 下的 title 字段：

SELECT info->'title' FROM movie;

  ?column?
--------------
 "我是路人甲"
 "鐵拳"

上面，我們使用了->加上屬性名的方式，訪問到了title，當(dāng)然你也可以這樣訪問：

SELECT info->>'title' FROM movie;

  ?column?
------------
 我是路人甲
 鐵拳

->與->>二者是有區(qū)別的，->返回的是 jsonb 類型，而->>返回的是文本類型。

我們還可以通過下標(biāo)來返回?cái)?shù)組對象：

SELECT info->'category'->0 from movie;

 ?column?
----------
 "劇情"
 "劇情"

4.2.2 json 路徑數(shù)組查詢

PostgreSQL 還支持路徑數(shù)組的形式來訪問數(shù)據(jù)：

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 匹配運(yùn)算符

jsonb支持多種匹配運(yùn)算符，常見的有：

4.4.2 使用匹配運(yùn)算符

如我們可以使用@>來查詢名稱為我是路人甲的電影評分：

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

 ?column?
----------
 7.4

因此，我們也可以使用這樣的方式來刪除：

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

注意： PostgreSQL 的 JSON 數(shù)據(jù)類型操作實(shí)則很復(fù)雜，需要大量的篇章來介紹，我們無法在一個實(shí)戰(zhàn)小節(jié)來覆蓋，如果你感興趣，可以閱讀官方文檔，或者 PostgreSQL中文網(wǎng)。

5. jsonb 索引

前面我們說到，與 json 類型相比，jsonb 額外支持索引，這也是為什么推薦你使用 jsonb 的原因，因?yàn)閿?shù)據(jù)量一旦大起來，沒有索引

的查詢會十分緩慢。

5.1 創(chuàng)建 jsonb 索引

jsonb 創(chuàng)建索引也十分簡單，以上面的 movie 表為例：

CREATE INDEX movie_info_gin_index ON movie USING gin(info);

movie_info_gin_index是索引名稱，gin(info)括號里面的 info 表示使用 movie 表中的 info 字段創(chuàng)建索引。

5.2 索引操作

jsonb 上的 gin 索引操作有一定的限制，它支持以下幾個操作符：

• 包含關(guān)系判定符@>：判斷 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' = '鐵拳';

若要支持->>索引，你必須為它也建立單獨(dú)的索引，如下：

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

6. 小結(jié)

關(guān)于 PostgreSQL JSON 的介紹到這里也將告一段落了，我們總結(jié)一下：

• jsonb 的支持明顯優(yōu)于 json，推薦你在第一位上選擇jsonb。
• PostgreSQL 在 json 上的支持完全能夠媲美 MongoDB 等 NoSQL 數(shù)據(jù)庫，你完全可以嘗試一下。
• PostgreSQL JSON 的知識點(diǎn)真的很多，本小節(jié)介紹了常用的，如果你有興趣，可以查閱一番官網(wǎng)。