背景環境
作為一種簡單易用的非結構化資料，JSON格式的應用場景非常廣泛。在當前的大資料環境下，處理非結構化資料的需求越來越頻繁，我們是不是必須用MongoDB這一類NoSQL的解決方案？強大的PostgreSQL資料庫，在RDBMS的基礎上提供了對JSON的完善支援，不需要MongoDB也可以玩轉JSON。
PostgreSQL-9.2中引入了對JSON型別的支援，經過幾個大版本的進化，目前對JSON數字型別的支援已經比較完善。在PG中對JSON格式資訊的CRUD操作，針對具體的節點建立索引，這些都可以很容易的實現。
本次我們測試在PG中使用JSON的常見場景，軟體環境如下
CentOS 7 x64
PostgreSQL 11.1
兩種資料型別
PG中提供了兩種不同的資料型別，分別是JSON和JSONB。顧名思義，JSON是儲存字串的原始格式，而JSONB是二進位制編碼版本。JSON需要儲存包括空格等原始格式，所以在每次查詢的時候都會有解析過程。而JSONB查詢時不需要實時解析，所以更高效。
簡而言之，JSON 為了準確儲存，插入快查詢慢；JSONB 為了高效查詢，插入慢檢索快。
如果沒有特殊理由，最好使用JSONB型別。
-- 使用 JSONB 欄位型別（無特殊需求不要使用JSON型別）
drop table if exists demo.j_waybill;
create table demo.j_waybill (id int primary key, data jsonb);
insert into demo.j_waybill(id, data) values(1,
'
{
"waybill": 2019000000,
"project": "測試專案",
"pay_org_name": "ABC製造廠",
"driver": {
"name": "張三",
"mobile": 13800000000
},
"line": {
"from": {"province":"河北省", "city":"唐山市", "district":"豐潤區"},
"to": {"province":"四川省", "city":"綿陽市", "district":"市轄區"}
},
"payment": {
"oil_amount": 1234,
"cash_amount": 5678
}
}
'
);
複製程式碼資料查詢
格式化輸出
-- jsonb_pretty() 函式，列印更可讀的JSON輸出
select jsonb_pretty(w.data) from demo.j_waybill w where w.id = 1;
jsonb_pretty
-----------------------------------
{ +
"line": { +
"to": { +
"city": "綿陽市", +
"district": "市轄區",+
"province": "四川省" +
}, +
"from": { +
"city": "唐山市", +
"district": "豐潤區",+
"province": "河北省" +
} +
}, +
"driver": { +
"name": "張三", +
"mobile": 13800000000 +
}, +
"payment": { +
"oil_amount": 1234, +
"cash_amount": 5678 +
}, +
"project": "測試專案", +
"waybill": 2019000000, +
"pay_org_name": "ABC製造廠" +
}
(1 row)
複製程式碼提取物件成員
PG提供了兩種型別的查詢語法，分別是用於提取頂級成員的 -> ，和提取巢狀成員的#> 語法。如果僅想取出文字內容，使用 ->> 或 #>> 即可。
-- 提取頂級成員， 注意 -> 和 ->> 的區別，後者取出的是文字值
select
w.data->'waybill' as waybill,
w.data->'project' as project,
w.data->>'project' as project_text
from demo.j_waybill w where w.id = 1;
waybill | project | project_text
------------+------------+--------------
2019000000 | "測試專案" | 測試專案
(1 row)
複製程式碼-- 指定節點的路徑來提取巢狀成員，仍然有 #> 和 #>> 的區別
select
w.data#>'{driver}' as driver,
w.data#>>'{driver, name}' as driver_name,
w.data#>'{driver, mobile}' as mobile
from demo.j_waybill w where w.id = 1;
driver | driver_name | mobile
-----------------------------------------+-------------+-------------
{"name": "張三", "mobile": 13800000000} | 張三 | 13800000000
(1 row)
複製程式碼條件篩選
PG提供了特殊的存在判斷符號 ？。這種語法和 is not null 是等價的。
-- 判斷是否存在指定的頂級key
select count(1) from demo.j_waybill w where w.data ? 'waybill';
count
-------
1
(1 row)
-- 上一句的等價語句如下
select count(1) from demo.j_waybill w where w.data->'waybill' is not null ;
-- 判斷巢狀中的key是否存在
select count(1) from demo.j_waybill w where w.data->'driver' ? 'mobile';
count
-------
1
(1 row)
複製程式碼?| 和 ?& 對 ? 的功能進行擴充套件，等價於 or 和 and 操作。
-- 多個條件的判斷 ?| 表示or， ?& 表示and
select count(1) from demo.j_waybill w where w.data->'driver' ?| '{"mobile", "addr"}';
複製程式碼除了檢查key的存在之外，還可以用 @> 符號檢查key:value。
-- ? 僅用來檢查 key 存在，那麼 @> 可以檢查子串的功能
select count(1) from demo.j_waybill w where w.data @> '{"waybill":2019000000, "project":"測試專案"}';
count
-------
1
(1 row)
-- 上一句的等價語句如下
-- PS：數字引數要用to_jsonb()，字串要用 ->> 提取
select count(1) from demo.j_waybill w
where w.data->'waybill' = to_jsonb(2019000000)
and w.data->>'project' = '測試專案' ;
-- 也可以使用型別轉換
select count(1) from demo.j_waybill w
where (w.data->'waybill')::numeric = 2019000000
and w.data->>'project' = '測試專案' ;
複製程式碼資料更新
新增/合併
-- 合併操作符 || 用來增加新的節點，演示如下
select
jsonb_pretty(w.data#>'{line}' || '{"new_line":"增加的"}') as new_line,
jsonb_pretty(w.data || '{"new_key":"增加的"}') as new_key
from demo.j_waybill w where w.id = 1;
new_line | new_key
-------------------------------+-----------------------------------
{ +| { +
"to": { +| "line": { +
"city": "綿陽市", +| "to": { +
"district": "市轄區",+| "city": "綿陽市", +
"province": "四川省" +| "district": "市轄區",+
}, +| "province": "四川省" +
"from": { +| }, +
"city": "唐山市", +| "from": { +
"district": "豐潤區",+| "city": "唐山市", +
"province": "河北省" +| "district": "豐潤區",+
}, +| "province": "河北省" +
"new_line": "增加的" +| } +
} | }, +
| "driver": { +
| "name": "張三", +
| "mobile": 13800000000 +
| }, +
| "new_key": "增加的", +
| "payment": { +
| "oil_amount": 1234, +
| "cash_amount": 5678 +
| }, +
| "project": "測試專案", +
| "waybill": 2019000000, +
| "pay_org_name": "ABC製造廠" +
| }
(1 row)
複製程式碼-- 操作符可以用在update語法中
update demo.j_waybill
set data = data || '{"new_key":"增加的"}' ;
複製程式碼刪除
-- 刪除整個頂級成員
update demo.j_waybill
set data = data-'driver' ;
-- 刪除指定路徑下的成員
update demo.j_waybill
set data = data#-'{driver, mobile}' ;
-- 同時刪除多個成員
update demo.j_waybill
set data = data#-'{driver, mobile}'#-'{line, to}' ;
複製程式碼修改
jsonb_set（） 就是設計用來更新單一路徑節點值。引數含義如下：
- 第一個就是你要修改的 JSONB 資料型別欄位；
- 第二個是一個文字陣列，用來指定修改的路徑；
- 第三個引數是要替換值（可以是 JSON）；
- 如果給的路徑不存在，json_set() 預設會建立他；如果想要禁用這個行為，那就把第四個引數設定成 false；
-- 字串，要使用雙引號
update demo.j_waybill set data = jsonb_set(data, '{"project"}', '"變更的"' );
-- 數字，要使用to_jsonb()
update demo.j_waybill set data = jsonb_set(data, '{"waybill"}', to_jsonb(100) );
-- 新增簡單元素
update demo.j_waybill set data = jsonb_set(data, '{"new_simple"}', to_jsonb(999) );
-- 增加複雜元素
update demo.j_waybill set data = jsonb_set(data, '{"new_complex"}', '{"foo":"bar", "foo1": 123}');
複製程式碼索引
PG自帶的gin型別索引，可以支援除了範圍查詢之外的所有JSON操作。我們用一些例子來進行說明。
-- 建立樣例表
drop table if exists demo.j_cargo;
create table demo.j_cargo (id int primary key, data jsonb);
insert into demo.j_cargo(id, data)
select v.waybill_id, to_jsonb(v)
from (
select b.waybill_create_time, c.*
from dwd_lhb.wb_cargo_info as c, dwd_lhb.wb_base_info as b
where c.waybill_id = b.waybill_id
limit 100000
) as v
;
複製程式碼預設模式
gin有兩種使用模式，預設不帶任何引數。建立index如下
-- 支援除範圍查詢以外的所有查詢
drop index if exists idx_jc_non_ops ;
create index idx_jc_non_ops on demo.j_cargo using gin (data);
複製程式碼判斷指定KEY是否存在的 ？操作，如下
-- 檢視執行計劃確認用到索引
explain select * from demo.j_cargo j where j.data ? 'cargo_name';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=16.77..389.25 rows=100 width=803)
Recheck Cond: (data ? 'cargo_name'::text)
-> Bitmap Index Scan on idx_jc_non_ops (cost=0.00..16.75 rows=100 width=0)
Index Cond: (data ? 'cargo_name'::text)
(4 rows)
複製程式碼判斷指定Key:Value是否相等的 @> 操作，如下
-- 判斷值相等，用到索引
explain select * from demo.j_cargo j where j.data @> '{"cargo_name":"尿素"}' ;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=28.77..401.25 rows=100 width=803)
Recheck Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
-> Bitmap Index Scan on idx_jc_non_ops (cost=0.00..28.75 rows=100 width=0)
Index Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
(4 rows)
複製程式碼OR操作的值相等判斷
-- PS：多個值or操作也用到索引
explain select * from demo.j_cargo j where j.data @> '{"cargo_name":"尿素"}' or j.data @> '{"cargo_name":"白酒"}';
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=57.60..775.81 rows=200 width=803)
Recheck Cond: ((data @> '{"cargo_name": "尿素"}'::jsonb) OR (data @> '{"cargo_name": "白酒"}'::jsonb))
-> BitmapOr (cost=57.60..57.60 rows=200 width=0)
-> Bitmap Index Scan on idx_jc_non_ops (cost=0.00..28.75 rows=100 width=0)
Index Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
-> Bitmap Index Scan on idx_jc_non_ops (cost=0.00..28.75 rows=100 width=0)
Index Cond: (data @> '{"cargo_name": "白酒"}'::jsonb)
(7 rows)
複製程式碼jsonb_path_ops 模式
帶有jsonb_path_ops的gin索引，效率比預設高。
-- jsonb_path_ops只支援@>操作符，但是效率高
drop index if exists idx_jc_ops ;
create index idx_jc_ops on demo.j_cargo using gin (data jsonb_path_ops);
複製程式碼檢視執行計劃，確定使用了更高效的索引 idx_jc_ops
explain select * from demo.j_cargo j where j.data @> '{"cargo_name":"尿素"}' ;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=16.77..389.25 rows=100 width=803)
Recheck Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
-> Bitmap Index Scan on idx_jc_ops (cost=0.00..16.75 rows=100 width=0)
Index Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
(4 rows)
複製程式碼btree索引 - 數字
因為gin索引不支援範圍查詢，所以我們把有這種需求的欄位提出來建立btree索引。在建立的時候，必須進行顯式的型別轉換，如下
-- 支援範圍查詢，把範圍查詢的型別提取出來，建立btree表示式索引
drop index if exists idx_jc_btree_num ;
create index idx_jc_btree_num on demo.j_cargo ( ((data->>'price')::numeric) );
複製程式碼使用索引的時候也需要執行型別轉換，如下
explain select * from demo.j_cargo j where (j.data->>'price')::numeric between 10 and 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=13.42..1673.22 rows=500 width=803)
Recheck Cond: ((((data ->> 'price'::text))::numeric >= '10'::numeric) AND (((data ->> 'price'::text))::numeric <= '100'::numeric))
-> Bitmap Index Scan on idx_jc_btree_num (cost=0.00..13.29 rows=500 width=0)
Index Cond: ((((data ->> 'price'::text))::numeric >= '10'::numeric) AND (((data ->> 'price'::text))::numeric <= '100'::numeric))
(4 rows)
複製程式碼btree索引 - 時間戳
重要：如果直接建立timestamp型別的btree索引，會因為預設的字串轉時間戳函式不滿足IMMUTABLE特性而報錯，錯誤如下
-- Timestamp 錯誤！！！ 因為預設的字串轉時間戳函式不滿足immutable
create index idx_jc_btree_ts on demo.j_cargo ( ((data->>'waybill_create_time')::timestamp) );
ERROR: functions in index expression must be marked IMMUTABLE
複製程式碼正確的做法是，建立一個IMMUTABLE函式進行型別轉換，如下
-- 自定義immutable函式處理時間戳
drop function if exists demo.to_timestamp ;
create or replace function demo.to_timestamp(text) returns timestamp as $$
select $1::timestamp;
$$ language sql strict immutable;
--
drop index if exists idx_jc_btree_ts ;
create index idx_jc_btree_ts on demo.j_cargo ( demo.to_timestamp(data->>'waybill_create_time') );
複製程式碼在SQL中也需要使用自定義函式才能用到索引，演示如下
-- 自定義函式用到索引
explain select * from demo.j_cargo j where demo.to_timestamp(j.data->>'waybill_create_time') between '2015-06-27' and '2015-06-28';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------
Bitmap Heap Scan on j_cargo j (cost=13.42..1918.22 rows=500 width=803)
Recheck Cond: ((demo.to_timestamp((data ->> 'waybill_create_time'::text)) >= '2015-06-27 00:00:00'::timestamp without time zone) AND (demo.to_timestamp((data ->> 'waybill_create_time'::text)) <= '201
5-06-28 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on idx_jc_btree_ts (cost=0.00..13.29 rows=500 width=0)
Index Cond: ((demo.to_timestamp((data ->> 'waybill_create_time'::text)) >= '2015-06-27 00:00:00'::timestamp without time zone) AND (demo.to_timestamp((data ->> 'waybill_create_time'::text)) <=
'2015-06-28 00:00:00'::timestamp without time zone))
(4 rows)
複製程式碼-- 不用自定義函式的時候，使用的是filter操作
explain select * from demo.j_cargo j where (j.data->>'waybill_create_time')::timestamp between '2015-06-27' and '2015-06-28';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
Gather (cost=1000.00..13167.00 rows=500 width=803)
Workers Planned: 2
-> Parallel Seq Scan on j_cargo j (cost=0.00..12117.00 rows=208 width=803)
Filter: ((((data ->> 'waybill_create_time'::text))::timestamp without time zone >= '2015-06-27 00:00:00'::timestamp without time zone) AND (((data ->> 'waybill_create_time'::text))::timestamp w
ithout time zone <= '2015-06-28 00:00:00'::timestamp without time zone))
(4 rows)
複製程式碼