快捷導(dǎo)航

PostgreSQL查詢和處理JSON數(shù)據(jù)

更新時(shí)間：2023年11月25日 08:47:29 作者：c_zyer

這篇文章主要給大家介紹了關(guān)于PostgreSQL查詢和處理JSON數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下

前言

由于項(xiàng)目?jī)?nèi)使用的Postgresql 且存儲(chǔ)了一些非結(jié)構(gòu)化的json數(shù)據(jù)，里面含有統(tǒng)計(jì)與記錄，并且有嵌套關(guān)系，所以需要了解如何查詢和處理Postgresql中的JSON數(shù)據(jù)。

Postgresql：9.6
官方文檔：http://postgres.cn/docs/9.6/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE
一張基礎(chǔ)訂單表結(jié)構(gòu)：

-- order表
"id" bigserial primary key,
"order_id" varchar(55) COLLATE "default",
"product_id" int8,
"order_json" text COLLATE "default",
"create_time" timestamp(6),

背景知識(shí)：json和jsonb 操作符

操作符	右操作數(shù)類型	描述	例子	例子結(jié)果
->	int	獲得 JSON 數(shù)組元素（索引從 0 開始，負(fù)整數(shù)結(jié)束）	‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json->2	{“c”:“baz”}
->	text	通過(guò)鍵獲得 JSON 對(duì)象域	‘{“a”: {“b”:“foo”}}’::json->‘a’	{“b”:“foo”}
->>	int	以文本形式獲得 JSON 數(shù)組元素	‘[1,2,3]’::json->>2	3
->>	text	以文本形式獲得 JSON 對(duì)象域	‘{“a”:1,“b”:2}’::json->>‘b’	2
#>	text[]	獲取在指定路徑的 JSON 對(duì)象	‘{“a”: {“b”:{“c”: “foo”}}}’::json#>‘{a,b}’	{“c”: “foo”}
#>>	text[]	以文本形式獲取在指定路徑的 JSON 對(duì)象	‘{“a”:[1,2,3],“b”:[4,5,6]}’::json#>>‘{a,2}’	3

問(wèn)：如何查看JSON指定的key內(nèi)容？

通過(guò)::json的語(yǔ)法

select order_json::json->'orderBody' from order -- 對(duì)象域
select order_json::json->>'orderBody' from order -- 文本
select order_json::json#>'{orderBody}' from order -- 對(duì)象域
select order_json::json#>>'{orderBody}' from order -- 文本

還有更多的jsonb操作符和json操作函數(shù)見官方文檔

問(wèn)：怎么處理多層嵌套的JSON？

就是采用基本的JSON語(yǔ)法，注意結(jié)果是對(duì)象域還是文本，對(duì)象域可以繼續(xù)取用字段，文本就不能繼續(xù)查看JSON咯

select '{"sites":{"site":{"id":"1","name":"菜鳥教程","url":"www.runoob.com"}}}'::json->'sites'->'site' -- 對(duì)象域
select '{"sites":{"site":{"id":"1","name":"菜鳥教程","url":"www.runoob.com"}}}'::json->'sites'->>'site' -- 文本

問(wèn)：怎么處理JSON數(shù)組呢?

也是通過(guò)JSON的基本操作先定位到數(shù)組對(duì)象所在的Key,通過(guò)key取到對(duì)應(yīng)的value后直接->(0),就可以取用到對(duì)應(yīng)的對(duì)象域，注意對(duì)象域和文本，轉(zhuǎn)化為文本就不能夠在取key和具體數(shù)據(jù)數(shù)據(jù)咯

還有很多關(guān)于json相關(guān)的方法，可以詳見官方文檔

select '{"sites":{"site":[{"id":"1","name":"菜鳥教程","url":"www.runoob.com"},{"id":"2","name":"菜鳥工具","url":"c.runoob.com"},{"id":"3","name":"Google","url":"www.google.com"}]}}'::json->'sites'->'site'->(0)

select '{"sites":{"site":[{"id":"1","name":"菜鳥教程","url":"www.runoob.com"},{"id":"2","name":"菜鳥工具","url":"c.runoob.com"},{"id":"3","name":"Google","url":"www.google.com"}]}}'::json->'sites'->'site'->(0)->>'id'

延伸：如何取用JSON數(shù)組的最后一個(gè)對(duì)象數(shù)據(jù)？

select json_array_length('{"sites":{"site":[{"id":"1","name":"菜鳥教程","url":"www.runoob.com"},{"id":"2","name":"菜鳥工具","url":"c.runoob.com"},{"id":"3","name":"Google","url":"www.google.com"}]}}'::json->'sites'->'site') -- 查詢json數(shù)據(jù)的長(zhǎng)度

select '{"sites":{"site":[{"id":"1","name":"菜鳥教程","url":"www.runoob.com"},{"id":"2","name":"菜鳥工具","url":"c.runoob.com"},{"id":"3","name":"Google","url":"www.google.com"}]}}'::json->'sites'->'site'->(json_array_length('{"sites":{"site":[{"id":"1","name":"菜鳥教程","url":"www.runoob.com"},{"id":"2","name":"菜鳥工具","url":"c.runoob.com"},{"id":"3","name":"Google","url":"www.google.com"}]}}'::json->'sites'->'site') -1)

問(wèn)：怎么替換JSON字符串中的內(nèi)容？

通過(guò)select語(yǔ)句先看一下官方語(yǔ)法

函數(shù)	返回值	描述	例子	例子結(jié)果
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])	jsonb	如果create_missing是真的（缺省是true）并且通過(guò)path 指定部分不存在，那么返回target，它具有path指定部分， new_value替換部分，或者new_value添加部分。正如路徑導(dǎo)向的操作符，負(fù)整數(shù)出現(xiàn)在JSON數(shù)組結(jié)尾的path>計(jì)數(shù)中。	(1)jsonb_set(‘[{“f1”:1,“f2”:null},2,null,3]’, ‘{0,f1}’,‘[2,3,4]’, false) (2)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_set的方法

第一位參數(shù)，需要是jsonb的對(duì)象域
第二位參數(shù)，是訪問(wèn)對(duì)應(yīng)value的path(注意這個(gè)path的語(yǔ)法可以是{a,b},表名key-a中的key-b,數(shù)據(jù)的話參看表格中的(2))
第三位參數(shù)，就是一個(gè)新的值，來(lái)替換第一個(gè)參數(shù)中的第二個(gè)參數(shù)key的value
第四個(gè)參數(shù)，如果create_missing是真的（缺省是true）并且通過(guò)path 指定部分不存在，那么返回target，它具有path指定部分， new_value替換部分，或者new_value添加部分。正如路徑導(dǎo)向的操作符，負(fù)整數(shù)出現(xiàn)在JSON數(shù)組結(jié)尾的path>計(jì)數(shù)中

參照官方文檔，簡(jiǎn)單的一次內(nèi)容替換

select jsonb_set(order_json::jsonb,'{premsg}','test'::jsonb) from order

那么如果是嵌套多層的JSON value可以替換嗎？–可以的，語(yǔ)法是一樣的，就是需要定位到指定的字段就可以

select jsonb_set((order_json::json->>'rspDesc')::jsonb, '{preOrder}', '"11111"'::jsonb) from order

上面是select語(yǔ)句，那具體的update語(yǔ)句怎么寫呢？

語(yǔ)法：UPDATE 表明 set 列名 = (jsonb_set(列名::jsonb,'{key}','"value"'::jsonb)) where 條件 

update order set order_json = jsonb_set(order_json::jsonb,'{rspDesc}',(jsonb_set((event_json::json->>'rspDesc')::jsonb, '{preNumber}', '"999999999"'::jsonb)::jsonb)) -- 需要先把需要改的內(nèi)容替換好，然后在整體更新替換，此時(shí)這個(gè)rspDesc是對(duì)象域格式

網(wǎng)上基本沒(méi)有執(zhí)行成功的例子，在此記錄下。