postgres之jsonb屬性的使用操作
jsonb的一些簡(jiǎn)單操作（增刪改查）
1、更新操作（attributes屬性為jsonb類(lèi)型）
方法定義：
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
參數(shù)：
target：目標(biāo)（jsonb類(lèi)型的屬性）
path ：路徑，如果jsonb是數(shù)組‘{0，a}'表示在下標(biāo)是0的位置更新a屬性，如果不是數(shù)組，是對(duì)象，則寫(xiě)‘{a}'即可
new_value：新值
選填參數(shù)：create_missing：jsonb字段不存在f1屬性時(shí)創(chuàng)建，默認(rèn)為true
返回：更新后的jsonb
官方文檔給出的示例（jsonb數(shù)組）：
jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)
結(jié)果：[{"f1":[2,3,4],"f2":null},2,null,3]
jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')
結(jié)果：[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
更新jsonb屬性：
-- attributes為jsonb類(lèi)型字段（對(duì)象轉(zhuǎn)成的json）
原值：{"a":"1"}
update user_test set attributes = jsonb_set(attributes,'{a}','"0"'::jsonb, false) where id = '8888';
執(zhí)行后：{"a":"0"}
為jsonb插入屬性：
-- 執(zhí)行后attributes字段中添加了platform：baidu
update user_test set attributes = attributes::jsonb || '{"platform":"baidu"}'::jsonb;
或者：
update user_test set attributes = jsonb_set(attributes, '{platform}','"baidu"');
查詢(xún)
select value from json_each('{"a":"foo", "b":"bar"}') where key = 'a'
select * from json_object_keys('{"a":"foo", "b":"bar"}')
select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
select * from json_object_keys(from ci_type.attributes);--錯(cuò)誤
select * from to_jsonb('"a":1,"b":2')
select '{"a":1,"b":2}'::json->>'b' --獲取jsonb中對(duì)應(yīng)鍵的值（文本）
--select * from json_each( to_jsonb(select distinct attributes from ci_type ) )
--select to_jsonb(select distinct attributes from ci_type )
--擴(kuò)展字段提取相應(yīng)屬性的值
select attributes :: json->>'instanceType' from ci_type
-- 屬性值轉(zhuǎn)為jsonb
select to_jsonb('id:'||id::text) from ci
--jsonb添加屬性，刪除屬性
select '{"a":"foo", "b":"bar"}'::jsonb || '{"c":"fc", "d":"bdd"}'::jsonb--添加
select '{"a":"foo", "b":"bar"}'::jsonb -'c'-'d'-'a'||'{"a":2}'--刪除
select '{"a": "b","c":3}'::jsonb - 'a'
-- 根據(jù)路徑獲取json對(duì)象：#>
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> '{b,ba}'
結(jié)果："b1"
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> ''
結(jié)果：{"ba":"b1","bb":"b2"}
-- 根據(jù)路徑獲取json對(duì)象為text：#>>
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #>> '{b,ba}'
結(jié)果："b1"
補(bǔ)充一下吧
1、to_jsonb()方法接受一個(gè)參數(shù)，將參數(shù)轉(zhuǎn)換為jsonb
jsonb存儲(chǔ)毫秒值字段
# 更新user表中attributes字段中的create_time字段為當(dāng)前時(shí)間
update user_test
set attributes = jsonb_set(attributes,'{create_time}',to_jsonb(extract(epoch from now())*1000), true)
2、extract(epoch from now())*1000 獲取毫秒值
EXTRACT(field FROM source)
field 表示取的時(shí)間對(duì)象，source 表示取的日期來(lái)源，類(lèi)型為 timestamp、time 或 interval。
EXAMPLE:select extract(year from now());
extract(epoch from now())查看現(xiàn)在距1970-01-01 00:00:00 UTC 的秒數(shù)
epoch:新紀(jì)元時(shí)間 Epoch 是以 1970-01-01 00:00:00 UTC 為標(biāo)準(zhǔn)的時(shí)間，將目標(biāo)時(shí)間與 1970-01-01 00:00:00時(shí)間的差值以秒來(lái)計(jì)算 ，單位是秒，可以是負(fù)值;
postgresql操作jsonb數(shù)組
先看表結(jié)構(gòu)：
create table person (id int, -- 唯一標(biāo)識(shí) label jsonb); -- 人的標(biāo)簽數(shù)組（指明某人是哪個(gè)公司的），標(biāo)簽時(shí)一個(gè)一個(gè)的對(duì)象
label字段數(shù)據(jù)實(shí)例
[{"id":1,"code":"p123","name":"ali"},{"id":2,"code":"p123","name":"ali"}]
要求：寫(xiě)sql實(shí)現(xiàn)添加一個(gè)標(biāo)簽，刪除一個(gè)標(biāo)簽，清空標(biāo)簽；
1、添加一個(gè)標(biāo)簽
直接使用 || 符號(hào)將兩個(gè)jsonb連接成一個(gè)jsonb
-- 當(dāng)label為null時(shí)
update person set label = '{"id":1,"code":"p123","name":"ali"}'::jsonb;
-- label不為null時(shí)運(yùn)行
update person set label = '{"id":1,"code":"p123","name":"ali"}'::jsonb || label
注意：當(dāng)label為null時(shí)這樣執(zhí)行最后得到的也是null
2、清空標(biāo)簽
這個(gè)比較簡(jiǎn)單，我直接設(shè)置為null
update person set label = null;
3、刪除一個(gè)標(biāo)簽
這個(gè)就比較麻煩一點(diǎn)，我用到了
-> ->> jsonb_array_elements() jsonb_build_array() array()
不熟悉這些符號(hào)和函數(shù)的用法的看：http://www.postgres.cn/docs/10/datatype-json.html
update person
set label = jsonb_build_array(
array( -- 不使用該函數(shù)，當(dāng)篩選出有多于2跳數(shù)據(jù)時(shí)會(huì)報(bào)錯(cuò)，因?yàn)閖sonb_build_array函數(shù)只能有一個(gè)json
(select * from
(select jsonb_array_elements(label)j from person where id = 1)as a
where (j->>'id')::int <> 1) -- 篩選出要?jiǎng)h除的對(duì)象
)
)->0 -- 如果不加這個(gè)你會(huì)得到兩個(gè)[[]]的數(shù)組
where id = 1;
以上就是我解決pg中操作jsonb數(shù)組的方法，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家.
postgresql 實(shí)現(xiàn)取出分組中最大的幾條數(shù)據(jù)
postgreSQL查詢(xún)結(jié)果添加一個(gè)額外的自增序列操作
postgresql 實(shí)現(xiàn)字符串分割字段轉(zhuǎn)列表查詢(xún)
Postgresql刪除數(shù)據(jù)庫(kù)表中重復(fù)數(shù)據(jù)的幾種方法詳解
PostgreSQL教程(十三)：數(shù)據(jù)庫(kù)管理詳解
使用pg_basebackup對(duì)Postgre進(jìn)行備份與恢復(fù)的實(shí)現(xiàn)
淺談postgresql數(shù)據(jù)庫(kù)varchar、char、text的比較