Postgresql JSON操作符

龙小胖

已于 2024-10-15 18:11:38 修改

阅读量959

点赞数 9

文章标签： postgresql json 数据库

于 2024-10-12 15:27:58 首次发布

本文链接：https://blog.csdn.net/weixin_43706875/article/details/142878703

版权

postgresql json数据

参考文章：
JSON数据操作

操作符

1、-> 表示获取一个JSON数组元素，支持下标值(下标从0开始)、Key获取。
2、->> 表示获取一个JSON对象字符串。
3、#> 表示获取指定路径的一个JSON对象。
4、#>>表示获取指定路径的一个JSON对象的字符串。

1、版本

select version();

在这里插入图片描述

2、数据准备

创建学生表

CREATE TABLE students
(
 stu_id SERIAL primary key ,
 student_desc jsonb
);

-- 插入数据
INSERT INTO "students" ("stu_id", "student_desc") VALUES (1, '{"date": "2024-10-12", "sdata": {"sno": "1001", "tel": ["010-82886998", "13550011000"], "addr": ["北京市海淀区科学大厦", "海淀区中关村路2号"], "sage": "23", "ssex": "女"}, "sname": "李思佳", "courses": [{"cno": "01", "cname": "math", "teacher": {"tno": "101", "tname": "刘平安"}}, {"cno": "02", "cname": "chinese", "teacher": {"tno": "102", "tname": "李昌阳"}}, {"cno": "03", "cname": "english", "teacher": {"tno": "103", "tname": "Steven"}}]}');
INSERT INTO "students" ("stu_id", "student_desc") VALUES (2, '{"sc": [{"cno": "01", "score": "76"}, {"cno": "02", "score": "77"}, {"cno": "03", "score": "87"}], "date": "2024-10-12", "sdata": {"sno": "1004", "tel": ["021-2861789", "18211028796"], "addr": ["科技一路6号", "科技二路8号", "科技三路20号"], "sage": "20", "ssex": "女"}, "sname": "张成", "courses": [{"cno": "01", "cname": "math", "teacher": {"tno": "101", "tname": "刘平安"}}, {"cno": "02", "cname": "chinese", "teacher": {"tno": "102", "tname": "李昌阳"}}, {"cno": "03", "cname": "english", "teacher": {"tno": "103", "tname": "Steven"}}]}');

学生成绩表

CREATE TABLE sc (
  "sno" text COLLATE "pg_catalog"."default" NOT NULL,
  "cno" text COLLATE "pg_catalog"."default" NOT NULL,
  "score" text COLLATE "pg_catalog"."default",
  CONSTRAINT "sc_pkey" PRIMARY KEY ("sno", "cno")
);

-- 插入数据
insert into sc values('1001','01','99');
insert into sc values('1001','02','89');
insert into sc values('1001','03','77'); 
insert into sc values('1002','01','45');
insert into sc values('1002','02','78');
insert into sc values('1002','03','100'); 
insert into sc values('1003','01','90');
insert into sc values('1003','02','76');
insert into sc values('1003','03','87');

3、查询

3.1获取一个JSON对象字符串

-- ->>表示获取一个JSON对象字符串。
select student_desc ->> 'sdata' as stu_data,
student_desc ->> 'sname'as stu_name
from students where stu_id = 1

在这里插入图片描述

3.2获取一个JSON数组元素

-- -> 表示获取一个JSON数组元素，支持下标值(下标从0开始)、Key获取。
select student_desc->'sdata' as stu_data  from students;

在这里插入图片描述

3.3获取一个JSON对象内数据

-- 获取对象 -> 返回值带引号
select student_desc->'sdata'->'sage' as age  from students;

在这里插入图片描述

-- 获取值 ->> 直接返回值
select student_desc->'sdata'->>'sage' as age  from students;

在这里插入图片描述

3.4获取一个JSON对象内数组

-- 获取数组对象
select student_desc->'sdata'#>'{addr}' as addr  from students;

在这里插入图片描述

-- 获取数组对象值 指定下标
select student_desc->'sdata'#>>'{addr,1}' as addr  from students;

在这里插入图片描述

3.5 获取一个JSON数组对象

-- #> 表示获取指定路径的一个JSON对象
-- 获取整个数组对象
select student_desc #>'{courses}' as stu_courses from students;

在这里插入图片描述

-- 获取数组对象，指定下标

select student_desc #>'{courses,0}' as stu_courses from students;

在这里插入图片描述

3.6获取一个JSON数组内对象内的数据

-- 获取数组内对象
select student_desc #>'{courses,0}'->'teacher' as teacher from students;

在这里插入图片描述

-- 获取数组内对象中数据
select student_desc #>'{courses,0}'->'teacher'->>'tname' as stu_courses from students;

在这里插入图片描述

4、增改

新增一个属性

update students set student_desc=student_desc|| '{"nickname":"平安"}' 
where stu_id=1;

在这里插入图片描述

删除一个属性

update students set student_desc = student_desc - 'nickname' where stu_id=1;

删除所有

update students set student_desc = student_desc - 'sdata' where stu_id=1;

5、联合查询

表转JSON

-- 
select row_to_json(sc.*) from sc where sno='1001';

联表查询

-- 联表查询
select student_desc->>'sname' as stu_name,student_desc #>'{courses}' as stu_courses from sc 
join students s on  text(s.student_desc->'sdata'->>'sno')=sc.sno and sc.sno='1001' and sc.cno='01';

在这里插入图片描述

操作函数

1、组装对象：json_build_object

select json_build_object('fam_id',fam_id,'fam_name',fam_name,'stu_id',stu_id,'fam_phone',fam_phone) as fam_json
from family_info

在这里插入图片描述

2、组装数组：json_agg

-- 第一种
select json_agg(row_to_json(t))
from (
select fam_id,fam_name,stu_id,fam_phone from family_info 
)t

-- 第二种
select json_agg(
json_build_object('fam_id',fam_id,'fam_name',fam_name,'stu_id',stu_id,'fam_phone',fam_phone)
) as fam_json from family_info

在这里插入图片描述

3、json对象转行数据：json_to_record

从 JSON 对象构建任意记录

select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r json);

在这里插入图片描述

4、json数组转行(集合)数据

SELECT * FROM json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]') AS x(a INT, b json, c BOOLEAN);

在这里插入图片描述

SELECT * FROM json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') AS x(a INT, b text, c BOOLEAN);

在这里插入图片描述

5、从json对象中取指定值 json_extract_path

select json_extract_path('{"a": "1", "b": {"x": 2, "y": 3}}', 'a') as a;
-- 结果 "1"

select json_extract_path_text('{"a": "1", "b": {"x": 2, "y": 3}}', 'a') as a;
-- 结果 1
select json_extract_path('{"a": 1, "b": {"x": 2, "y": 3}}', 'b', 'x') as b;
-- 结果 2

6、json_array_elements

select * from json_array_elements('[{"fam_id":"11111","fam_name":"妈妈","fam_phone":"18269856548"},{"fam_id":"11111","fam_name":"妈妈","fam_phone":"18800000001"}]') as fam_data

在这里插入图片描述

联合使用

select ar.fam_phone
from (
	select string_agg(json_extract_path_text(fam_data,'fam_phone'),',') fam_phone
	from json_array_elements('[{"fam_id":"11111","fam_name":"妈妈","fam_phone":"18269856548"},{"fam_id":"11111","fam_name":"妈妈","fam_phone":"18800000001"}]') as fam_data
)ar
where ar.fam_phone like '%18800000001%'