PostgreSQL支持非關係數據類型JSON (JavaScript Object Notation)，本節介紹json類型、json與jsonb差異、json與jsonb操作符和函數，以及jsonb鍵值的追加、刪除、更新。

JSON類型簡介

PotgreSQL早在9.2版本已經提供了json類型，並且隨著大版本的演進，PostgreSQL對json的支持趨於完善，例如提供更多的json函數和操作符方便應用開發，一個簡單的json類型例子如下：

SELECT '{"a":1,"b":2}'::json;
json
---------------
{"a":1,"b":2}

創建一張表，如下所示：

CREATE TABLE test_json1 (id serial primary key,name json);

以上示例定義欄位name為json類型，插入表數據，如下所示：

INSERT INTO test_json1 (name)
VALUES ('{"col1":1,"col2":"test","col3":"male"}');
INSERT 0 1

INSERT INTO test_json1 (name)
VALUES ('{"col1":2,"col2":"fp","col3":"female"}');
INSERT 0 1

查詢表test_json1數據：

SELECT * FROM test_json1;
id | name
----+------------------------------------------
1 | {"col1":1,"col2":"francs","col3":"male"}
2 | {"col1":2,"col2":"fp","col3":"female"}
————————————————

查詢JSON數據

通過 -> 操作符可以查詢json數據的鍵值，如下所示：

SELECT name -> 'col2' FROM test_json1 WHERE id=1;
?column?
----------
"francs"
(1 row)

如果想以文本格式返回json欄位鍵值可以使用->>符，如下所示：

SELECT name ->> 'col2' FROM test_json1 WHERE id=1;
?column?
----------
francs
(1 row)

JSONB與JSON差異

PostgreSQL支持兩種JSON數據類型：json和jsonb，PostgreSQL 9.4 有加入了jsonb 類型，兩種類型在使用上幾乎完全相同，兩者主要區別為以下：

• json存儲格式為文本，而jsonb存儲格式為二進位 ，由於存儲格式的不同使得兩種json數據類型的處理效率不一樣，json類型以文本存儲並且存儲的內容和輸入數據一樣，當檢索json數據時必須重新解析，而jsonb以二進位形式存儲已解析好的數據，當檢索jsonb數據時不需要重新解析，因此json寫入比jsonb快，但檢索比jsonb慢，後面會通過測試驗證兩者讀寫性能差異。
• json與jsonb在使用過程中還存在差異，jsonb輸出的鍵的順序和輸入不一樣，而json的輸出鍵的順序和輸入完全一樣，如下所示：

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
json
-------------------------------------------------
{"bar": "baz", "balance": 7.77, "active":false}
(1 row)

• jsonb類型會去掉輸入數據中鍵值的空格，而json的輸出和輸入一樣，不會刪掉空格鍵，如下所示：

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

INSERT INTO books(client, data) values ( 'Joe', 
    '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
),( 'Jenny', 
    '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
),( 'Jenny', 
    '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
);

SELECT * FROM books;

CREATE TABLE books_1 (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

INSERT INTO books_1(client, data) values ( 'Joe', 
    '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
),( 'Jenny', 
    '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
),( 'Jenny', 
    '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
);

SELECT * FROM books_1;

• jsonb會刪除重複的鍵，僅保留最後一個，，而json數據類型會保留重複的鍵值，如下所示：

SELECT ' {"id":1,
"name":"francs",
"remark":"a good guy!",
"name":"test"
}'::jsonb;
jsonb
----------------------------------------------------
{"id": 1, "name": "test", "remark": "a good guy!"}
(1 row)

相比json大多數應用場景建議使用jsonb，除非有特殊的需求，比如對json的鍵順序有特殊的要求。

JSONB與JSON操作符

PostgreSQL支持豐富的JSONB和JSON的操作符，舉例如下：-> 以json對象形式返回， ->>以文本格式返回，如下所示：

select data -> 'title' as title from books;

select data -> 'author' ->> 'last_name' as last_name from books;

jsonb鍵/值的追加、刪除、更新

jsonb鍵/值追加可通過||操作符，如下增加sex鍵/值：

select data || '{"sex":"male"}'::jsonb FROM books

jsonb鍵/值的刪除有兩種方法，一種是通過操作符號-刪除，通過操作符號-刪除鍵/值如下：

SELECT '{"name": "James", "email": "james@localhost"}'::jsonb
- 'email';
?column?
-------------------
{"name": "James"}
(1 row)

SELECT '["red","green","blue"]'::jsonb - 0;
?column?
-------------------
["green", "blue"]

第二種方法是通過操作符#-刪除指定鍵/值，通常用於有嵌套json數據刪除的場景，如下刪除嵌套contact中的fax鍵/值：

SELECT '{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb #- '{contact,fax}'::text[];
?column?
---------------------------------------------------------
{"name": "James", "contact": {"phone": "01234 567890"}}
(1 row)

刪除嵌套aliases中的位置為1的鍵/值，如下所示：

SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];
?column?
--------------------------------------------------
{"name": "James", "aliases": ["Jamie", "J Man"]}
(1 row)

鍵/值的更新也有兩種方式，第一種方式為||操作符，||操作符可以連接json鍵，也可覆蓋重複的鍵值，如下修改age鍵的值：

SELECT '{"name":"francs","age":"31"}'::jsonb ||
'{"age":"32"}'::jsonb;
?column?
---------------------------------
{"age": "32", "name": "francs"}
(1 row)

第二種方式是通過jsonb_set函數，語法如下：

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

target：指源jsonb數據，

path：指路徑：

new_value：指更新後的鍵值；

create_missing： true表示如果鍵不存在則添加， false表示如果鍵不存在則不添加，默認值為true

示例如下：

SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);
jsonb_set
---------------------------------
{"age": "32", "name": "francs"}
(1 row)

SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,true);
jsonb_set
------------------------------------------------
{"age": "31", "sex": "male", "name": "francs"}
(1 row)

給JSONB類型創建索引

給jsonb數據類型創建索引，jsonb數據類型支持GIN索引，為了便於說明，假如一個json欄位內容如下，並且以jsonb格式存儲。

{
  "id": 1,
  "user_id": 1440933,
  "user_name": "1_francs",
  "create_time": "2023-02-03 16:22:05.528432+08"
}

假如存儲以上jsonb數據的欄位名為user_info，表名為tbl_user_jsonb，在user_info欄位上創建GIN索引語法如下：

CREATE INDEX idx_gin ON tbl_user_jsonb USING gin(user_info);

jsonb上的GIN索引支持@>、?、 ?&、?|操作符，例如以下查詢將會使用索引。

SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "1_frans"}'

但是以下基於jsonb鍵值的查詢不會走索引idx_gin，如下所示：

SELECT * FROM tbl_user_jsonb WHERE user_info->>'user_name'= '1_francs';

如果要想提升基於jsonb類型的鍵值檢索效率，可以在jsonb數據類型對應的鍵值上創建索引，如下所示：

CREATE INDEX idx_gin_user_infob_user_name ON tbl_user_jsonb USING btree

((user_info ->> 'user_name'));

創建以上索引後，上述根據user_info->>'user_name'鍵值查詢的SQL將會走索引。

JSON與JSONB讀寫性能測試

本小節將對json、jsonb讀寫性能進行簡單對比，前面介紹json、jsonb數據類型時提到了兩者讀寫性能的差異，主要表現為json寫入時比jsonb快，但檢索時比jsonb慢，主要原因為：json存儲格式為文本而jsonb存儲格式為二進位，存儲格式的不同使得兩種json數據類型的處理效率不一樣，json類型存儲的內容和輸入數據一樣，當檢索json數據時必須重新解析，而jsonb以二進位形式存儲已解析好的數據，當檢索jsonb數據時不需要重新解析。

準備測試數據

下面通過一個簡單的例子測試下json、jsonb的讀寫性能差異，計劃創建以下三張表：

`user_ini：基礎數據表，並插入200萬測試數據；

tbl_user_json:： json 數據類型表，200萬數據；

tbl_user_jsonb： jsonb 數據類型表，200萬數據；

首先創建user_ini表並插入200萬測試數據，如下：

CREATE TABLE user_ini(id int4 ,user_id int8, user_name character
varying(64),create_time timestamp(6) with time zone default
clock_timestamp());

INSERT INTO user_ini(id,user_id,user_name)
SELECT r,round(random()*2000000), r || '_francs'
FROM generate_series(1,2000000) as r;

計劃使用user_ini表數據生成json、jsonb數據，創建user_ini_json、user_ini_jsonb表，如下所示：

CREATE TABLE tbl_user_json(id serial, user_info json);

CREATE TABLE tbl_user_jsonb(id serial, user_info jsonb);

JSON與JSONB表寫性能測試

根據user_ini數據通過row_to_json函數向表user_ini_json插入200萬json數據，如下：

INSERT INTO tbl_user_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;

從以上結果看出tbl_user_json插入200萬數據花了13秒左右；接著根據user_ini表數據生成200萬jsonb數據並插入表tbl_user_jsonb，如下：

INSERT INTO tbl_user_jsonb(user_info) SELECT row_to_json(user_ini)::jsonb FROM user_ini;

從以上看出tbl_user_jsonb表插入200萬jsonb數據花了20秒左右，正好驗證了json數據寫入比jsonb快。

比較兩表占用空間大小，如下所示：

從占用空間來看，同樣的數據量jsonb數據類型占用空間比json稍大。

查詢測試

對於json、jsonb讀性能測試我們選擇基於json、jsonb鍵值查詢的場景，例如，根據user_info欄位的user_name鍵的值查詢，如下所示：

上述SQL執行時間為186毫秒左右，基於user_info欄位的user_name鍵值創建btree索引如下：

CREATE INDEX idx_jsonb ON tbl_user_jsonb USING btree ((user_info->>'user_name'));

再次執行上述查詢，如下所示：

根據上述執行計劃看出走了索引，並且SQL時間下降到0.147ms。為更好的對比tbl_user_json、tbl_user_jsonb表基於鍵值查詢的效率，計劃根據user_info欄位id鍵進行範圍掃描對比性能，創建索引如下：

 CREATE INDEX idx_gin_user_info_id ON tbl_user_json USING btree (((user_info ->> 'id')::integer));

CREATE INDEX idx_gin_user_infob_id ON tbl_user_jsonb USING btree (((user_info ->> 'id')::integer));

根據以上看出，查詢表tbl_user_json的user_info欄位id鍵值在1到10000範圍內的記錄走了索引，並且執行時間為27毫秒，而tbl_user_jsonb的執行時間為13毫秒，從這個測試看出jsonb檢索比json效率高。

從以上兩個測試看出，正好驗證了「json寫入比jsonb快，但檢索時比jsonb慢」的觀點，值得一提的是如果需要通過key/value進行檢索，例如以下。

SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_test"}';

這時執行計劃為全表掃描，如下所示：

從以上看出執行時間為582毫秒左右，在tbl_user_jsonb欄位user_info上創建gin索引，如下所示：

CREATE INDEX idx_tbl_user_jsonb_user_Info ON tbl_user_jsonb USING gin (user_Info);

索引創建後，再次執行以下，如下所示：

從以上看出走了索引，並且執行時間下降到了0.185毫秒。

JSONB與JSON函數

row_to_json()

能夠將行作為json對象返回，此函數常用來生成json測試數據，比如將一個普通錶轉換成json類型表：

SELECT row_to_json(books) from books

json_each()

json與jsonb相關的函數非常豐富，舉例如下： 擴展最外層的json對象成為一組鍵/值結果集，如下所示：

-- 鍵/值結果集
select jsonb_each(data) from books where id =1
-- 文本形式返
select jsonb_each_text(data) from books where id =1

json_object_keys()

返回最外層的json對像中的鍵的集合，如下所示：

update 待更新表 a
set 待更新表_欄位一 = b.欄位一 ,
待更新表_欄位二 = b.欄位二
from 關聯表 b 
where  a.關聯欄位= b.關聯欄位

eg:

update test001 a 
set name= b.name,age=b.age
from test002 b 
where  a.id= b.id

row_to_json()

將表的所有欄位值以json的形式返回，如下所示：

但是有時候我們只需要查詢指定的列，那麼我們可以使用 row() 結構函數，如下所示：

雖然返回了指定列，但是缺遺失了欄位名稱，如需達到這樣的效果，如下所示：