PostgreSQL操作json/jsonb方法詳解

更新時間：2023年09月30日 08:00:17 作者：一碗情深

這篇文章主要給大家介紹了關(guān)于PostgreSQL操作json/jsonb的相關(guān)資料,PostgreSQL提供了兩種存儲JSON數(shù)據(jù)的類型:json和jsonb; jsonb是json的二進制形式,文中介紹的非常詳細,需要的朋友可以參考下

前言

PostgreSQL 9.2 中引入了對JSON類型的支持，經(jīng)過幾個大版本的進化，目前對JSON數(shù)字類型的支持已經(jīng)比較完善。在PG中對JSON格式信息的CRUD操作，針對具體的節(jié)點建立索引，這些都可以很容易的實現(xiàn)。本文以 PostgreSQL 15.3 版本作為演示，希望小伙伴們能有所收獲。

兩種數(shù)據(jù)類型json和jsonb

PG中提供了兩種不同的數(shù)據(jù)類型，分別是JSON和JSONB。顧名思義，JSON是存儲字符串的原始格式，而JSONB是二進制編碼版本。JSON需要存儲包括空格等原始格式，所以在每次查詢的時候都會有解析過程。而JSONB查詢時不需要實時解析，所以更高效。

簡而言之，JSON 為了準(zhǔn)確存儲，插入快查詢慢；JSONB 為了高效查詢，插入慢檢索快。

如果沒有特殊理由，最好使用JSONB類型。

json和jsonb操作符

操作符	右操作數(shù)類型	描述	例子	例子結(jié)果
->	integer	獲得 JSON 數(shù)組的第n個元素（數(shù)組元素從0開始索引，負整數(shù)從末尾開始計算）	‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json->2	{“c”:“baz”}
->	text	通過鍵獲得 JSON 對象域	‘{“a”: {“b”:“foo”}}’::json->‘a’	{“b”:“foo”}
->>	integer	以文本形式獲得 JSON 數(shù)組的第n個元素	‘[1,2,3]’::json->>2	3
->>	text	以文本形式獲得 JSON 對象域	‘{“a”:1,“b”:2}’::json->>‘b’	2
#>	text[]	獲取在指定路徑的 JSON 對象，其中路徑元素可以是字段鍵或數(shù)組索引	‘{“a”: {“b”:{“c”: “foo”}}}’::json#>‘{a,b}’	{“c”: “foo”}
#>>	text[]	以文本形式獲取在指定路徑的 JSON 對象	‘{“a”:[1,2,3],“b”:[4,5,6]}’::json#>>‘{a,2}’	3

注意

對json和jsonb類型，這些操作符都有其并行變體。域/元素/路徑抽取操作符返回與其左手輸入（json或jsonb）相同的類型，不過那些被指定為返回text的，它們的返回值會被強制為文本。如果該 JSON 輸入沒有匹配請求的正確結(jié)構(gòu)（例如那樣的元素不存在），這些域/元素/路徑抽取操作符會返回 NULL 而不是失敗。接受整數(shù)JSON數(shù)組下標(biāo)的域/元素/路徑提取操作符支持?jǐn)?shù)組末尾的負下標(biāo)。

額外的jsonb操作符

操作符	右操作數(shù)類型	描述	例子	例子結(jié)果
@>	jsonb	左邊的 JSON 值是否包含頂層右邊JSON路徑/值項？	‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb	true
<@	jsonb	左邊的JSON路徑/值是否包含在頂層右邊JSON值中？	‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb	true
?	text	字符串是否作為頂層鍵值存在于JSON值中？	‘{“a”:1, “b”:2}’::jsonb ? ‘b’	true
?\|	text[]	這些數(shù)組字符串中的任何一個是否作為頂層鍵值存在？	‘{“a”:1, “b”:2, “c”:3}’::jsonb ?\| array[‘b’, ‘c’]	true
?&	text[]	這些數(shù)組字符串是否作為頂層鍵值存在？	‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’]	true
\|\|	jsonb	連接兩個jsonb值到新的jsonb值	‘[“a”, “b”]’::jsonb \|\| ‘[“c”, “d”]’::jsonb	[“a”, “b”, “c”, “d”]
-	text	從左操作數(shù)中刪除鍵/值對或字符串元素。基于鍵值匹配鍵/值對。	‘{“a”: “b”}’::jsonb - ‘a’	{}
-	text[]	從左操作數(shù)中刪除多個鍵/值對或string元素。鍵/值對基于其鍵值進行匹配。	‘{“a”: “b”, “c”: “d”}’::jsonb - ‘{a,c}’::text[]	{}
-	integer	刪除指定索引的數(shù)組元素（負整數(shù)結(jié)尾）。如果頂層容器不是一個數(shù)組，那么拋出錯誤。	‘[“a”, “b”]’::jsonb - 1	[“a”]
#-	text[]	刪除指定路徑的域或元素（JSON數(shù)組，負整數(shù)結(jié)尾）	‘[“a”, {“b”:1}]’::jsonb #- ‘{1,b}’	[“a”, {}]

注意

操作符連接每個操作數(shù)頂層的元素。它不遞歸操作。比如，如果兩個操作數(shù)都是具有共同鍵字段名稱的對象，則結(jié)果中字段值只會是從右邊操作數(shù)的值。

JSON/JSONB 創(chuàng)建函數(shù)

函數(shù)	描述	例子	例子結(jié)果
to_json(anyelement) to_jsonb(anyelement)	把值返回為json或者jsonb。數(shù)組和組合被（遞歸地）轉(zhuǎn)換成數(shù)組和對象；否則，如果有從該類型到j(luò)son的投影，將使用該投影函數(shù)來執(zhí)行轉(zhuǎn)換；否則將產(chǎn)生一個標(biāo)量值。對任何一個數(shù)值、布爾量或空值的標(biāo)量類型，將使用其文本表達，以這樣一種方式使其成為有效的json或者jsonb值。	to_json(‘Fred said “Hi.”’::text)	“Fred said “Hi.””
array_to_json(anyarray [, pretty_bool])	把數(shù)組作為一個 JSON 數(shù)組返回。一個 PostgreSQL 多維數(shù)組會成為一個數(shù)組的 JSON 數(shù)組。如果pretty_bool為真，將在第 1 維度的元素之間增加換行。	array_to_json(‘{{1,5},{99,100}}’::int[])	[[1,5],[99,100]]
row_to_json(record [, pretty_bool])	把行作為一個 JSON 對象返回。如果pretty_bool為真，將在第1層元素之間增加換行。	row_to_json(row(1,‘foo’))	{“f1”:1,“f2”:“foo”}
json_build_array(VARIADIC “any”) jsonb_build_array(VARIADIC “any”)	從一個可變參數(shù)列表構(gòu)造一個可能包含異質(zhì)類型的 JSON 數(shù)組。	json_build_array(1,2,‘3’,4,5)	[1, 2, “3”, 4, 5]
json_build_object(VARIADIC “any”) jsonb_build_object(VARIADIC “any”)	從一個可變參數(shù)列表構(gòu)造一個 JSON 對象。通過轉(zhuǎn)換，該參數(shù)列表由交替出現(xiàn)的鍵和值構(gòu)成。	json_build_object(‘foo’,1,‘bar’,2)	{“foo”: 1, “bar”: 2}
json_object(text[]) jsonb_object(text[])	從一個文本數(shù)組構(gòu)造一個 JSON 對象。該數(shù)組必須可以是具有偶數(shù)個成員的一維數(shù)組（成員被當(dāng)做交替出現(xiàn)的鍵/值對），或者是一個二維數(shù)組（每一個內(nèi)部數(shù)組剛好有 2 個元素，可以被看做是鍵/值對）。	json_object(‘{a, 1, b, “def”, c, 3.5}’) 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[])	json_object的這種形式從兩個獨立的數(shù)組得到鍵/值對。在其他方面和一個參數(shù)的形式相同。	json_object(‘{a, b}’, ‘{1,2}’)	{“a”: “1”, “b”: “2”}

注意

array_to_json 和 row_to_json 與 to_json 具有相同的行為，不過它們提供了更好的打印選項。針對to_json所描述的行為同樣也適用于由其他 JSON 創(chuàng)建函數(shù)轉(zhuǎn)換的每個值。

JSON/JSONB 處理函數(shù)

函數(shù)	返回值	描述	例子	例子結(jié)果
json_array_length(json) jsonb_array_length(jsonb)	int	返回最外層 JSON 數(shù)組中的元素數(shù)量。	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 * from json_each(‘{“a”:“foo”, “b”:“bar”}’)	key \| value -----±------ a \| “foo” b \| “bar”
json_each_text(json) jsonb_each_text(jsonb)	setof key text, value text	擴展最外層的 JSON 對象成為一組鍵/值對。返回值將是文本類型。	select * from json_each_text(‘{“a”:“foo”, “b”:“bar”}’)	key \| value -----±------ 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指向的 JSON 值（等效于#>操作符）。	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指向的 JSON 值（等效于 #>> 操作符）。	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 對象中的鍵集合。	json_object_keys(‘{“f1”:“abc”,“f2”:{“f3”:“a”, “f4”:“b”}}’)	json_object_keys ------------------ f1 f2
json_populate_record(base anyelement, from_json json) jsonb_populate_record(base anyelement, from_json jsonb)	anyelement	擴展from_json中的對象成一個行，它的列匹配由base定義的記錄類型（見下文的注釋）。	select * from json_populate_record(null::myrowtype, ‘{“a”: 1, “b”: [“2”, “a b”], “c”: {“d”: 4, “e”: “a b c”}}’)	a \| b \| c —±----------±------------ 1 \| {2,“a b”} \| (4,“a b c”)
json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb)	setof anyelement	擴展from_json中最外的對象數(shù)組為一個集合，該集合的列匹配由base定義的記錄類型。	select * from json_populate_recordset(null::myrowtype, ‘[{“a”:1,“b”:2},{“a”:3,“b”:4}]’)	a \| b —±– 1 \| 2 3 \| 4
json_array_elements(json) jsonb_array_elements(jsonb)	setof json setof jsonb	把一個 JSON 數(shù)組擴展成一個 JSON 值的集合。	select * from json_array_elements(‘[1,true, [2,false]]’)	value ----------- 1 true [2,false]
json_array_elements_text(json) jsonb_array_elements_text(jsonb)	setof text	把一個 JSON 數(shù)組擴展成一個text值集合。	select * from json_array_elements_text(‘[“foo”, “bar”]’)	value ----------- foo bar
json_typeof(json) jsonb_typeof(jsonb)	text	把最外層的 JSON 值的類型作為一個文本字符串返回?？赡艿念愋褪牵?object、array、string、number、 boolean以及null。	json_typeof(‘-123.4’)	number
json_to_record(json) jsonb_to_record(jsonb)	record	從一個 JSON 對象（見下文的注解）構(gòu)建一個任意的記錄。正如所有返回record 的函數(shù)一樣，調(diào)用者必須用一個AS子句顯式地定義記錄的結(jié)構(gòu)。	select * from json_to_record(‘{“a”:1,“b”:[1,2,3],“c”:[1,2,3],“e”:“bar”,“r”: {“a”: 123, “b”: “a b c”}}’) as x(a int, b text, c int[], d text, r myrowtype)	a \| b \| c \| d \| r —±--------±--------±–±-------------- 1 \| [1,2,3] \| {1,2,3} \| \| (123,“a b c”)
json_to_recordset(json) jsonb_to_recordset(jsonb)	setof record	從一個 JSON 對象數(shù)組（見下文的注解）構(gòu)建一個任意的記錄集合。正如所有返回record 的函數(shù)一樣，調(diào)用者必須用一個AS子句顯式地定義記錄的結(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 \|
json_strip_nulls(from_json json) jsonb_strip_nulls(from_json jsonb)	json jsonb	返回具有空值對象域的from_json。其它空值不變。	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）并且通過path 指定部分不存在，那么返回target，它具有path指定部分， new_value替換部分，或者new_value添加部分。正如路徑導(dǎo)向的操作符，負整數(shù)出現(xiàn)在JSON數(shù)組結(jié)尾的path>計數(shù)中。	jsonb_set(‘[{“f1”:1,“f2”:null},2,null,3]’, ‘{0,f1}’,‘[2,3,4]’, false) 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	返回被插入了new_value的target。如果path指定的target 節(jié)在一個 JSONB 數(shù)組中，new_value 將被插入到目標(biāo)之前（insert_after為false，默認情況）或者之后（insert_after為真）。如果path指定的target 節(jié)在一個 JSONB 對象內(nèi)，則只有當(dāng)target 不存在時才插入new_value。對于面向路徑的操作符來說，出現(xiàn)在path中的負整數(shù)表示從 JSON 數(shù)組的末尾開始計數(shù)。	jsonb_insert(‘{“a”: [0,1,2]}’, ‘{a, 1}’, ‘“new_value”’) 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文本返回from_json。	jsonb_pretty(‘[{“f1”:1,“f2”:null},2,null,3]’)	[ { “f1”: 1, “f2”: null }, 2, null, 3 ]
jsonb_concat(jsonb, jsonb)	jsonb	連接兩個jsonb值到新的jsonb值。	select jsonb_concat(‘{“a”: “b”,“test”:“v”}’::jsonb,‘{“test”:[{“name”: “r”}]}’::jsonb);	{“a”: “b”, “test”: [{“name”: “r”}]}
jsonb_contains(jsonb, jsonb)	boolean	判斷第1個jsonb是否包含第2個jsonb（等效于 \|\| 操作符）。	select jsonb_contains(‘{“a”: “b”,“test”: [{“name”: “v”}]}’::jsonb,‘{“test”:[{“name”: “v”}]}’::jsonb);	true

注意

在 json_populate_record、json_populate_recordset、 json_to_record 和 json_to_recordset 中，來自 JSON 的類型強制是“盡力而為”并且對于某些類型可能得不到想要的值。JSON 鍵會被匹配目標(biāo)行類型中相同的列名。沒有出現(xiàn)在目標(biāo)行類型中的 JSON 域?qū)粡妮敵鲋泻雎裕?并且不匹配任何 JSON 域的目標(biāo)列將被簡單地作為 NULL。

所有 jsonb_set 的path參數(shù)項必須存在于target中，除非 create_missing 是真的，在這種情況下，除了最后一項都存在。如果這些條件未滿足target返回不變。

如果最后路徑項是對象鍵，如果它不存在并且給予新值，則被創(chuàng)建。如果最后路徑項是一個數(shù)組索引，如果通過從左邊計數(shù)發(fā)現(xiàn)設(shè)置項是正數(shù)，如果從右邊 -1計數(shù)負數(shù)指定右邊的元素，等等。如果項超出-array_length … array_length -1范圍，并且create_missing是真，如果項是負數(shù)，則在數(shù)組開頭添加新值，如果是正數(shù)，則添加到數(shù)組末尾。

不要把 json_typeof 函數(shù)的null返回值與 SQL 的 NULL 弄混。雖然調(diào)用json_typeof(‘null’::json)將會返回null，但調(diào)用 json_typeof(NULL::json)將會返回一個 SQL 的 NULL。

如果參數(shù)到j(luò)son_strip_nulls包含任何對象的復(fù)制字段名，其結(jié)果可能是語義上不同，依賴于它們所產(chǎn)生的順序。這不是jsonb_strip_nulls的問題，因為jsonb值從不復(fù)制對象字段名。