COSCUP2020
入門
TR213
Loading embed note
請從這裡開始
正規化的目的是讓資料保持一致,不要讓資料重複
反正規化
jsonb_pretty
select ["a", "b", "c", "d"]::json->2
可判斷 key, value 是否存在 json array 當中,也支援 operator (|, &
)
可增刪 new key into current json
jsonb 可增加 index
-- 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
Learn More →
or
By clicking below, you agree to our terms of service.
New to HackMD? Sign up