PostgreSQL 史上最强JSON功能 - PG 12 jsonpath 完全超越oracle, mysql, sql server的sql json标准覆盖率

作者

digoal

日期

2020-10-13

标签

PostgreSQL , jsonpath , sql json标准

背景

sql 2016的sql/json标准有15条， PG 12 实现了14条， 远远超过oracle(18c 11/15), mysql(8.0.4 5/15), sqlserver(2017 2/15)最新版本。

参考

https://2019.pgconf.eu/

https://www.postgresql.org/docs/13/datatype-json.html#DATATYPE-JSONPATH

http://www.postgres.cn/v2/news/viewone/1/499

https://postgres.fun/20190724143200.html

《2019 pg欧洲峰会oleg分享的 sql json 用法和roadmap》

相关函数:

``` postgres=> \df jsonpath* List of functions Schema | Name | Result data type | Argument data types | Type ------------+------------------------------+------------------+-------------------------------------------------------------------------------------------+------ pg_catalog | json_extract_path | json | from_json json, VARIADIC path_elems text[] | func pg_catalog | json_extract_path_text | text | from_json json, VARIADIC path_elems text[] | func pg_catalog | jsonb_delete_path | jsonb | jsonb, text[] | func pg_catalog | jsonb_extract_path | jsonb | from_json jsonb, VARIADIC path_elems text[] | func pg_catalog | jsonb_extract_path_text | text | from_json jsonb, VARIADIC path_elems text[] | func pg_catalog | jsonb_path_exists | boolean | target jsonb, path jsonpath, vars jsonb DEFAULT '{}'::jsonb, silent boolean DEFAULT false | func pg_catalog | jsonb_path_exists_opr | boolean | jsonb, jsonpath | func pg_catalog | jsonb_path_match | boolean | target jsonb, path jsonpath, vars jsonb DEFAULT '{}'::jsonb, silent boolean DEFAULT false | func pg_catalog | jsonb_path_match_opr | boolean | jsonb, jsonpath | func pg_catalog | jsonb_path_query | SETOF jsonb | target jsonb, path jsonpath, vars jsonb DEFAULT '{}'::jsonb, silent boolean DEFAULT false | func pg_catalog | jsonb_path_query_array | jsonb | target jsonb, path jsonpath, vars jsonb DEFAULT '{}'::jsonb, silent boolean DEFAULT false | func pg_catalog | jsonb_path_query_first | jsonb | target jsonb, path jsonpath, vars jsonb DEFAULT '{}'::jsonb, silent boolean DEFAULT false | func

postgres=> \df .prett* List of functions Schema | Name | Result data type | Argument data types | Type ------------+----------------+------------------+---------------------+------ pg_catalog | jsonb_pretty | text | jsonb | func ```

``` 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) ```

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.