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数组的方法,希望能给大家一个参考,也希望大家多多支持悠久资源.