MySQL 和 PostgreSQL 相比，對 JSON 的支持如何？

01-13

Pg 對 json 的支持已經比較成熟, 除了把結果轉換成 json 外, 還可以:

按 json field 的內容作為查詢條件
以 json field 的內容建立索引
把 json 和 result row 相互轉換
甚至還能把 json 當成數據集和視圖
甚至迭代 json 內容做循環
...

Pg 也有比 MySQL 強大很多的編程能力, 可以在 Pg 中完成比較複雜的邏輯, 配合 OpenResty 甚至只用幾個 nginx module 搭建輕量級高性能的微服務也是沒問題的.

MySQL 的 json 支持還才起步. 不過晚有晚的好處, 例如函數不用像 pg 那樣兼容 json 和 jsonb 寫兩套... 缺點是首先你得上 5.7, 國內落後的技術環境你知道的... 就算如此, 很多 Pg 里好使的 json 功能你是找不到 MySQL 的對應函數的. MySQL 8.0 (怎麼就跳到 8 了...) 有所增強, 加入了 JSON_ARRAYAGG() / JSON_OBJECTAGG() 可以和 Pg 的 to_json() / json_to_record() 一拼.

說回來, 對於一般的應用場景, 常用函數的使用, 其實功能區別不大...

PostgreSQL專門有bjson數據類型的，在一個json保存在欄位裡面的同時，支持很多高階的SQL查詢語法去遍歷和運算json中間的節點。在非關係型的數據類型中，MySQL可是比Postgre差的太遠了。

給大家簡單介紹一下 MySQL5.7 JSON實現的實現原理

原文鏈接：MySQL5.7 JSON實現簡介

摘要：

本文主要介紹在MySQL 5.7.7開始引入的非結構化數據類型JSON的特性以及具體的實現方式(包括存儲方式)。首先介紹為什麼要引入JSON的原生數據類型的支持；接著介紹MySQL給用戶提供的JSON操作函數，以及JSON路徑表達式語法，結合兩者，用戶可以在資料庫級別操作JSON的任意鍵值和數據；之後，重點介紹JSON在伺服器側的存儲結構，這也是深入理解很多其他JSON特性的根基；在最後介紹JSON作為新數據類型的比較與排序規則之前，介紹了對JSON類型數據建立索引的原理。

全文索引：

為什麼JSON 原生支持？

1、文檔合法性

2、更有效的訪問

3、性能優化

JSON的操作介面及路徑表達式

1、JSON的操作介面

2、JSON路徑表達式

JSON的存儲結構及具體實現

JSON的索引

JSON比較與排序

小結

本文主要介紹了MySQL在5.7.7之後引入的原生JSON支持的特性，說明了引入JSON類型的好處，並結合具體的示例介紹了MySQL在JSON類型上對外的介面以及引入的新語法規則。此外，還重點介紹了JSON在硬碟上的存儲結構，簡要分析了這種存儲結構的優勢和不足。最後還介紹了JSON的索引原理，以及比較和排序規則。相信理解了本文介紹的內容，關於JSON文中沒有提到的部分內容也較容易理解。

閱讀原文，請點擊 MySQL5.7 JSON實現簡介

簡單來說，mysql不支持json（僅僅把數據json encode保存起來不算)，而postgresql支持得非常好

PostgreSQL: Documentation: 9.5: JSON Types

MySQL 5.7之後的版本支持JSON 數據類型。

json支持本來就是末端需求，一個軟體設計系統到了靠末端功能解決軟體問題的時候，首先思考一下軟體設計是不是有問題，而且這種設計明顯要軟體依附於某種dbms的特定功能，豈不鼠目寸光，站在看問題的位置太lower，任何資料庫管理人員優化資料庫的一個基本原則是：對資料庫的操作是越少越好，而不是相反！

更不要迷信所謂的PG 比MySQL強太多的說法，世界上存在7年以上的資料庫絕大部分基本我都用過，PG是個人認為設計最呆板，最莫名奇妙的一種dbms，把簡單的東西弄的很煩就是PG的一個亮點，原因是最初是拿來做教材演示用的。理論上看是實現了不少功能，真正好用實用的不多，PG從來不是一個工程化的資料庫商業系統像 ORACLE ，MS sqlserver（非sydbase），只是看起來做的功能比較多而已，比如GIS的數據類型支持。PG幾個不好的地方：備份恢復落後、redo日誌機制更是奇葩、schema和user設計的很垃圾、管理效率不高、研究探討性文檔稀少、不但會用的不多，高手也沒幾個，優化大型項目管理沒有積累這點尤其重要：ORACLE、mysql在人才這方面都是非常深厚的積累，項目誤用PG，可能後面越來越被動，這種例子在深圳已經看到幾起了，正在焦頭爛額之中煎熬，不換煎熬，換的話工程實施又很麻煩。

Json栗子一個，這種功能有什麼難的么：

mysql&>

mysql&> SELECT NAME ,telno_s-&>"$.tel_1",telno_s-&>"$.tel_2",telno_s-&>"$.tel_4",telno_s-&>"$.tel_5",lastlogininfo -&>"$.ip"

FROM USER ;

+------+--------------------+--------------------+--------------------+--------------------+------------------------+

+------+--------------------+--------------------+--------------------+--------------------+------------------------+

| lucy | "13788812345" | "13888880000" | NULL | "15000001234" | "192.168.1.1" |

| Tom | 13988812345 | 17011110000 | "13300001111" | NULL | "192.168.1.1" |

+------+--------------------+--------------------+--------------------+--------------------+------------------------+

2 rows in set (0.01 sec)

mysql的json發展年限太短，跟 pgsql 沒有什麼可比性，等幾次迭代之後再說；

另外如果有人說 mysql 的 json 比 pgsql，那該多讀點書，別出來誤導別人

摘自 https://n3xtchen.github.io/n3xtchen/postgresql/2016/04/24/postgresql-json

PostgreSQL - 9.5 會成為你的下一個 JSON 資料庫?

24 April 2016

TL;DR: 是的，但這不是一個好的問題。

就在一年前，我們提出問題「Is PostgreSQL Your Next JSON Database…」。現在，隨著 PostgreSQL-9.5 的發布，是時候驗證下 Betteridge』s law 是否仍然有效。因此，我們一起來探討下各個版本的 PostgreSQL 對 JSONB 的改進。

PostgreSQL 的 JSON 史可以追溯到 9.2。

JSON in 9.2

原始的 JSON 數據類型在 PostgreSQL-9.2 中強勢引入，但實際上只是一個被標記為 JSON 類型的文本欄位，通過解析器來處理。在 9.2 中，你只能對 JSON 中進行簡單的存取；其他的任何事情都只能使用 PL 語言來處理。在一些場景下是很有用的，但是。。。你還需要更多的功能。

為了說明，假設我們有如下 JSON 數據：

{

"title": "The Shawshank Redemption",

"num_votes": 1566874,

"rating": 9.3,

"year": "1994",

"type": "feature",

"can_rate": true,

"tconst": "tt0111161",

"image": {

"url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg",

"width": 933,

"height": 1388

}

首先，創建一張表：

CREATE TABLE filmsjson (id BIGSERIAL PRIMARY KEY, data JSON);

然後像這樣插入數據：

n3xt_pg=# INSERT INTO filmsjson (data) VALUES ("{

"title": "The Shawshank Redemption",

"num_votes": 1566874,

"rating": 9.3,

"year": "1994",

"type": "feature",

"can_rate": true,

"tconst": "tt0111161",

"image": {

"url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg",

"width": 933,

"height": 1388

}

}");

INSERT 0 1

n3xt_pg=# select * from filmsjson

postgres-# ;

id | data

---+-------------------------------------------------------------------------------------------------------------

1 | {"title": "The Shawshank Redemption", +

| "num_votes": 1566874, +

| "rating": 9.3, +

| "year": "1994", +

| "type": "feature", +

| "can_rate": true, +

| "tconst": "tt0111161", +

| "image": { +

| "url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg", +

| "width": 933, +

| "height": 1388 +

| } +

| }

(1 row)

我們的能做很受限。注意到了嗎？空格和換行都會被保留。這個在後面很重要。。。

接著到 9.3

PostgreSQL-9.3 有了新的解析器，操作符可以用於提取 JSON 數據中的值。他們中使用率最高的就是 -&> ，可以賦予整型，提取數組中的值；或者一個字元串，提取 JSON 對象成員；-&>&> 也一樣，不過他返回的是文本。我們還可以使用 #&> 和＃&>&> 來指定路徑來獲取數據。

接著，我們之前的表，我們可以進一步操作 JSON，做如下查詢：

n3xt_pg=# select data-&>"title" from filmsjson;

?column?

----------------------------

"The Shawshank Redemption"

(1 row)

n3xt_pg=# select data#&>"{image,width}" from filmsjson;

?column?

----------

933

(1 row)

路徑實際上就是一個 key 列表來遍歷 JSON 文檔的。不要以為花括弧只是用來展示 JSON 的 —— 他實際上是一個數組的字畫量，在 PostgreSQL 中解釋成 text[]。這個意味和下面的查詢等價：

n3xt_pg=# select data#&>ARRAY["image", "width"] from filmsjson;

?column?

----------

933

(1 row)

雖然加入了很多功能函數，但是仍然很受限。它不允許複雜的查詢，不能在特殊類型使用索引，而且只能創建新的 JSON 元素。另外，最嚴重的問題就是每次查詢都要對文本欄位進行實時解析，這樣做相當的低效。

切到 9.4

PostgreSQL-9.4 引入了新的 JSON 類型是 JSONB。JSONB 是 JSON 的二進位編碼版本，它高效地存儲著鍵值。這意味著所有的空格都會被刪除。缺點就是你不能在同級創建重複的 key(不知道這個實際場景是什麼？？？)，你會失去文檔的格式。但是這個犧牲是值得的，因為任何東西都變得更高效了，不再需要實時解析。它同時也拖慢了插入的速度，因為要等解析完成為止。現在來看看它們的不同，首先創建一個 JSONB 表，插入演示數據：

n3xt_pg=# CREATE TABLE filmsjsonb (id BIGSERIAL PRIMARY KEY, data JSONB);

CREATE TABLE

n3xt_pg=# SELECT * from filmsjsonb;

id | data

----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 | {"type": "feature", "year": "1994", "image": {"url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg", "width": 933, "height": 1388}, "title": "The Shawshank Redemption", "rating": 9.3, "tconst": "tt0111161", "can_rate": true, "num_votes": 1566874}

(1 row)

是的，長度非常寬。所有的空格和換行都被替換成一個空格。

雖然它們擁有很多相同的特性，但是最大的區別就是：JSONB 沒有創建函數。在 9.4 中，JSON 數據類型有一堆的創建函數：json_build_object(), json_build_array() 和 json_object(),也可以轉化成 JSONB（::jsonb）類型。它同時也反應了 PostgreSQL 開發者的使用的邏輯 —— JSON 為了準確存儲，JSONB 為的是快速，高效的查詢。因此 JSON 和 JSONB 都有 -&>, -&>&>, #&> 和 #&>&> 操作符，而 JSONB 還有有包含和存在操作符 @&>, &<@, ?, ?| 和 ?。

存在是用來檢查 key 是否存在，因此我們首先檢查下我們演示數據中是否存在 rating 欄位：

n3xt_pg=# select data-&>"title" from filmsjsonb where data ? "rating";

?column?

----------------------------

"The Shawshank Redemption"

(1 row)

但是數據中 url 不在最外層，所以無法檢索到：

n3xt_pg=# select data-&>"title" from filmsjsonb where data ? "url";

?column?

----------

(0 rows)

但是我們可以這樣子做：

n3xt_pg=# select data-&>"title" from filmsjsonb where data-&>"image" ? "url";

?column?

----------------------------

"The Shawshank Redemption"

(1 row)

?| 和 ? 對 ? 的功能進行擴展了:

n3xt_pg=# select data-&>"title" from filmsjsonb where data ?| "{"image", "rat"}"; -- 相當於 data ? "image" or data ? "rate"

?column?

----------------------------

"The Shawshank Redemption"

(1 row)

n3xt_pg=# select data-&>"title" from filmsjsonb where data ? "{"image", "rat"}"; -- 相當於 data ? "image" and data ? "rate"

?column?

----------

(0 rows)

n3xt_pg=# select data-&>"title" from filmsjsonb where data ? "{"image", "rating"}";

?column?

----------------------------

"The Shawshank Redemption"

(1 row)

? 僅用來檢查 key 存在，那麼 @&> 和 &<@ 可以檢查子串的功能。

n3xt_pg=# select "{"a":1, "b":2}"::jsonb @&> "{"b":2}"::jsonb;

?column?

----------

(1 row)

n3xt_pg=# select "{"b":2}"::jsonb &<@ "{"a":1, "b":2}"::jsonb;

?column?

----------

(1 row)

9.4 同樣也帶來了 GIN 索引類型，它覆蓋所有 JSONB 文段中的欄位。你還可以創建帶 json_path_ops 的 GIN 索引類型，它更快，更小，但是只能用於 @&> 包含操作符，用來檢查子串很有用。

因此，你可以使用 9.4 創建，檢索和索引 JSON/JSONB 數據。同時，也失去對了修改 JSON 類型數據的能力。你還可以把 JSON 數據傳遞給 PLv8 或者 PLPerl 腳本處理。因此，這些東西已經接近一個完整服務的 JSON 文檔處理環境，但是遠遠還不夠。

進入 9.5

PostgreSQL-9.5 引入了處理 JSON 的新能力：修改和操作 JSONB 數據。先來看看 jsonb_pretty() 函數，列印更可讀的 JSON：

n3xt_pg=# select jsonb_pretty("{"a":3,"b":2}"::jsonb);

jsonb_pretty

--------------

{ +

"a": 3, +

"b": 2 +

}

(1 row)

開刪

最簡單的修改莫過於刪除了。為了這個，9.5 引入了 - 和 #- 操作符。- 後面帶上 key，代表刪除 JSON 的這個 key（如果是數組，則是跟著一個整型索引）。現在，我們來試驗下：

n3xt_pg=# select jsonb_pretty(data) from filmsjsonb;

jsonb_pretty

---------------------------------------------------------------------------------------------------------------

{ +

"type": "feature", +

"year": "1994", +

"image": { +

"url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg",+

"width": 933, +

"height": 1388 +

}, +

"title": "The Shawshank Redemption", +

"rating": 9.3, +

"tconst": "tt0111161", +

"can_rate": true, +

"num_votes": 1566874 +

}

(1 row)

n3xt_pg=# update filmsjsonb set data=data-"rating";

UPDATE 1

#- 以路徑作為索引。

n3xt_pg=# update filmsjsonb set data=data#-"{image,width}";

UPDATE 1

n3xt_pg=# update filmsjsonb set data=data#-"{image,height}";

UPDATE 1

n3xt_pg=# select jsonb_pretty(data) from filmsjsonb;

jsonb_pretty

--------------------------------------------------------------------------------------------------------------

{ +

"type": "feature", +

"year": "1994", +

"image": { +

"url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg"+

}, +

"title": "The Shawshank Redemption", +

"tconst": "tt0111161", +

"can_rate": true, +

"num_votes": 1566874 +

}

(1 row)

上一個例子中，需要執行2次，會不會覺得很蛋疼呢？還好，PostgreSQL 提供了一個簡便的方法來處理：

n3xt_pg=# update filmsjsonb set data#-『{image,height}』#-『{image,width}』;

UPDATE 1

你不僅可以在刪除數據時使用，你還可以在輸出中使用這些函數（pipeline 的思想，是不是很有同感！）：

n3xt_pg=# select jsonb_pretty(data#-"{image,height}"#-"{image,width}") from

filmsjsonb where id=1;

jsonb_pretty

--------------------------------------------------------------------------------------------------------------

{ +

"type": "feature", +

"year": "1994", +

"image": { +

"url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg"+

}, +

....

合併

另一個重要的數據操作就是合併操作 ||；它合併兩個 JSONB 對象。它只能合併頂級的 key，如果兩邊都存在，它會選擇右邊那個。這意味著你也可以使用它作為一個更新機制（Replace）。開始，現在想要給我們的電影數據添加兩個 key，並賦予初始值：

n3xt_pg=# update filmsjsonb set data=data || "{"can_rate":false,"num_votes":0,"revote":true }";

UPDATE 1

n3xt_pg=# select jsonb_pretty(data) from filmsjsonb;

jsonb_pretty

--------------------------------------------------------------------------------------------------------------

{ +

"type": "feature", +

"year": "1994", +

"image": { +

"url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg"+

}, +

"title": "The Shawshank Redemption", +

"revote": true, +

"tconst": "tt0111161", +

"can_rate": false, +

"num_votes": 0 +

}

(1 row)

它通常用於合併 JSONB 數據。如果使用它來更新一個 key，那似乎就有點 overkill；所以接下里我們將要看到殺手級的函數：

jsonb_set 來幫你

jsonb_set 就是設計用來更新單一 key 值的。直接看例子吧：

n3xt_pg=# update filmsjsonb SET data = jsonb_set(data,"{"image","width"}",to_jsonb(1024)) where id=1;

UPDATE 1

它把 image.width 的值修改成 1024。jsonb_set 的參數很簡單：第一個就是你要修改的 JSONB 數據類型欄位；第二個是一個文本數組，用來指定修改的路徑；第三個參數是要替換值（可以是 JSON）。如果給的路徑不存在，json_set() 默認會創建他；如果想要禁用這個行為，那就把第四個參數設置成 false。

現在我們想為圖片添加版權屬性：

n3xt_pg=# update filmsjsonb SET data = jsonb_set(data,"{"image","quality"}","{"copyright":"company X","registered":true}");

UPDATE 1

n3xt_pg=# select jsonb_pretty(data) from filmsjsonb;

jsonb_pretty

---------------------------------------------------------------------------------------------------------------

{ +

"type": "feature", +

"year": "1994", +

"image": { +

"url": "http://ia.media-imdb.com/images/M/MV5BODU4MjU4NjIwNl5BMl5BanBnXkFtZTgwMDU2MjEyMDE@._V1_.jpg",+

"width": 1024, +

"quality": { +

"copyright": "company X", +

"registered": true +

} +

}, +

"title": "The Shawshank Redemption", +

"revote": true, +

"tconst": "tt0111161", +

"can_rate": false, +

"num_votes": 0 +

}

(1 row)

jsonb_set() 可能是 9.5 版本關於 JSONB 的最重要更新了。他為我們提供修改 JSONB 數據的方法。另外需要記住的是，我們的例子只是使用簡單的值；它還支持子查詢。

思考

所有的這些造就了 PostgreSQL 今日的有趣地位。9.5 對 PostgreSQL 的 JSON 的加強，意味著你可以使用 PostgreSQL 作為 JSON 資料庫；它足夠快，功能強大。你所需要的就是使用不同角度的思考。

例如，很多 JSON 資料庫沒有相對簡潔的 API 或者客戶端庫可用。這裡，PosgreSQL 有自己的領域語言，SQL，來操作 JSON；它能和 SQL 一起爆發出強大的力量。這意味著你仍然需要學習 SQL，不幸的是，很多人希望把它作為 NoSQL 資料庫使用。

你可以使用 PostgreSQL 創建複雜的 JSON/JSONB 文檔。但是如果你這麼做的話，你可能需要思考下你是否能更好地使用它。如果文檔的複雜度（比如嵌套的 JSON）來源於文檔之間的關係，那麼關係型模型可能是解決數據纏繞的更好選擇。關係型數據模型還有個好處，就是避免了數據重複（三範式）。

PostgreSQL 對 JSON 的完美支持消除了關係型環境中處理 JSON 數據的障礙，添加更多易用的，內建的有效函數和操作符來操作 JSONB 資料庫。

PostgreSQL-9.5 不是你的下一個 JSON 資料庫，但他是一個帶著完整 JSON 存儲方案的關係型資料庫。強化 JSON 處理的同時，還做了其他的不少改進，如 upsert，skip locked 以及更好的表隨機等等。

它可能不是你的下一個 JSON 資料庫，但是 PostgreSQL 將會是下一個你可以同時用於處理關係型和 JSON 數據的資料庫。

SELECT json_object_agg(name, value) FROM kv WHERE ...

直接構造一個 josn 返回，寫 api 服務時不知多爽

偶然一次開始了解postgres，相見恨晚，特別是json支持這一塊。鼓足勇氣就把目前在做的和已經上線的項目資料庫全部換成了pg，沒想到還異常順利。

說白了兩點

pg比mysql多支持一個預索引的jsonb格式（通過格式優化查找，並非添加索引），使得對json的操作比pg本身的json和mysql的json（它們都是存的普通文本，用的時候當場全部解析後才用）要快一些
pg比mysql對json操作的函數，提供得更多。然而這些函數本質只是一些文本（或者二進位）串的操作，並未涉及到資料庫核心的東西。就比如假設原來只有ltrim，現在再加一個rtrim一樣。

其它細節上就是資料庫本身的差距了。其實我覺得pg對json的操作能力是比不過mongodb的，就想用幾個函數能代替語言級的支持是不可能的。

json 什麼的都不重要。因為json、hstore、array的索引利用真的很差。

真正的重點是，PostgreSQL是多進程的，意味著一個實例可以跑滿128核甚至1024核。

而MySQL，只要有一個查詢阻塞了，就把整個系統鎖死了。只能搞點什麼主從分離，多實例，mysql proxy什麼的無聊架構來解決問題了。