域名預(yù)訂/競(jìng)價(jià)，好“米”不錯(cuò)過(guò)

這篇文章主要介紹了postgresql 實(shí)現(xiàn)修改jsonb字段中的某一個(gè)值操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧。

我就廢話不多說(shuō)了，大家還是直接看代碼吧~

1UPDATE 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是計(jì)算多邊形的面積，后面的0.0015是面積單位轉(zhuǎn)換為畝

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

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

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

補(bǔ)充：postgresql----JSON和JSONB類型的增刪改查

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

注意：鍵值對(duì)的鍵必須使用雙引號(hào)

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

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

創(chuàng)建表

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

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

建表語(yǔ)句如下：

create table if not exists name_age (
info jsonb
)

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

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

插入數(shù)據(jù)可以直接以json格式插入：

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

在json里插入新的key值gender，如下：

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

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

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

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

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

再來(lái)一個(gè)復(fù)雜一點(diǎn)的查詢的，查詢 age>16 的記錄，并且只顯示 name ，語(yǔ)句如下：

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

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

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

下面，將 age 從 18 改為 22 ，SQL語(yǔ)句：

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

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

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

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

詳細(xì)使用可參考 9.15. JSON Functions and Operators

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

刪除age這個(gè)key，SQL如下：

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

直接用操作符 - 即可。

總結(jié)

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

json和jsonb的操作符

操作符右操作數(shù)類型描述示例結(jié)果

->int獲取JSON數(shù)組元素（索引從0開(kāi)始）select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;{"c":"baz"}

->text通過(guò)鍵獲取值select '{"a": {"b":"foo"}}'::json->'a';{"b":"foo"}

->>int獲取JSON數(shù)組元素為 textselect '[1,2,3]'::json->>2;3

->>text通過(guò)鍵獲取值為textselect '{"a":1,"b":2}'::json->>'b';2

#>text[]在指定的路徑獲取JSON對(duì)象select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';{"c": "foo"}

#>>text[]在指定的路徑獲取JSON對(duì)象為 textselect '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';3

jsonb額外操作符

操作符右操作數(shù)類型描述示例結(jié)果

@>jsonb左側(cè)json最上層的值是否包含右邊json對(duì)象select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb;

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

t

<@jsonb左側(cè)json對(duì)象是否包含于右側(cè)json最上層的值內(nèi)select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb;t

?texttext是否作為左側(cè)Json對(duì)象最上層的鍵select '{"a":1, "b":2}'::jsonb ? 'b';t

?|text[]text[]中的任一元素是否作為左側(cè)Json對(duì)象最上層的鍵select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'];t

?&text[]text[]中的所有元素是否作為左側(cè)Json對(duì)象最上層的鍵select '["a", "b"]'::jsonb ?& array['a', 'b'];t

||jsonb連接兩個(gè)json對(duì)象，組成一個(gè)新的json對(duì)象select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;["a", "b", "c", "d"]

-text刪除左側(cè)json對(duì)象中鍵為text的鍵值對(duì)select '{"a": "b"}'::jsonb - 'a';{}

-integer刪除數(shù)組指定索引處的元素，如果索引值為負(fù)數(shù)，則從右邊計(jì)算索引值。

如果最上層容器內(nèi)不是數(shù)組，則拋出錯(cuò)誤。select '["a", "b"]'::jsonb - 1;["a"]

#-text[]刪除指定路徑下的域或元素（如果是json數(shù)組，且整數(shù)值是負(fù)的，

則索引值從右邊算起）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ù)組和對(duì)象。另外除數(shù)字、

布爾、NULL值（直接使用NULL拋出錯(cuò)誤）外，其他標(biāo)量必須有類型轉(zhuǎn)換。（此處請(qǐng)參考原文）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對(duì)象返回行。如果pretty_bool 為真，則在級(jí)別1元素之間添加換行。select row_to_json(row(1,'foo'),true);{"f1":1, +

"f2":"foo"}

json_build_array(VARIADIC "any")

jsonb_build_array(VARIADIC "any")建立一個(gè)由可變參數(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")建立一個(gè)由可變參數(shù)列表組成的JSON對(duì)象。參數(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ù)組建立一個(gè)json對(duì)象，如果是一維數(shù)組，則必須有偶數(shù)個(gè)

元素，元素交替組成鍵和值。如果是二維數(shù)組，則每個(gè)元素必須有2個(gè)元素，可以組成鍵值對(duì)。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ù)組最外層元素個(gè)數(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對(duì)象轉(zhuǎn)換為鍵值對(duì)集合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對(duì)象轉(zhuǎn)換為鍵值對(duì)集合，且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對(duì)象最外層的keyselect 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對(duì)象的value以base定義的行類型返回，如果行類型字段比json對(duì)象鍵值少，則多出的鍵值將被拋棄；如果行類型字段多，則多出的字段自動(dòng)填充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對(duì)象最外層數(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對(duì)象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對(duì)象創(chuàng)建一個(gè)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)建一個(gè)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對(duì)象中所有非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]}

文章來(lái)源：腳本之家

來(lái)源地址：https://www.jb51.net/article/204887.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！