JSON in PostgreSQL，建議跟不建議 - Rubin

tags: COSCUP2020 入門 TR213

歡迎來到 https://hackmd.io/@coscup/2020 共筆
點擊本頁上方的 開始用 Markdown 一起寫筆記！
手機版請點選上方 按鈕展開議程列表。

請從這裡開始

正規化

• 正規化的目的是讓資料保持一致，不要讓資料重複

  • 範例1:
    • 同一個會員資料表同時有電話1、電話2、電話3…，重複屬性的欄位在同一張資料表上。導致開發時修改、維護資料的困難。
    • 並非每一個會員都有多支手機，或是固定N支手機。預先宣告N個手機欄位導致只有一支手機的用戶其餘手機都是null、或是程式維護不好導致手機1~手機8都是null，真正的手機出現在手機9…
    • 也不能假設使用者最多就是N支手機，未來遇到N+1手機的用戶，這個系統就等於限制了使用者的資料(可以把手機換成其他項目，像是購物車內的商品項目！)，未來擴充也成為隱憂
    • 總結，一個資料表上有重複的項目會導致(1)資料數量被限制 (2)冗余不必要的欄位出現導致資料庫效能拖慢 (3)程式端難以維護，需要知道手機的索引號才能寫出對應的SQL語句
  • 範例2:
    • 商品名稱、商品描述等資訊，同時出現在商品資料表與訂單記錄資料表中。導致資料的一致性維護困難。
    • 當產品A要修改名稱時，需要把訂單記錄資料表中的產品A也一併修改。會導致 (1)資料量大時會導致修改困難 (2)Lock資料表影響線上交易 (3)修改不完整導致資料錯誤(少修改到、where條件沒寫好)
    • 因此一般會建議在交易紀錄資料表中記錄商品代號(pid)，透過join的方式跟商品資料表連動、取得商品資料，這樣要修改商品描述、商品名稱時，只需要修改商品資料表的內容即可，省下了大量IO、確保了資料的完整、一致性(如果搭配外鍵會更舒服)，降低開發者的負擔。

• 反正規化

  • query 時減少 join 的動作，把資料通通塞在同一列
  • 報表性質的資料表會需要，有一些巨量的表格跟表格JOIN會爆炸

JSON support

• PG12之後可支援 JSON 格式
• json: 純文字(含空白、換行符號等)
• jsonb: 二進位轉換(無空白、換行，精簡)
• 函數/運算子
• 若 json 格式不標準，無法 insert into pgdb(詳見講者範例)
• json vs jsonb
  • https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql
  • 如果僅在應用程序中使用JSON表示形式，則PostgreSQL僅用於存儲和檢索此表示形式，應使用json。
  • 如果您在PostgreSQL中對JSON值進行了大量操作，或對某些JSON字段使用索引，則應使用jsonb。

一些函數

• jsonb_pretty

  • 可以把純文字的 json 轉為比較漂亮易讀的格式

• select ["a", "b", "c", "d"]::json->2

  • return "c"

• 可判斷 key, value 是否存在 json array 當中，也支援 operator (|, &)

• 可增刪 new key into current json

• jsonb 可增加 index

問題與責任

• 要想清楚 Access pattern ，考慮商業邏輯、是否常 IO

• 關心資料處理流程
• 關心資料粒度、交易流程

議程現場實驗腳本

-- Rubin.Sheu 202/08/02 於COSCUP X PostgreSQL.tw
-- 議程簡報與實驗腳本皆會提供在PostgreSQL.tw Facebook社群

-- 基本json/jsonb的呈現

-- Lab 1 基本json/jsonb比較

-- 建立json基本格式資料表
drop table if exists json_tab;
create table json_tab(
my_data json
);

-- 建立jsonb基本格式資料表
drop table if exists jsonb_tab;
create table jsonb_tab(
my_data jsonb
);

-- 嘗試插入json資料(純數字)
insert into json_tab(my_data)
values ('123');
-- 嘗試插入json資料(陣列，包含數值、文字、布林)
insert into json_tab(my_data)
values ('[100,"lulu",true]');

-- 嘗試插入非標準json資料
-- 非key-value
insert into jsontab(my_data)
values('{"abc"}');
-- 缺乏、不完整的json文字
insert into jsontab(my_data)
values('{"abc":"non-json"');

-- 查詢輸入的結果
select * from json_tab;

-- json 與 jsonb空間的不同
SELECT pg_column_size('{"test":"data"}'::json)
union
SELECT pg_column_size('{"test":"data"}'::jsonb);


-- 清空json格式表格
truncate table json_tab;

-- 針對json/jsonb輸入相同資料
insert into json_tab(my_data)
values ('
{
"oid":"AZ10925786",
"odt":"2020/01/01",
"otime":"17:20:13",
"cid":160844897,
"order_list":
[
{
"pid":"B10294565",
"sub_pid":"A00098",
"pcount":2,
"pprice":400
},
{
"pid":"B10294887",
"pcound":1,
"pprice":1000
}
],
"order_ext":
[
{
"ext_type":"smemo",
"ext_info":"包含易碎物"
},
{
"ext_type":"smemo",
"ext_info":"包含常溫品"
},
{
"ext_type":"umemo",
"ext_info":"請晚上六點後再送貨謝謝"
}
]
}
');

insert into jsonb_tab(my_data)
values ('
{
"oid":"AZ10925786",
"odt":"2020/01/01",
"otime":"17:20:13",
"cid":160844897,
"order_list":
[
{
"pid":"B10294565",
"sub_pid":"A00098",
"pcount":2,
"pprice":400
},
{
"pid":"B10294887",
"pcound":1,
"pprice":1000
}
],
"order_ext":
[
{
"ext_type":"smemo",
"ext_info":"包含易碎物"
},
{
"ext_type":"smemo",
"ext_info":"包含常溫品"
},
{
"ext_type":"umemo",
"ext_info":"請晚上六點後再送貨謝謝"
}
]
}
');

-- 顯示出的結果
select my_data from json_tab;
select my_data from jsonb_tab;




















-- Lab2 對json資料的操作

-- 存取
-- jsonb_pretty讓輸出美化/結構化
select
'
{
"test":"this is json data"
}
'::json;

select jsonb_pretty(
'
{
"test":"this is json data"
}
'
);


-- 用->操作指定資料
select '["a", "b", "c", "d"]'::json->2; -- 從零開始
select '["a", "b", "c", "d"]'::json->87; -- 不存在，回傳null

-- 判斷
-- 有無包含key/value
select '{"a":1, "b":2, "c":3}'::json @> '{"a":1}'::json; -- 用json無法正確存取
select '{"a":1, "b":2, "c":3}'::jsonb @> '{"a":1}'::jsonb; -- 用jsonb即可正確存取
select '{"a":1, "b":2, "c":3}'::jsonb @> '{"a":13}'::jsonb; -- 用jsonb即可正確存取

-- 有無包含key
select '{"a":1, "b":2, "c":3}'::jsonb ? 'a'; -- 包含該key
select '{"a":1, "b":2, "c":3}'::jsonb ? 'z'; -- 不包含該key

-- 有無包含key(or判斷)
select '{"a":1, "b":2, "c":3}'::jsonb ?| array['a','z']; -- 包含該key集合
select '{"a":1, "b":2, "c":3}'::jsonb ?| array['y','z']; -- 包含該key集合

-- 有無包含key(and判斷)
select '{"a":1, "b":2, "c":3}'::jsonb ?& array['a','b']; -- 包含該key集合
select '{"a":1, "b":2, "c":3}'::jsonb ?& array['a','z']; -- 包含該key集合

-- 是否包含該path
select '{"a":1, "b":2, "c":3}'::jsonb @? '$.a'; -- 包含該key集合
select '{"a":1, "b":2, "c":3}'::jsonb @? '$.f'; -- 包含該key集合

-- 操作
-- 串接value
select '[1,2,3,4]'::jsonb || '[5,6]'::jsonb;
-- 移除指定索引項目
select '[1,2,3,4]'::jsonb - 0 ;
-- 移除指定key
select '{"a":1, "b":2, "c":3}'::jsonb - 'a' ;


-- jsonpath操作
/*
{
"oid":"AZ10925786",
"odt":"2020/01/01",
"otime":"17:20:13",
"cid":160844897,
"order_list":
[
{
"pid":"B10294565",
"sub_pid":"A00098",
"pcount":2,
"pprice":400
},
{
"pid":"B10294887",
"pcound":1,
"pprice":1000
}
],
"order_ext":
[
{
"ext_type":"smemo",
"ext_info":"包含易碎物"
},
{
"ext_type":"smemo",
"ext_info":"包含常溫品"
},
{
"ext_type":"umemo",
"ext_info":"請晚上六點後再送貨謝謝"
}
]
}*/

select my_data from jsonb_tab; -- 查詢全部資料

-- 單獨取出oid
select jsonb_path_query(my_data,'$.oid') from jsonb_tab;

-- 單獨取出order_list內的資料
select jsonb_path_query(my_data,'$.order_list') from jsonb_tab;

-- 單獨取出第一筆order_list的pid
select jsonb_path_query(my_data,'$.order_list[0].pid') from jsonb_tab;



-- 實驗結束，銷毀資料表
drop table if exists jsonb_tab;
drop table if exists json_tab;

參考資料

JSON Functions and Operators
JSON Types
PostgreSQL internals: JSONB type and its indexes

---