PostgreSQL 12 新特性解讀之一|支持 SQL/JSON path
- 2019 年 11 月 21 日
- 筆記
作者介紹
譚峰，網名francs，《PostgreSQL實戰》作者之一，《PostgreSQL 9 Administration Cookbook》譯者之一，PostgreSQL 中文社區委員，致力於PostgreSQL技術分享，博客 https://postgres.fun
PostgreSQL 之前版本已支持 Json 和 Jsonb 數據類型，支持非關係數據的存儲和檢索，如果 Json 數據較複雜(層級多、嵌套json、包含數組等 )，之前版本不能方便的檢索 Json 數據元素值。
PostgreSQL 12 版本的一個重量級特性是新增 SQL/JSON path 特性，支持基於 Json 元素的複雜查詢，文檔上關於 SQL/JSON path 內容很豐富，本文僅演示簡單的用例。
發行說明
Add support for the SQL/JSON path language (Nikita Glukhov, Teodor Sigaev, Alexander Korotkov, Oleg Bartunov, Liudmila Mantrova)
This allows execution of complex queries on JSON values using an SQL-standard language.
SQL/JSON Path Expressions 語法
SQL/JSON path 特性的核心是定義函數表達式，其實現方式是使用了 jsonpath 數據類型，jsonpath 以二進制格式展現 SQL/JSON 函數表達式。
SQL/JSON path 函數表達式使用了 JavaScript 的一些語法，如下:
l 點號 . 表示引用 Json 數據的元素
l 方括號 [] 表示引用數組元素
l Json 數據中的數組元素下標從0開始
SQL/JSON path 函數表達式的變量，如下:
l $ 符號表示要查詢的Json文本的變量
l $varname 表示指定變量
l @ 指在 filter 表達式中表示當前路徑元素的變量
SQL/JSON Path Expressions 基本演示
為了方便演示，創建以下測試表並插入一條 Json 測試數據，如下:
CREATE TABLE t_track ( a jsonb); INSERT INTO t_track (a) VALUES (' { "gpsname": "gps1", "track" : { "segments" : [ { "location": [ 47.763, 13.4034 ], "start time": "2018-10-14 10:05:14", "HR": 73 }, { "location": [ 47.706, 13.2635 ], "start time": "2018-10-14 10:39:21", "HR": 130 } ] } }'); SELECT jsonb_pretty(a) FROM t_track; jsonb_pretty ----------------------------------------------------- { + "track": { + "segments": [ + { + "HR": 73, + "location": [ + 47.763, + 13.4034 + ], + "start time": "2018-10-14 10:05:14"+ }, + { + "HR": 130, + "location": [ + 47.706, + 13.2635 + ], + "start time": "2018-10-14 10:39:21"+ } + ] + }, + "gpsname": "gps1" + } (1 row)
11 版本可以通過操作符號查詢 Json 數據元素值，如下:
mydb=> SELECT a ->> 'gpsname' FROM t_track ; ?column? ---------- gps1 (1 row)
12 版本可以使用 SQL/JSON path 函數表達式查詢，如下:
mydb=> SELECT jsonb_path_query(a,'$.gpsname') FROM t_track ; jsonb_path_query ------------------ "gps1" (1 row)
以上使用了 jsonb_path_query() 函數，這個函數是 SQL/JSON Path 的常用函數。
若 Json 數據比較複雜，涉及較多層級，這時 SQL/JSON path 函數表達式發揮優勢，比如查詢表 t_track 的 track.segments 元素，如下:
mydb=> SELECT jsonb_pretty(jsonb_path_query(a,'$.track.segments')) FROM t_track ; jsonb_pretty --------------------------------------------- [ + { + "HR": 73, + "location": [ + 47.763, + 13.4034 + ], + "start time": "2018-10-14 10:05:14"+ }, + { + "HR": 130, + "location": [ + 47.706, + 13.2635 + ], + "start time": "2018-10-14 10:39:21"+ } + ] (1 row)
track.segments 是個數組，可以通過方括號[]查詢相應數組元素，如下:
mydb=> SELECT jsonb_pretty(jsonb_path_query(a,'$.track.segments[0]')) FROM t_track ; jsonb_pretty ----------------------------------------- { + "HR": 73, + "location": [ + 47.763, + 13.4034 + ], + "start time": "2018-10-14 10:05:14"+ } (1 row) mydb=> SELECT jsonb_pretty(jsonb_path_query(a,'$.track.segments[1]')) FROM t_track ; jsonb_pretty ----------------------------------------- { + "HR": 130, + "location": [ + 47.706, + 13.2635 + ], + "start time": "2018-10-14 10:39:21"+ } (1 row)
查詢下一層級的元素，如下
mydb=> SELECT jsonb_path_query(a,'$.track.segments[1].HR') FROM t_track ; jsonb_path_query ------------------ 130 (1 row)
SQL/JSON Path Expressions 過濾演示
使用 SQL/JSON path 函數表達式查詢 Json 數據時，可以指定 filter 條件查詢滿足條件的 Json 元素，例如查詢 HR 元素值大於 100 的 track.segments 元素，如下:
mydb=> SELECT jsonb_pretty(jsonb_path_query(a,'$.track.segments ? ( @.HR > 100)')) FROM t_track ; jsonb_pretty ----------------------------------------- { + "HR": 130, + "location": [ + 47.706, + 13.2635 + ], + "start time": "2018-10-14 10:39:21"+ } (1 row)
若只想顯示指定元素信息，例如僅顯示 "start time" 元素，如下:
mydb=> SELECT jsonb_pretty(jsonb_path_query(a,'$.track.segments ? ( @.HR > 100)."start time"')) FROM t_track ; jsonb_pretty ----------------------- "2018-10-14 10:39:21" (1 row)
可以指定多個過濾條件，如下:
mydb=> SELECT jsonb_pretty(jsonb_path_query(a,'$.track.segments ? ( @.HR > 100) ? ( @.location[*] < 40)')) FROM t_track ; jsonb_pretty ----------------------------------------- { + "HR": 130, + "location": [ + 47.706, + 13.2635 + ], + "start time": "2018-10-14 10:39:21"+ } (1 row
jsonb_path_exists() 函數
jsonb_path_exists() 函數判斷是否存在指定 Json 路徑，語法如下:
jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb, silent bool])
一個簡單示例，如下:
mydb=> SELECT jsonb_path_exists(a,'$.track.segments.HR') FROM t_track ; jsonb_path_exists ------------------- t (1 row) mydb=> SELECT jsonb_path_exists(a,'$.track.segments.ab') FROM t_track ; jsonb_path_exists ------------------- f (1 row)
總結
本文簡單演示了 SQL/JSON path 特性的簡單用例，關於這塊詳細的介紹可參考手冊。
參考
https://paquier.xyz/postgresql-2/postgres-12-jsonpath/
https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH
https://www.postgresql.org/docs/12/datatype-json.html#DATATYPE-JSONPATH
https://postgres.fun/20190724143200.html
