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

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

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

前言

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

Postgresql：9.6
官方文檔：http://postgres.cn/docs/9.6/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE
一張基礎訂單表結構：

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

背景知識：json和jsonb 操作符

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

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

通過::json的語法

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

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

問：怎么處理多層嵌套的JSON？

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

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

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

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

還有很多關于json相關的方法，可以詳見官方文檔

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ù)組的最后一個對象數(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ù)的長度

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)

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

通過select語句先看一下官方語法

函數(shù)	返回值	描述	例子	例子結果
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])	jsonb	如果create_missing是真的（缺省是true）并且通過path 指定部分不存在，那么返回target，它具有path指定部分， new_value替換部分，或者new_value添加部分。正如路徑導向的操作符，負整數(shù)出現(xiàn)在JSON數(shù)組結尾的path>計數(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的對象域
第二位參數(shù)，是訪問對應value的path(注意這個path的語法可以是{a,b},表名key-a中的key-b,數(shù)據(jù)的話參看表格中的(2))
第三位參數(shù)，就是一個新的值，來替換第一個參數(shù)中的第二個參數(shù)key的value
第四個參數(shù)，如果create_missing是真的（缺省是true）并且通過path 指定部分不存在，那么返回target，它具有path指定部分， new_value替換部分，或者new_value添加部分。正如路徑導向的操作符，負整數(shù)出現(xiàn)在JSON數(shù)組結尾的path>計數(shù)中

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

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

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

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

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

語法：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)容替換好，然后在整體更新替換，此時這個rspDesc是對象域格式

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