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
