PostgreSQL JSON类型字段常用操作

初始数据

insert into name_age values('{"id":1,"name":"小明", "age":18}');
insert into name_age values('{"id":2,"name":"小明1", "age":17}');
insert into name_age values('{"id":3,"name":"小明3", "age":16}');
insert into name_age values('{"id":4,"name":"小明4", "age":20}');
insert into name_age values('{"id":5,"name":"小明5", "age":30}');
insert into name_age values('{"id":6,"name":"小明6", "age":10}');
insert into name_age values('{"id":7,"name":"小明7", "age":15}');
insert into name_age values('{"id":8,"name":"小明8", "age":35}');
insert into name_age values('{"id":9,"name":"小明9", "age":35}');

Untitled

Where

精确匹配

SELECT info||'{"gender":"男"}' as a,info||'{"gender":"男"}'::jsonb as b,info as c from name_age where (info->>'id')::int4 = 2;

Untitled

---

SELECT info||'{"gender":"男"}' as a,info||'{"gender":"男"}'::jsonb as b,info as c from name_age where (info->>'name') = '小明4';

Untitled

模糊匹配

select * from name_age where info #>> '{name}' like '%明';

Untitled

---

select * from name_age where (info->>'name') like '%4';

Untitled

排序

select * from name_age order by info->'age' desc,info->'id' asc;
select * from name_age order by info->>'age' desc,info->'id' asc;
select * from name_age order by info->>'age' desc,info->>'id' asc;

Untitled

修改

增加json字段

update name_age set info = info || '{"gender":"男"}'::jsonb  where (info->>'id')::int4 = 1;

Untitled

Untitled

删除json字段

update name_age set info = info-'gender' where  (info->>'id')::int4 = 1;

Untitled

Untitled

修改json字段值

update name_age set info = info || '{"age":16}'::jsonb  where (info->>'id')::int4 = 1;

Untitled

Untitled

分页

select * from name_age order by info->>'age' desc,info->>'id' asc  limit 2 offset 1;

• offset 从0开始

Untitled

其他内容

1. 统计表占用空间

   SELECT table_schema || '.' || table_name                                         AS table_full_name,
          pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') AS size
   FROM information_schema.tables
   ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC DESC
   

2. 筛选某个字段包含%的数据

   select * from xxxxx where aaa like '%\%%' ESCAPE '\';
   
   或者 
   
   select * from xxxxx where aaa like '%a%%' ESCAPE 'a';
   

系统推荐

• getPath vs getAbsolutePath vs getCanonicalPath
• 来个JVM冷知识
• 数组转树形结构只需两步
• K8S 无感发布
• InnoDB存储引擎
• Arthas使用记录
• Git Merge 、Rebase
• SQL优化
• vuepress-theme-hope使用心得
• Java面试基础
• MySQL数据迁移到PGSQL
• Mermaid示例

---

---

• 随机毒鸡汤：我是一个很自律的人，我既然说了我要减肥，我就要天天说。