postgres之jsonb屬性的使用操作

jsonb的一些簡單操作(增刪改查)

1、更新操作(attributes屬性為jsonb類型)

方法定義:

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

參數:

target:目標(jsonb類型的屬性)

path :路徑,如果jsonb是數組‘{0,a}’表示在下標是0的位置更新a屬性,如果不是數組,是對象,則寫‘{a}’即可

new_value:新值

選填參數:create_missing:jsonb字段不存在f1屬性時創建,默認為true

返回:更新後的jsonb

官方文檔給出的示例(jsonb數組):

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)
結果:[{"f1":[2,3,4],"f2":null},2,null,3]
jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')
結果:[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

更新jsonb屬性:

-- attributes為jsonb類型字段(對象轉成的json)
原值:{"a":"1"}
update user_test set attributes = jsonb_set(attributes,'{a}','"0"'::jsonb, false) where id = '8888';
執行後:{"a":"0"}

為jsonb插入屬性:

-- 執行後attributes字段中添加瞭platform:baidu
update user_test set attributes =  attributes::jsonb || '{"platform":"baidu"}'::jsonb;
或者:
update user_test set attributes = jsonb_set(attributes, '{platform}','"baidu"');

查詢

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);--錯誤
select * from to_jsonb('"a":1,"b":2') 
 select '{"a":1,"b":2}'::json->>'b' --獲取jsonb中對應鍵的值(文本)
 --select * from json_each( to_jsonb(select distinct attributes from ci_type ) )
 --select to_jsonb(select distinct attributes from ci_type ) 
 
--擴展字段提取相應屬性的值
  select  attributes :: json->>'instanceType' from ci_type 
-- 屬性值轉為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'
-- 根據路徑獲取json對象:#>
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> '{b,ba}'
結果:"b1"
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> '{b}'
結果:{"ba":"b1","bb":"b2"}
-- 根據路徑獲取json對象為text:#>>
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #>> '{b,ba}'
結果:"b1"

補充一下吧

1、to_jsonb()方法接受一個參數,將參數轉換為jsonb

jsonb存儲毫秒值字段
# 更新user表中attributes字段中的create_time字段為當前時間
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 表示取的時間對象,source 表示取的日期來源,類型為 timestamp、time 或 interval。

EXAMPLE:select extract(year from now());

extract(epoch from now())查看現在距1970-01-01 00:00:00 UTC 的秒數

epoch:新紀元時間 Epoch 是以 1970-01-01 00:00:00 UTC 為標準的時間,將目標時間與 1970-01-01 00:00:00時間的差值以秒來計算 ,單位是秒,可以是負值;

postgresql操作jsonb數組

先看表結構:

create table person 
(id int, --  唯一標識
label jsonb); -- 人的標簽數組(指明某人是哪個公司的),標簽時一個一個的對象

label字段數據實例

[{"id":1,"code":"p123","name":"ali"},{"id":2,"code":"p123","name":"ali"}]

要求:寫sql實現添加一個標簽,刪除一個標簽,清空標簽;

1、添加一個標簽

直接使用 || 符號將兩個jsonb連接成一個jsonb

-- 當label為null時
update person set label =  '{"id":1,"code":"p123","name":"ali"}'::jsonb;
-- label不為null時運行
update person set label =  '{"id":1,"code":"p123","name":"ali"}'::jsonb || label

註意:當label為null時這樣執行最後得到的也是null

2、清空標簽

這個比較簡單,我直接設置為null

update person set label = null;

3、刪除一個標簽

這個就比較麻煩一點,我用到瞭

-> ->> jsonb_array_elements() jsonb_build_array() array()

不熟悉這些符號和函數的用法的看:http://www.postgres.cn/docs/10/datatype-json.html

update person 
set label = jsonb_build_array(
    array( -- 不使用該函數,當篩選出有多於2跳數據時會報錯,因為jsonb_build_array函數隻能有一個json
        (select * from 
         (select jsonb_array_elements(label)j from person where id = 1)as a 
         where (j->>'id')::int <> 1) -- 篩選出要刪除的對象
    )
)->0 -- 如果不加這個你會得到兩個[[]]的數組
where id = 1;

以上就是我解決pg中操作jsonb數組的方法,希望能給大傢一個參考,也希望大傢多多支持WalkonNet.

推薦閱讀: