實戰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 的知識點真的很多，本小節介紹了常用的，如果你有興趣，可以查閱一番官網。