postgresql 實現(xiàn)修改jsonb字段中的某一個值

更新時間：2021年01月27日 11:22:48 作者：weixin_43169720

這篇文章主要介紹了postgresql 實現(xiàn)修改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位小數(shù)

st_area是計算多邊形的面積，后面的0.0015是面積單位轉(zhuǎn)換為畝

st_transform是投影轉(zhuǎn)換，這里我表中原始的geom是4326，不適合計算面積，所以在這里轉(zhuǎn)換為4527計算面積

::NUMERIC是轉(zhuǎn)換為numeric數(shù)據(jù)類型

::jsonb是轉(zhuǎn)換為jsonb類型

補充：postgresql----JSON和JSONB類型的增刪改查

postgresql支持兩種json數(shù)據(jù)類型：json和jsonb，而兩者唯一的區(qū)別在于效率,json是對輸入的完整拷貝，使用時再去解析，所以它會保留輸入的空格，重復(fù)鍵以及順序等。而jsonb是解析輸入后保存的二進制，它在解析時會刪除不必要的空格和重復(fù)的鍵，順序和輸入可能也不相同。使用時不用再次解析。兩者對重復(fù)鍵的處理都是保留最后一個鍵值對。效率的差別：json類型存儲快，使用慢，jsonb類型存儲稍慢，使用較快。

注意：鍵值對的鍵必須使用雙引號

從PostgreSQL 9.3開始，json就成了postgres里的一種數(shù)據(jù)類型，也就是和varchar、int一樣，我們表里的一個字段的類型可以為json了。

與此同時，postgres還提供了jsonb格式，jsonb格式是json的二進制形式，二者的區(qū)別在于json寫入快，讀取慢，jsonb寫入慢，讀取快，但在操作上，二者是沒有區(qū)別的。下面以jsonb為例。

創(chuàng)建表

假設(shè)我們要存儲的json數(shù)據(jù)是這樣的：

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

建表語句如下：

create table if not exists name_age (
info jsonb
)

好了，這樣就創(chuàng)建了一張表，里面只有一個 info 字段，下面開始進行CRUD操作。

插入數(shù)據(jù)

插入數(shù)據(jù)可以直接以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

查詢數(shù)據(jù)

Postgres里的查詢需要用到查詢符。比如說，我們要查詢id為1的數(shù)據(jù)，語句如下：

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

用到了 @> 這個查詢符，表明info當(dāng)前這條記錄里的頂層json中有沒有id為1的key-value對；有的話則滿足條件。

再來一個復(fù)雜一點的查詢的，查詢 age>16 的記錄，并且只顯示 name ，語句如下：

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

關(guān)于詳細運算符使用，請參考官方文檔： 9.15. JSON Functions and Operators

修改數(shù)據(jù)

下面，將 age 從 18 改為 22 ，SQL語句：

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

上述用法僅適用于9.5以上，9.5以下需要整個記錄更新，不可以單獨修改某個值。

除了操作符以外，還可以使用函數(shù)操作： jsonb_set() ，函數(shù)簽名如下：

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

詳細使用可參考 9.15. JSON Functions and Operators

刪除數(shù)據(jù)

刪除age這個key，SQL如下：

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

直接用操作符 - 即可。

總結(jié)

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

json和jsonb的操作符

操作符	右操作數(shù)類型	描述	示例	結(jié)果
->	int	獲取JSON數(shù)組元素（索引從0開始）	select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;	{"c":"baz"}
->	text	通過鍵獲取值	select '{"a": {"b":"foo"}}'::json->'a';	{"b":"foo"}
->>	int	獲取JSON數(shù)組元素為 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額外操作符

操作符	右操作數(shù)類型	描述	示例	結(jié)果
@>	jsonb	左側(cè)json最上層的值是否包含右邊json對象	select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb; select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;	f t
<@	jsonb	左側(cè)json對象是否包含于右側(cè)json最上層的值內(nèi)	select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb;	t
?	text	text是否作為左側(cè)Json對象最上層的鍵	select '{"a":1, "b":2}'::jsonb ? 'b';	t
?\|	text[]	text[]中的任一元素是否作為左側(cè)Json對象最上層的鍵	select '{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'c'];	t
?&	text[]	text[]中的所有元素是否作為左側(cè)Json對象最上層的鍵	select '["a", "b"]'::jsonb ?& array['a', 'b'];	t
\|\|	jsonb	連接兩個json對象，組成一個新的json對象	select '["a", "b"]'::jsonb \|\| '["c", "d"]'::jsonb;	["a", "b", "c", "d"]
-	text	刪除左側(cè)json對象中鍵為text的鍵值對	select '{"a": "b"}'::jsonb - 'a';	{}
-	integer	刪除數(shù)組指定索引處的元素，如果索引值為負數(shù)，則從右邊計算索引值。如果最上層容器內(nèi)不是數(shù)組，則拋出錯誤。	select '["a", "b"]'::jsonb - 1;	["a"]
#-	text[]	刪除指定路徑下的域或元素（如果是json數(shù)組，且整數(shù)值是負的，則索引值從右邊算起）	select '["a", {"b":1}]'::jsonb #- '{1,b}';	["a", {}]

json創(chuàng)建函數(shù)

函數(shù)	描述	示例	結(jié)果
to_json(anyelement) to_jsonb(anyelement)	返回json或jsonb類型的值。數(shù)組和復(fù)合被轉(zhuǎn)換（遞歸）成數(shù)組和對象。另外除數(shù)字、布爾、NULL值（直接使用NULL拋出錯誤）外，其他標量必須有類型轉(zhuǎn)換。（此處請參考原文）	select to_json('3'::int);	3
array_to_json(anyarray [, pretty_bool])	以JSON數(shù)組返回該數(shù)組。PostgreSQL多維數(shù)組變成JSON數(shù)組中的數(shù)組。如果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")	建立一個由可變參數(shù)列表組成的不同類型的JSON數(shù)組	select json_build_array(1,2,'3',4,5);	[1, 2, "3", 4, 5]
json_build_object(VARIADIC "any") jsonb_build_object(VARIADIC "any")	建立一個由可變參數(shù)列表組成的JSON對象。參數(shù)列表參數(shù)交替轉(zhuǎn)換為鍵和值。	select json_build_object('foo',1,'bar',2);	{"foo" : 1, "bar" : 2}
json_object(text[]) jsonb_object(text[])	根據(jù)text[]數(shù)組建立一個json對象，如果是一維數(shù)組，則必須有偶數(shù)個元素，元素交替組成鍵和值。如果是二維數(shù)組，則每個元素必須有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[]中獲取鍵和值，與一維數(shù)組類似。	select json_object('{a, b}', '{1,2}');	{"a" : "1", "b" : "2"}

json處理函數(shù)

函數(shù)	返回類型	描述	示例	結(jié)果
json_array_length(json) jsonb_array_length(jsonb)	int	返回Json數(shù)組最外層元素個數(shù)	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對象轉(zhuǎn)換為鍵值對集合	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對象轉(zhuǎn)換為鍵值對集合，且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，并轉(zhuǎn)為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對象最外層數(shù)組以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數(shù)組轉(zhuǎn)換成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數(shù)組轉(zhuǎn)換成text的value集合	select json_array_elements_text('["foo", "bar"]');	foo bar
json_typeof(json) jsonb_typeof(jsonb)	text	返回json最外層value的數(shù)據(jù)類型，可能的類型有 object, array, string, number, boolean, 和null.	select json_typeof('-123.4')	number
json_to_record(json) jsonb_to_record(jsonb)	record	根據(jù)json對象創(chuàng)建一個record類型記錄，所有的函數(shù)都返回record類型，所以必須使用as明確定義record的結(jié)構(gòu)。	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	根據(jù)json數(shù)組創(chuàng)建一個record類型記錄，所有的函數(shù)都返回record類型，所以必須使用as明確定義record的結(jié)構(gòu)。	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的數(shù)據(jù)，其他的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 ]