PostgreSQL 12: 支持 SQL/JSON path 特性

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 的一些语法,如下:

  • 点号 . 表示引用 Json 数据的元素
  • 方括号 [] 表示引用数组元素
  • Json 数据中的数组元素下标从0开始

SQL/JSON path 函数表达式的变量,如下:

  • $ 符号表示要查询的Json文本的变量
  • $varname 表示指定变量
  • @ 指在 filter 表达式中表示当前路径元素的变量

SQL/JSON Path Expressions 基本演示

为了方便演示,创建以下测试表并插入一条 Json 测试数据,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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 数据元素值,如下:

1
2
3
4
5
mydb=> SELECT a ->> 'gpsname' FROM t_track ;
?column?
----------
gps1
(1 row)

12 版本可以使用 SQL/JSON path 函数表达式查询,如下:

1
2
3
4
5
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 元素,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 是个数组,可以通过方括号[]查询相应数组元素,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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)

查询下一层级的元素,如下

1
2
3
4
5
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 元素,如下:

1
2
3
4
5
6
7
8
9
10
11
12
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” 元素,如下:

1
2
3
4
5
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)

可以指定多个过滤条件,如下:

1
2
3
4
5
6
7
8
9
10
11
12
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])

一个简单示例,如下:

1
2
3
4
5
6
7
8
9
10
11
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 特性的简单用例,关于这块更详细的介绍请参考手册 SQL/JSON Path Expressionsjsonpath Type

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%