PostgreSQL 12 新特性解读之一|支持 SQL/JSON path __PostgreSQL 之前版本已支持 Json 和 Jsonb 数据类型，支持非关系数据的存储和检索，如果 Json 数据较复杂(层级多、嵌套json、包含数组等 )，之前版本不能方便的检索 Json 数据元素值。 PostgreSQL 12 版本的一个重量级特性是新增 SQL/JSON path 特性，支持基于 Json 元素的复杂查询，文档上关于 SQL/JSON path 内容很丰富，本文仅演示简单的用例。_

PG中文社区 / PostgreSQL 12 新特性解读之一|支持 SQL

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

PostgreSQL 12 新特性解读之一|支持 SQL/JSON path

原作者：谭峰　　创作时间：2019-08-13 16:04:02+08　　

wangliyun 发布于2019-08-14 08:04:02　　

评论: 1　　

顶: 520　

踩: 554　

### **作者介绍**
#### 谭峰，网名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 的一些语法，如下:
#### 点号 . 表示引用 Json 数据的元素
#### 方括号 [] 表示引用数组元素
#### Json 数据中的数组元素下标从0开始

#### SQL/JSON path 函数表达式的变量，如下:
#### $  符号表示要查询的Json文本的变量
#### $varname  表示指定变量
#### @  指在 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

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)

评论:1　　

顶: 520　

踩: 554　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-23 14:51:49+08

展望未来的句子:https://www.nanss.com/yulu/4846.html 为情所伤的id:https://www.nanss.com/mingcheng/5634.html 关于劳动的优美句子:https://www.nanss.com/xuexi/5929.html 运动员激励语10个字:https://www.nanss.com/xuexi/5526.html 致敬科比的句子简短:https://www.nanss.com/yulu/5050.html 表达夜景很美的一句话:https://www.nanss.com/xuexi/5770.html 既高傲又冷漠的昵称:https://www.nanss.com/yulu/5848.html 美文美句:https://www.nanss.com/xuexi/5142.html 产假申请:https://www.nanss.com/gongzuo/5748.html 统一回复大家对孩子的生日祝福:https://www.nanss.com/wenan/5622.html 搞笑的网名:https://www.nanss.com/mingcheng/5556.html 节约用水的优美段落:https://www.nanss.com/xuexi/5807.html 收到红包幽默感谢语:https://www.nanss.com/yulu/5278.html 适合吃榴莲发朋友圈的句子:https://www.nanss.com/wenan/5736.html 游戏名字大全霸气的:https://www.nanss.com/mingcheng/5427.html 倩女幽魂爆鬼最多名字:https://www.nanss.com/mingcheng/5577.html 有文化内涵又高雅的祝福语:https://www.nanss.com/yulu/5559.html 高情商晚上好问候方式:https://www.nanss.com/yulu/5576.html 考研座右铭:https://www.nanss.com/xuexi/5917.html 佛语签名:https://www.nanss.com/wenan/5506.html 春暖花开的季节的说说:https://www.nanss.com/wenan/4053.html 如果就造句二年级:https://www.nanss.com/xuexi/5181.html 心态好格局大的句子:https://www.nanss.com/yulu/5552.html 最凉不过人心经典语录:https://www.nanss.com/yulu/5957.html 离婚后释然的句子:https://www.nanss.com/wenan/5623.html lol名字能用的符号:https://www.nanss.com/mingcheng/5968.html 文案短句温柔:https://www.nanss.com/wenan/4241.html 心得体会开头:https://www.nanss.com/xuexi/4193.html 我国最早的表演艺术:https://www.nanss.com/wenti/4560.html 求学之路的经典句子:https://www.nanss.com/xuexi/5954.html