什么是JSON PATH
在一般的编程语言中,JSON对象中,深层次的对象和属性的访问,使用一种链式标识的方式,例如对于下面这个对象(来自PG官方技术文档):
js
代码解读
复制代码
{ "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": 135 } ] } }
在JS语言中,如果要访问其中的一个属性,可以使用下面的方法: track.segements[0].HR。但在Postgres的SQL语言中,由于语法的限制,可能需要使用"->"引用操作符,也是链式访问,如下:
sql
代码解读
复制代码
with D (data) as (values ('{ "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": 135 } ] } }')) select D.data::jsonb->'track'->'segments'->0->>'HR' from D; ?column? ---------- 73 (1 row)
可能觉得这种写法过于繁复,PG的设计者提出了一种“路径”查询的方式,就是通过一种形式,可以定义一个寻址的路径,然后基于这个路径来解析和访问JSON对象,得到需要的结果。 这个路径,可以简单的使用一个字符串来进行表达,容易构造和使用。
这个路径,就是所谓的JSON PATH,而用来表示和描述这个路径所使用的语言,就是规范化的字符串,则被称为 SQL/JSON Path Language,即JSON路径语言。
SQL/JSON PATH语言如何使用
还是使用上面那个例子,如果使用路径语言来实现,可以使用下面的方式:
sql
代码解读
复制代码
-- 查询属性 select jsonb_path_query(data::jsonb, '$.track.segments') from D; jsonb_path_query ------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}] (1 row) -- 指定位置和属性 select jsonb_path_query(data::jsonb, '$.track.segments[0].location') from D; jsonb_path_query ------------------- [47.763, 13.4034] (1 row) -- 通配符 select jsonb_path_query(data::jsonb, '$.track.segments[*].location') from D; jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635] (2 rows) -- 不存在的路径 select jsonb_path_query(data::jsonb, '$.track.segments[3].location') from D; jsonb_path_query ------------------ (0 rows) -- 条件检查 select jsonb_path_query(data::jsonb, '$.track.segments[*] ? (@.HR > 130)."start time"') from D; jsonb_path_query ----------------------- "2018-10-14 10:39:21" (1 row)
从这些例子中,我们可以看到,JSON Path一般结合匹配的函数或者操作符使用,这时可以将其看成简单的字符串参数。然后由数据库引擎来解析这个字符串,并进行相关的处理。
JSON Path的主要语法规则是什么样的
根据对上面例子和技术资料的解读,笔者总结了一下JSON Path的主要语法规则
- 所有JSON Path,都是一个字符串,使用''包围起来
- 一般使用$开始,代表当前处理的JSON对象
- 类似于JS的语法,使用.和中括号,结合属性名称来表示寻址路径
- 寻址的次序,从左到右进行解析
- 属性名称中有空格的,可以使用双引号包围
- 对于数组,可以使用通配符 *,或者具体数字
- 可以使用条件语句,来对属性进行过滤
- 条件语句中,使用@表示当前引用的属性值,并使用 ? 作为操作符
PG中关于JSON PATH有那些相关的功能和函数
在JSON章节中,我们已经提到,下面由两个操作符,需要配合JSON Path进行使用:
- jsonb @? jsonpath → boolean
用于检查是否有匹配JSON Path的项目(可以返回记录)。
- jsonb @@ jsonpath → boolean
对JSON对象,使用JSON Path进行谓词检查,只计算第一个匹配项目。和前面项目的区别是,需要使用一个检查机制,如指定一个条件,来对枚举的项目进行检查,而前者则是通过返回的记录来确定。需要注意,如果检查的项目本身为null,则会返回null,而不是布尔值。
PG中,还有有一类使用json_path开头的函数,一般都和JSON Path语言的应用相关,例如:
- jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
这个方法非常基本,就是用于检查在JSON对象中,路径查询的结果是否存在。在路径字符串中,还可以定义参数,并可以使用在vars对象中的值来替换(也就是说path也可以是参数化的);silent参数为true时,函数将抑制与@?和@@运算符相同的错误(这个设置,笔者没有完全理解,大致是条件抛出错误而非返回空值的意思)。
- jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb
这个方法是实际应用中最常见的,就是使用路径进行查询并获得匹配的结果。下面有一个简单的例子:
sql
代码解读
复制代码
select id, jsonb_path_exists(data::jsonb, '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') j1, jsonb_path_query(data::jsonb, '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":5}') j2 from lateral ( values (1001,'{"a":[1,2,3,4,5]}'),(1002,'{"a":[5,6,7,8]}')) as D(id,data); id | j1 | j2 ------+----+---- 1001 | t | 2 1001 | t | 3 1001 | t | 4 1001 | t | 5 1002 | f | 5 (5 rows)
从这个例子我们可以看到:
exists,返回的是一个布尔值;query,根据查询路径不同,可能返回的结果也有差异,例子中返回的是一个匹配条件的记录集;例子中使用了一个参数化的路径,并传输了要使用的参数对象;参数化查询,大大提高了JSON路径查询构建和执行的灵活性
- jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
需要使用一个检查性的JSON Path语句,返回匹配检查的结果。例如:
jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists(.a[∗]?(@>=.a[*] ? (@ >= .a[∗]?(@>=min && @ <= $max))', '{"min":2, "max":4}') → t
注意其中的exists判断,笔者没有找到其他的例子和写法。在这一点,math好像和exists是一样的。
- jsonb_path_query_array, jsonb_path_query_first
这两个函数和path_query类似,只不过如果是数组的话,它会返回一个数组而不是记录集;query_first显然就是返回数组中第一个值(不是数��,也不是记录集)。
- jsonb_path_exists_tz,jsonb_path_match_tz,jsonb_path_query_tz,jsonb_path_query_array_tz,jsonb_path_query_first_tz
这里的tz,是TimeZone(时区的意思)。也就是说,这些方法的基本功能都是和前面的方法差不多的,但如果包括时间相关处理的话,都要考虑时区的信息。
小结
本文讨论了Postgres JSON功能的一个扩展性的特性,就是JSON Path。表述了笔者对其的理解,应用的方式和场合,以及Postgres中,相关的操作符和函数,和使用方式等等内容。