postgres之jsonb属性的使用操作

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

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]

-- attributes为jsonb类型字段（对象转成的json）

原值：{ "a" : "1" }

update user_test set attributes = jsonb_set(attributes, '{a}' , '"0"' ::jsonb, false ) where id = '8888' ;

执行后：{ "a" : "0" }

-- 执行后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"

jsonb存储毫秒值字段

# 更新 user 表中attributes字段中的create_time字段为当前时间

update user_test

set attributes = jsonb_set(attributes, '{create_time}' ,to_jsonb(extract(epoch from now())*1000), true )

EXTRACT(field FROM source)

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

create table person

(id int , --  唯一标识

label jsonb); -- 人的标签数组（指明某人是哪个公司的），标签时一个一个的对象

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

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

update person set label = null ;

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

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;