postgresql 实现修改jsonb字段中的某一个值
我就废话不多说了,大家还是直接看代码吧~
UPDATEtablenameSETtags=jsonb_set(tags-'landuse_area','{landuse_area}',('"'||round((ST_Area(ST_Transform(geom,4527))*0.0015)::NUMERIC,3)||'"')::jsonb,TRUE)WHEREtags@>'{"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类型存储稍慢,使用较快。
注意:键值对的键必须使用双引号
从PostgreSQL9.3开始,json就成了postgres里的一种数据类型,也就是和varchar、int一样,我们表里的一个字段的类型可以为json了。
与此同时,postgres还提供了jsonb格式,jsonb格式是json的二进制形式,二者的区别在于json写入快,读取慢,jsonb写入慢,读取快,但在操作上,二者是没有区别的。下面以jsonb为例。
创建表
假设我们要存储的json数据是这样的:
{ "id":ID "name":"名字", "age":年龄 }
建表语句如下:
createtableifnotexistsname_age( infojsonb )
好了,这样就创建了一张表,里面只有一个info字段,下面开始进行CRUD操作。
插入数据
插入数据可以直接以json格式插入:
insertintoname_agevalues('{"id":1,"name":"小明","age":18}')
在json里插入新的key值gender,如下:
SELECTinfo||'{"gender":"男"}'::jsonbfromname_agewhere(info->>'id')::int4=1
查询数据
Postgres里的查询需要用到查询符。比如说,我们要查询id为1的数据,语句如下:
selectinfofromname_agewhereinfo@>'{"id":1}'::jsonb
用到了@>这个查询符,表明info当前这条记录里的顶层json中有没有id为1的key-value对;有的话则满足条件。
再来一个复杂一点的查询的,查询age>16的记录,并且只显示name,语句如下:
selectinfo->'name'fromname_agewhere(info->>'age')::int4>16
关于详细运算符使用,请参考官方文档:9.15.JSONFunctionsandOperators
修改数据
下面,将age从18改为22,SQL语句:
SELECTinfo||'{"age":22}'::jsonbfromname_agewhere(info->>'id')::int4=1
上述用法仅适用于9.5以上,9.5以下需要整个记录更新,不可以单独修改某个值。
除了操作符以外,还可以使用函数操作:jsonb_set(),函数签名如下:
jsonb_set(targetjsonb,pathtext[],new_valuejsonb[,create_missingboolean])
详细使用可参考9.15.JSONFunctionsandOperators
删除数据
删除age这个key,SQL如下:
SELECTinfo-'age'fromname_agewhere(info->>'id')::int4=1
直接用操作符-即可。
总结
PostgreSQL9.5以上的版本中有了很多方便的操作符,使得操作json变得非常方便了。
json和jsonb的操作符
获取JSON数组元素为text
在指定的路径获取JSON对象
在指定的路径获取JSON对象为text
jsonb额外操作符
select'{"a":{"b":2}}'::jsonb@>'{"b":2}'::jsonb;
select'{"a":1,"b":2}'::jsonb@>'{"b":2}'::jsonb;
f
t
删除数组指定索引处的元素,如果索引值为负数,则从右边计算索引值。
如果最上层容器内不是数组,则抛出错误。
删除指定路径下的域或元素(如果是json数组,且整数值是负的,
则索引值从右边算起)
json创建函数
to_json(anyelement)
to_jsonb(anyelement)
返回json或jsonb类型的值。数组和复合被转换(递归)成数组和对象。另外除数字、
布尔、NULL值(直接使用NULL抛出错误)外,其他标量必须有类型转换。(此处请参考原文)
array_to_json(anyarray
[,pretty_bool])
以JSON数组返回该数组。PostgreSQL多维数组变成JSON数组中的数组。
如果pretty_bool为真,则在维度1元素之间添加换行。
[[1,5],+
[99,100]]
{"f1":1,+
"f2":"foo"}
json_build_array(VARIADIC"any")
jsonb_build_array(VARIADIC"any")
json_build_object(VARIADIC"any")
jsonb_build_object(VARIADIC"any")
json_object(text[])
jsonb_object(text[])
根据text[]数组建立一个json对象,如果是一维数组,则必须有偶数个
元素,元素交替组成键和值。如果是二维数组,则每个元素必须有2个元素,可以组成键值对。
selectjson_object('{a,1,b,"def",c,3.5}');
selectjson_object('{{a,1},{b,"def"},{c,3.5}}');
json_object(keystext[],valuestext[])
jsonb_object(keystext[],valuestext[])
json处理函数
json_array_length(json)
jsonb_array_length(jsonb)
json_each(json)
jsonb_each(jsonb)
setofkeytext,valuejson
setofkeytext,valuejsonb
(a,"""foo""")
(b,"""bar""")
json_each_text(json)
jsonb_each_text(jsonb)
(a,foo)
(b,bar)
json_extract_path(from_jsonjson,
VARIADICpath_elemstext[])
jsonb_extract_path(from_jsonjsonb,
VARIADICpath_elemstext[])
json
jsonb
json_extract_path_text(from_jsonjson,
VARIADICpath_elemstext[])
jsonb_extract_path_text(from_jsonjsonb,
VARIADICpath_elemstext[])
json_object_keys(json)
jsonb_object_keys(jsonb)
f1
f2
json_populate_record(baseanyelement,
from_jsonjson)
jsonb_populate_record(baseanyelement,
from_jsonjsonb)
表tbl_test定义:
Table"public.tbl_test"
Column|Type|Modifiers
--------+-----------------------+-----------
a|bigint|
b|charactervarying(32)|
c|charactervarying(32)|
select*fromjson_populate_record(null::tbl_test,'{"a":1,"b":2}');
a|b|c
---+---+------
1|2|NULL
json_populate_recordset(baseanyelement,
from_jsonjson)
jsonb_populate_recordset(baseanyelement,
from_jsonjsonb)
表定义同上
select*fromjson_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)
setofjson
setofjsonb
1
true
[2,false]
json_array_elements_text(json)
jsonb_array_elements_text(jsonb)
foo
bar
json_typeof(json)
jsonb_typeof(jsonb)
返回json最外层value的数据类型,可能的类型有
object,array,string,number,boolean,和null.
json_to_record(json)
jsonb_to_record(jsonb)
a|b|d
---+---------+------
1|[1,2,3]|NULL
json_to_recordset(json)
jsonb_to_recordset(jsonb)
a|b
---+------
1|foo
2|NULL
json_strip_nulls(from_jsonjson)
jsonb_strip_nulls(from_jsonjsonb)
json
jsonb
selectjson_strip_nulls('[{"f1":1,"f2":null},2,null,3]');
jsonb_set(targetjsonb,pathtext[],new_valuejsonb[,create_missingboolean])
selectjsonb_set('[{"f1":1,"f2":null},2,null,3]','{0,f1}','[2,3,4]',false);
selectjsonb_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(targetjsonb,pathtext[],
new_valuejsonb,[insert_afterboolean])
selectjsonb_insert('{"a":[0,1,2]}','{a,1}','"new_value"');
selectjsonb_insert('{"a":[0,1,2]}','{a,1}','"new_value"',true);
{"a":[0,"new_value",1,2]}
{"a":[0,1,"new_value",2]}
[
{
"f1":1,
"f2":null
},
2,
null,
3
]
以上为个人经验,希望能给大家一个参考,也希望大家多多支持毛票票。如有错误或未考虑完全的地方,望不吝赐教。