postgres之jsonb属性的使用操作_PostgreSQL

来源:脚本之家  责任编辑:小易  

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数组的方法,希望能给大家一个参考,也希望大家多多支持真格学网.

您可能感兴趣的文章:PostgreSQL 更新JSON,JSONB字段的操作postgresql 实现修改jsonb字段中的某一个值postgresql的jsonb数据查询和修改的方法如何获取PostgreSQL数据库中的JSON值

  • 本文相关:
  • postgresql 默认用户名与密码的操作
  • debian中postgresql数据库安装配置实例
  • 查看postgresql系统信息的常用命令操作
  • postgresql中的ltree类型使用方法
  • postgresql 实现得到时间对应周的周一案例
  • postgresql分区表(partitioning)应用实例详解
  • postgresql 实现给查询列表增加序号操作
  • postgresql归档配置及自动清理归档日志的操作
  • postgresql 中的 like 查询优化方案
  • postgresql 赋予用户权限和撤销权限的实例
  • 如何将Postgres备份文件.backup导入到新的数据库中
  • postgres 表空间位置怎么修改
  • 如何回收VCSA 6自带的vPostgres数据库空间
  • POSTGRES.EXE无限增多,现在有解决方案吗
  • postgres怎样合并重复lie
  • postgres:获取当前年份,这个SQL语句没有看懂,求大神指点
  • 用C#怎么连接postgres 数据库 程序要怎么写
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页postgresqlpostgresql 更新json,jsonb字段的操作postgresql 实现修改jsonb字段中的某一个值postgresql的jsonb数据查询和修改的方法如何获取postgresql数据库中的json值postgresql 默认用户名与密码的操作debian中postgresql数据库安装配置实例查看postgresql系统信息的常用命令操作postgresql中的ltree类型使用方法postgresql 实现得到时间对应周的周一案例postgresql分区表(partitioning)应用实例详解postgresql 实现给查询列表增加序号操作postgresql归档配置及自动清理归档日志的操作postgresql 中的 like 查询优化方案postgresql 赋予用户权限和撤销权限的实例postgresql 角色与用户管理介绍windows下postgresql数据库的下载windows下postgresql安装图解postgresql中的oid和xid 说明15个postgresql数据库实用命令分postgresql alter语句常用操作小windows postgresql 安装图文教程postgresql 安装和简单使用postgresql 创建表分区postgresql新手入门教程postgresql 自增语法的用法说明postgresql使用ip无法连接的解决方法postgresql通过查询进行更新的操作postgresql 查询字符串中是否包含某字符的postgresql限制用户登录错误次数的实例代postgresql中调用存储过程并返回数据集实postgresql存储过程循环调用方式postgresql教程(二十):pl/pgsql过程语言postgresql表死锁问题的排查方式postgresql 如何查看pg_wal目录下xlog文件
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved