postgresql 實現修改jsonb字段中的某一個值

Posted on by

我就廢話不多說瞭，大傢還是直接看代碼吧~

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
]

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：