下表列出了常用的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数据有效果,反之亦然。