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

-- 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;