KingbaseES json操作符

下表列出了常用的json数据类型操作符：

操作符	操作符右侧数据类型	返回类型	描述
->	int	json or jsonb	获得 JSON 数组元素(索引从 0 开始，负整数从末尾开始计)
->	text	json or jsonb	通过键获得 JSON 对象域
->>	int	text	以text形式获得 JSON 数组元素
->>	text	text	以text形式获得 JSON 对象域
#>	text[]	json or jsonb	获取在指定路径的 JSON 对象
#>>	text[]	text	以text形式获取在指定路径的 JSON 对象
构建测试数据

CREATE TABLE student (stu_id serial NOT NULL PRIMARY KEY,stu_info json NOT NULL);
INSERT INTO student (stu_info)
VALUES
(
'{
"name": "lisi",
"information":
{
"mobile_number": "13700000001",
"branch": "Computer",
"rank":12
}
}'
),
(
'{
"name": "zhangsan",
"information":
{
"mobile_number": "13700000002",
"branch": "Computer",
"rank":1
}
}'
),
(
'{
"name": "zhouxinxin",
"information":
{
"mobile_number": "13700000003",
"branch": "Car",
"rank":2
}
}'
),
(
'{
"name": "lilei",
"information":
{
"mobile_number": "13700000004",
"branch": "Civil",
"rank":6
}
}'
);
INSERT INTO student (stu_info)  --数组类型的json
VALUES
(
'[{
"name": "wanwu"},
{"information":
{
"mobile_number": "13700000005",
"branch": "Computer",
"rank":11
}
}]'
)

1.使用索引来获取学生名字(返回的json类型的数据)

test=# SELECT stu_info ->0 AS StudentName FROM student;                                                           
   studentname    
------------------
 
 
 
 
 {               +
 "name": "wanwu"}
(5 行记录)
test=# SELECT stu_info ->0 ->'name' AS StudentName FROM student;
 studentname 
-------------
 
 
 
 
 "wanwu"
(5 行记录)

2.使用json键来获取学生名字(返回的json类型的数据)

test=# SELECT stu_info -> 'name' AS StudentName FROM student;
 studentname  
--------------
 "lisi"
 "zhangsan"
 "zhouxinxin"
 "lilei"
 
(5 行记录)

3.使用json键来获取学生名字(返回的字符串类型的数据)

test=# SELECT stu_info ->> 'name' AS StudentName FROM student;
 studentname 
-------------
 lisi
 zhangsan
 zhouxinxin
 lilei
 
(5 行记录)

4.获取学生的手机号码

test=# SELECT stu_info #>> '{information,mobile_number}' AS phone FROM student;
    phone    
-------------
 13700000001
 13700000002
 13700000003
 13700000004
 
(5 行记录)

5.在where条件中使用json操作符

test=# SELECT stu_info ->> 'name' AS StudentName FROM student WHERE stu_info -> 'information' ->> 'branch' = 'Computer';
 studentname 
-------------
 lisi
 zhangsan
(2 行记录)
test=# SELECT stu_info ->> 'name' AS StudentName FROM student WHERE stu_info #>> '{information,branch}' = 'Computer';
 studentname 
-------------
 lisi
 zhangsan
(2 行记录)

‘>>’ 操作符返回的是文本类型的数据，‘>’ 操作符返回的是json/jsonb类型的数据。
操作符右侧使用数字则只对数组类型的json数据有效果，反之亦然。