postgresql 实现修改jsonb字段中的某一个值

我就废话不多说了,大家还是直接看代码吧~

UPDATE tablename SET tags = jsonb_set(tags-'landuse_area', '{landuse_area}',('"' || round((ST_Area(ST_Transform(geom,4527)) * 0.0015) :: NUMERIC,3) || '"')::jsonb, TRUE) WHERE tags @> '{"name":"张三"}';

round的目的是保留3位小数

st_area是计算多边形的面积,后面的0.0015是面积单位转换为亩

st_transform是投影转换,这里我表中原始的geom是4326,不适合计算面积,所以在这里转换为4527计算面积

::NUMERIC是转换为numeric数据类型

::jsonb是转换为jsonb类型

补充:postgresql----JSON和JSONB类型的增删改查

postgresql支持两种json数据类型:json和jsonb,而两者唯一的区别在于效率,json是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。而jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别:json类型存储快,使用慢,jsonb类型存储稍慢,使用较快。

注意:键值对的键必须使用双引号

从PostgreSQL 9.3开始,json就成了postgres里的一种数据类型,也就是和varchar、int一样,我们表里的一个字段的类型可以为json了。

与此同时,postgres还提供了jsonb格式,jsonb格式是json的二进制形式,二者的区别在于json写入快,读取慢,jsonb写入慢,读取快,但在操作上,二者是没有区别的。下面以jsonb为例。

创建表

假设我们要存储的json数据是这样的:

{
"id": ID
"name":"名字",
"age":年龄
}

建表语句如下:

create table if not exists name_age (
info jsonb
)

好了,这样就创建了一张表,里面只有一个 info 字段,下面开始进行CRUD操作。

插入数据

插入数据可以直接以json格式插入:

insert into name_age values('{"id":1,"name":"小明", "age":18}')

在json里插入新的key值gender,如下:

SELECT info||'{"gender":"男"}'::jsonb from name_age where (info->>'id')::int4 = 1

查询数据

Postgres里的查询需要用到查询符。比如说,我们要查询id为1的数据,语句如下:

select info from name_age where info @> '{"id":1}'::jsonb

用到了 @> 这个查询符,表明info当前这条记录里的顶层json中有没有id为1的key-value对;有的话则满足条件。

再来一个复杂一点的查询的,查询 age>16 的记录,并且只显示 name ,语句如下:

select info->'name' from name_age where (info->>'age')::int4 > 16

关于详细运算符使用,请参考官方文档: 9.15. JSON Functions and Operators

修改数据

下面,将 age 从 18 改为 22 ,SQL语句:

SELECT info ||'{"age":22}'::jsonb from name_age where (info->>'id')::int4 = 1

上述用法仅适用于9.5以上,9.5以下需要整个记录更新,不可以单独修改某个值。

除了操作符以外,还可以使用函数操作: jsonb_set() ,函数签名如下:

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

详细使用可参考 9.15. JSON Functions and Operators

删除数据

删除age这个key,SQL如下:

SELECT info-'age' from name_age where (info->>'id')::int4 = 1

直接用操作符 - 即可。

总结

PostgreSQL 9.5以上的版本中有了很多方便的操作符,使得操作json变得非常方便了。

json和jsonb的操作符

操作符 右操作数类型 描述 示例 结果
-> int 获取JSON数组元素(索引从0开始) select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2; {"c":"baz"}
-> text 通过键获取值 select '{"a": {"b":"foo"}}'::json->'a'; {"b":"foo"}
->> int
获取JSON数组元素为 text

select '[1,2,3]'::json->>2; 3
->> text 通过键获取值为text select '{"a":1,"b":2}'::json->>'b'; 2
#> text[]
在指定的路径获取JSON对象

select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'; {"c": "foo"}
#>> text[]
在指定的路径获取JSON对象为 text

select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'; 3

jsonb额外操作符

操作符 右操作数类型 描述 示例 结果
@> jsonb 左侧json最上层的值是否包含右边json对象
select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb;

select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;


f

t

<@ jsonb 左侧json对象是否包含于右侧json最上层的值内 select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb; t
? text text是否作为左侧Json对象最上层的键 select '{"a":1, "b":2}'::jsonb ? 'b'; t
?| text[] text[]中的任一元素是否作为左侧Json对象最上层的键 select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']; t
?& text[] text[]中的所有元素是否作为左侧Json对象最上层的键 select '["a", "b"]'::jsonb ?& array['a', 'b']; t
|| jsonb 连接两个json对象,组成一个新的json对象 select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; ["a", "b", "c", "d"]
- text 删除左侧json对象中键为text的键值对 select '{"a": "b"}'::jsonb - 'a'; {}
- integer
删除数组指定索引处的元素,如果索引值为负数,则从右边计算索引值。

如果最上层容器内不是数组,则抛出错误。

select '["a", "b"]'::jsonb - 1; ["a"]
#- text[]
删除指定路径下的域或元素(如果是json数组,且整数值是负的,

则索引值从右边算起)

select '["a", {"b":1}]'::jsonb #- '{1,b}'; ["a", {}]

json创建函数

函数 描述 示例 结果

to_json(anyelement)

to_jsonb(anyelement)


返回json或jsonb类型的值。数组和复合被转换(递归)成数组和对象。另外除数字、

布尔、NULL值(直接使用NULL抛出错误)外,其他标量必须有类型转换。(此处请参考原文)

select to_json('3'::int); 3

array_to_json(anyarray

[, pretty_bool])


以JSON数组返回该数组。PostgreSQL多维数组变成JSON数组中的数组。
如果pretty_bool 为真,则在维度1元素之间添加换行。

select array_to_json('{{1,5},{99,100}}'::int[],true);
[[1,5], +
[99,100]]

row_to_json(record [, pretty_bool]) 以JSON对象返回行。如果pretty_bool 为真,则在级别1元素之间添加换行。 select row_to_json(row(1,'foo'),true);
{"f1":1, +
"f2":"foo"}


json_build_array(VARIADIC "any")

jsonb_build_array(VARIADIC "any")

建立一个由可变参数列表组成的不同类型的JSON数组 select json_build_array(1,2,'3',4,5); [1, 2, "3", 4, 5]

json_build_object(VARIADIC "any")

jsonb_build_object(VARIADIC "any")

建立一个由可变参数列表组成的JSON对象。参数列表参数交替转换为键和值。 select json_build_object('foo',1,'bar',2); {"foo" : 1, "bar" : 2}

json_object(text[])

jsonb_object(text[])


根据text[]数组建立一个json对象,如果是一维数组,则必须有偶数个

元素,元素交替组成键和值。如果是二维数组,则每个元素必须有2个元素,可以组成键值对。


select json_object('{a, 1, b, "def", c, 3.5}');

select json_object('{{a, 1},{b, "def"},{c, 3.5}}');

{"a" : "1", "b" : "def", "c" : "3.5"}

json_object(keys text[], values text[])

jsonb_object(keys text[], values text[])

分别从两组text[]中获取键和值,与一维数组类似。 select json_object('{a, b}', '{1,2}'); {"a" : "1", "b" : "2"}

json处理函数

函数 返回类型 描述 示例 结果

json_array_length(json)

jsonb_array_length(jsonb)

int 返回Json数组最外层元素个数 select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); 5

json_each(json)

jsonb_each(jsonb)


setof key text, value json

setof key text, value jsonb

将最外层Json对象转换为键值对集合 select json_each('{"a":"foo", "b":"bar"}');
(a,"""foo""")
(b,"""bar""")


json_each_text(json)

jsonb_each_text(jsonb)

setof key text, value text 将最外层Json对象转换为键值对集合,且value为text类型 select json_each_text('{"a":"foo", "b":"bar"}');
(a,foo)
(b,bar)


json_extract_path(from_json json,

VARIADIC path_elems text[])

jsonb_extract_path(from_json jsonb,

VARIADIC path_elems text[])


json

jsonb

返回path_elems指向的value,同操作符#> select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4'); {"f5":99,"f6":"foo"}

json_extract_path_text(from_json json,

VARIADIC path_elems text[])

jsonb_extract_path_text(from_json jsonb,

VARIADIC path_elems text[])

text 返回path_elems指向的value,并转为text类型,同操作符#>> select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6'); foo

json_object_keys(json)

jsonb_object_keys(jsonb)

setof text 返回json对象最外层的key select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
f1
f2


json_populate_record(base anyelement,

from_json json)

jsonb_populate_record(base anyelement,

from_json jsonb)

anyelement 将json对象的value以base定义的行类型返回,如果行类型字段比json对象键值少,则多出的键值将被抛弃;如果行类型字段多,则多出的字段自动填充NULL。
表tbl_test定义:

Table "public.tbl_test"
Column | Type | Modifiers
--------+-----------------------+-----------
a | bigint |
b | character varying(32) |

c | character varying(32) |

select * from json_populate_record(null::tbl_test, '{"a":1,"b":2}');


a | b | c
---+---+------
1 | 2 | NULL


json_populate_recordset(base anyelement,

from_json json)

jsonb_populate_recordset(base anyelement,

from_json jsonb)

setof anyelement 将json对象最外层数组以base定义的行类型返回
表定义同上

select * from json_populate_recordset(null::tbl_test, '[{"a":1,"b":2},{"a":3,"b":4}]');


a | b | c
---+---+------
1 | 2 | NULL
3 | 4 | NULL


json_array_elements(json)

jsonb_array_elements(jsonb)


setof json

setof jsonb

将json数组转换成json对象value的集合 select json_array_elements('[1,true, [2,false]]');
1
true
[2,false]


json_array_elements_text(json)

jsonb_array_elements_text(jsonb)

setof text 将json数组转换成text的value集合 select json_array_elements_text('["foo", "bar"]');
foo
bar


json_typeof(json)

jsonb_typeof(jsonb)

text
返回json最外层value的数据类型,可能的类型有

object, array, string, number, boolean, 和null.

select json_typeof('-123.4') number

json_to_record(json)

jsonb_to_record(jsonb)

record 根据json对象创建一个record类型记录,所有的函数都返回record类型,所以必须使用as明确定义record的结构。 select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text);
a | b | d
---+---------+------
1 | [1,2,3] | NULL


json_to_recordset(json)

jsonb_to_recordset(jsonb)

setof record 根据json数组创建一个record类型记录,所有的函数都返回record类型,所以必须使用as明确定义record的结构。 select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);
a | b
---+------
1 | foo
2 | NULL


json_strip_nulls(from_json json)

jsonb_strip_nulls(from_json jsonb)


json

jsonb

返回json对象中所有非null的数据,其他的null保留。
select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]');

[{"f1":1},2,null,3]

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

jsonb 如果create_missing为true,则将在target的path处追加新的jsonb;如果为false,则替换path处的value。
select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);

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


[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]

[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]


jsonb_insert(target jsonb, path text[],

new_value jsonb, [insert_after boolean])

jsonb 如果insert_after是true,则在target的path后面插入新的value,否则在path之前插入。
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');

select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true);


{"a": [0, "new_value", 1, 2]}

{"a": [0, 1, "new_value", 2]}

jsonb_pretty(from_json jsonb) text 以缩进的格式更容易阅读的方式返回json对象 select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');
[
{
"f1": 1,
"f2": null
},
2,
null,
3
]

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。

(0)

相关推荐

  • postgresql 中的 like 查询优化方案

    当时数量量比较庞大的时候,做模糊查询效率很慢,为了优化查询效率,尝试如下方法做效率对比 一.对比情况说明: 1.数据量100w条数据 2.执行sql 二.对比结果 explain analyze SELECT c_patent, c_applyissno, d_applyissdate, d_applydate, c_patenttype_dimn, c_newlawstatus, c_abstract FROM public.t_knowl_patent_zlxx_temp WHERE c_a

  • 解决PostgreSQL Array使用中的一些小问题

    在PostgreSQL 中可以使用Array数据结构,例如 select array[1,2,3]; return {1,2,3} 但是,如果 select array[1,2,3][1]; --会报错 select (select array[1,2,3])[1] --可以使用 那么在用正则匹配函数 regexp_match 就会遇到这样的问题,如 select regexp_match('123-123', '(\d+)-(\d+)'); --return {123, 123} select

  • PostgreSQL COALESCE使用方法代码解析

    有这种要求,更新自己本身的字段的某个值进行加或者减 常规方法: UPDATE tbl_kintai_print_his SET print_time = now(), print_emp_cd = '000000', times = (select times from tbl_kintai_print_his where kokyaku_cd = '000002' AND sagyo_ymd = '2015-01-30' ) + 1, pattern = '055' , ko_item_1 =

  • postgresql 实现将字段为空的值替换为指定值

    1.null 表示缺失的值, '' " "表示空值 null 参与的表达式运算都返回null 使用is null 判断是null is not null 判断非null 2.nullif(value1, value2) 如果value1 == value2 返回null 3.coalesce(arg1, arg2, ...) 返回第一个 不为null的值 所以可以使用如下语句,实现将table中filed为空的记录替换为指定值 update table set filed = COAL

  • postgresql 中的COALESCE()函数使用小技巧

    场景: 存在一个用户白名单表,提供了此用户的用户名和地区信息,判断此用户是否在此白名单表中. 如: 姓名 白名单地区 张三 中国,美国 则可使用如下语句: SELECT ID, user, area_list FROM t_white_user WHERE user = #{ user, jdbcType = VARCHAR } AND ( COALESCE (area_list, '') LIKE CONCAT ( '%' ,#{ country, jdbcType = VARCHAR },

  • PostgreSQL 禁用全表扫描的实现

    PostgreSQL可以通过一些设置来禁用全表扫描(FULL SCAN/Seq Scan) 注意: 设置此功能后不是完全避免全表扫描,而是只要有不通过全表扫描能得出结果的就不走全表扫描. 如果什么路都不通,那肯定得全表扫描,不然怎么获取数据. 而且并不是不走全表扫描性能就一定好. 下面展示下这个功能: 查询表结构: highgo=# \d test Table test Column | Type | Modifiers -------------+-----------------------

  • postgresql 实现修改jsonb字段中的某一个值

    我就废话不多说了,大家还是直接看代码吧~ UPDATE tablename SET tags = jsonb_set(tags-'landuse_area', '{landuse_area}',('"' || round((ST_Area(ST_Transform(geom,4527)) * 0.0015) :: NUMERIC,3) || '"')::jsonb, TRUE) WHERE tags @> '{"name":"张三"}';

  • 使用python批量修改XML文件中图像的depth值

    最近刚刚接触深度学习,并尝试学习制作数据集,制作过程中发现了一个问题,现在跟大家分享一下.问题是这样的,在制作voc数据集时,我采集的是灰度图像,并已经用labelimg生成了每张图像对应的XML文件.训练时发现好多目标检测模型使用的训练集是彩色图像,因此特征提取网络的输入是m×m×3的维度的图像.所以我就想着把我采集的灰度图像的深度也改成3吧.批量修改了图像的深度后,发现XML中的depth也要由1改成3才行.如果重新对图像标注一遍生成XML文件的话太麻烦,所以就想用python批量处理一下.

  • java8实现list集合中按照某一个值相加求和,平均值等操作代码

    集合: List<User> user = new User(); user .stream().collect(Collectors.summingInt(User::getAge)) 参数类型: summarizingDouble 统计数据(double)状态, 其中包括count min max sum和平均值 summarizingInt 统计数据(int)状态, 其中包括count min max sum和平均值 summarizingLong 统计数据(long)状态, 其中包括c

  • JavaScript中如何判断一个值的类型

    我们知道在js中有一个运算符可以帮助我们判断一个值的类型,它就是typeof运算符. console.log(typeof 123); //number console.log(typeof '123'); //string console.log(typeof true); //boolean console.log(typeof undefined); //undefined console.log(typeof null); //object console.log(typeof []);

  • PostgreSQL 更新JSON,JSONB字段的操作

    直接使用 update 表名 set 列名 = (jsonb_set(列名::jsonb,'{key}','"value"'::jsonb)) where 条件 要注意里面的单引号和双引号. 补充:向PostgreSQL中json中加入某个字段 或者更新某个字段的SQL语句 需求:通过SQL的方式,对JSON里面的某个字段统一处理,更新成一个新值 1.向PostgreSQL中json中加入某个字段.例如:向users表中id为3的data列中加入 {"uptate_data&

  • MybatisPlus修改时空字段无法修改的解决方案

    目录 MybatisPlus空字段无法修改 点开修改 这个时候,在实体类中加入 这行注释的意思是 Mybatis-Plus修改指定字段 核心代码 MybatisPlus空字段无法修改 点开修改 数据为空时,点击确定,可列表出现的数据还是为原来的数据 查看后台的打印输出,发现没有更新的这2个字段 这个时候,在实体类中加入 这行注释的意思是 在属性执行修改时,将自动填充一个值(默认为null),即将字段修改为空,而不是不做修改. Mybatis-Plus修改指定字段 核心代码 /** * 修改密码(

  • javascript中判断一个值是否在数组中并没有直接使用

    在JS中要判断一个值是否在数组中并没有函数直接使用,如PHP中就有in_array()这个函数.但我们可以写一个类似in_array()函数来判断是一个值否在函数中 例1 复制代码 代码如下: /* * * 判断在数组中是否含有给定的一个变量值 * 参数: * needle:需要查询的值 * haystack:被查询的数组 * 在haystack中查询needle是否存在,如果找到返回true,否则返回false. * 此函数只能对字符和数字有效 * */ function findnum(){

  • php 删除一维数组中某一个值元素的操作方法

    1. 自己写for循环 从array里去掉$tmp这个元素的值 <?php $tmp = '324'; $arr = array( '0' => '321', '1' => '322', '2' => '323', '3' => '324', '4' => '325', '5' => '326', ); 代码 foreach( $arr as $k=>$v) { if($tmp == $v) unset($arr[$k]); } print_r($arr);

  • 详解python中的defaultdict 默认值

    目录 前言 语法 setdefault()和defaultdict()的区别 setdefault() defaultdict() 前言 在字典中查找某一个值的时候,若key不存在就会返回一个keyerror错误而不是一个默认值,如果想要返回一个默认值可以使用defaultdict函数. defaultdict是python内建dict类的一个字类,功能与dict相同,但它带有一个默认的值,若key值不存在时返回一个默认的值. 语法 dict =defaultdict( factory_func

  • PostgreSQL批量修改函数拥有者的操作

    Postgresql如何批量修改函数拥有者,默认创建的函数对象的拥有者为当前创建的用户,如果使用postgres超级管理员创建一个test()的函数,拥有者就是postgres用户.下面讲解下如何批量修改拥有者. 本文演示的Postgresql版本如下: PostgreSQL 9.6.8 相关视图 要查询Postgresql的函数和函数参数需要使用函数视图和参数视图,分别记录了函数信息和参数列表信息. 视图一: information_schema.routines 视图routines包含当前

随机推荐