在使用上，能達到以下事項：
- 欄位能用 JSON 資料型態，以及內容的操作
- 欄位能用 JSONB 資料型態，以及內容的操作 --- 對應到 MongoDB 的 BSON
- 一般表格以 JSON 格式匯出資料庫
- JSON 檔匯入資料庫的普通表格
- 對於 MongoDB 提供的 Foreign Data Wrapper 套件，mongo_fdw
對於 JSON/JSONB 欄位，截至 9.5 版為止，已經有以下功能
- 存/取 JSON 檔，包含
- JSON/JSONB 欄位的存取
- 把 SQL 表格以 JSON 檔案格式匯出
- 新增修改刪除 JSON 檔內的 Value
- 合併 JSON/JSONB 檔
- JSONB 格式化輸出
- 對 JSON 裡面 Attribute（Key） 增加 Index
- 對整個 JSONB 欄位建立 Index
- JSON 的 JOIN 操作：將 JSON Field 和 SQL Column 進行 Join
除了 JSON 資料型態之外，還有類似 MongoDB 的 BSON 儲存格式的資料型態，JSONB。JSONB 在 9.4 版推出，提供 JSON 資料操作上更進一步的功能支援。
JSON 和 JSONB 的差別：
JSON
|
JSONB
|
儲存方式：純文字檔
內容：完整依照輸入資料
操作：可以查詢
索引建立：類似單純的 TEXT 欄位，建的索引會比較慢一點
類比到 MongoDB：JSON 模式
|
儲存方式：二進位串流形式（類似 BYTEA）
內容：儲存前會裁剪掉空格、換行等功能上沒有作用的字元
操作：可以新增、修改、刪除裡面的 Key-Value
索引建立：比較有效
比到 MongoDB：BSON 模式
|
雖然 JSONB 與 MongoDB 的 BSON 相類似，指的都是二進位串流資料格式，不過據說 MongoDB 的 BSON 格式與 PostgreSQL 的 JSONB 格式還是有一些小差異在～有興趣的人可以再深入了解，在這筆記中就不探究了。
接著簡介 JSON/JSONB 的相關功能。
首先是建立能夠儲存 JSON 資料的表格，然後塞個資料
postgres=# CREATE TABLE mybook(
id BIGSERIAL PRIMARY KEY,
data JSONB );
CREATE TABLE
postgres=# INSERT INTO mybook (data) VALUES (
'{
"title": "冰與火之歌",
"author": "George R. R. Martin",
"parts": {
"part1": "A Game of Thrones",
"part2": "A Clash of Kings",
"part3": "A Storm of Swords"
}
}'
);
INSERT 0 1
示範一個簡單的資料存取範例：這裡從 JSONB 欄位取出 JSON；由於 JSONB 有過濾掉一些排版字元，因此在這裡用 jsonb_pretty() 函數
postgres=# SELECT id, jsonb_pretty(data)
FROM mybook;
id | jsonb_pretty
----+-------------------------------------
1 | { +
| "parts": { +
| "part1": "A Game of Thrones",+
| "part2": "A Clash of Kings", +
| "part3": "A Storm of Swords" +
| }, +
| "title": "冰與火之歌", +
| "author": "George R. R. Martin" +
| }
(1 row)
接著，以下示範一般表格用 JSON 格式匯出，以及 JSON 資料攤開成表格欄位的匯入
edb=# SELECT row_to_json(r) FROM (
SELECT * FROM dept) r;
row_to_json
-----------------------------------------------------
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK"}
{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"}
{"deptno":30,"dname":"SALES","loc":"CHICAGO"}
{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON"}
(4 rows)
在 Postgres 裡面使用 JSON/JSONB 欄位的一個明顯的優勢在於，可以針對 JSON 資料建立索引（Index），加快資料查詢速度。對 JSON 資料可以用的索引有兩種用法：
- 對 JSON 裡面的 Key 建立 Index：可以用 BTree Index
- 對 JSON 欄位建立 Index：可以用 GIN Index（全文檢索的 Index）、BRIN Index（用在不會有 Update/Delete 的刪改、單調增長的資料表格）
CREATE INDEX ON mybook( ( ( data ->'author' ) ) );
CREATE INDEX ON mybook USING gin(data);
以上對 JSON 支援的介紹到這。
至於在 PostgreSQL JSON 功能和 MongoDB 使用上（用 MongoDB 3.x 為準）的比較，目前我整理的出來的有以下幾點：
- 用 PostgreSQL 都是用 SQL 語法操作資料，而 MongoDB 則否。
- PostgreSQL 需要事前規劃一個具有 JSON/JSONB 欄位的表格；MongoDB 則隨自己的意，塞 JSON 資料，無須事前規劃。
- 資料庫的水平擴充能力上，使用 MongoDB 比 PostgreSQL 9.x 容易的多
- 比起 MongoDB，使用 PostgreSQL 較容易對 JSON 資料內容作一些 Aggregation 操作。因此要在資料庫端先對資料作處理時，在 PostgreSQL 較「簡易」一些。要注意的是，MongoDB 也還是做的到這些功能的～
如果有 PostgreSQL 和 MongoDB 交換資料的使用需求，可以參考先前有關 MongoDB Foreign Data Wrapper 的筆記。
最後，到這篇筆記整理完之時，PostgreSQL 9.6 版釋出，在這版本對 JSON 資料比較明顯的新增功能是，支援 jsonb_insert() 函數，能夠輸入 JSONB 資料。有興趣的人可以進一步去了解～
參考
SQL vs. NoSQL KO. Postgres vs. Mongo - AirPair
When should I use MongoDB instead of PostgreSQL? (in web projects) - Quora
FDW
其他
在 Postgres 上面使用 MongoDB 的 BSON 型態：GitHub - maciekgajewski/postgresbson: BSON support for PostgreSQL
沒有留言:
張貼留言