初探postgresql-json数据格式

406 阅读4分钟

持续创作,加速成长!这是我参与「掘金日新计划 · 6 月更文挑战」的第6天,点击查看活动详情

一、背景

你是否对postgresql数据库的字段类型有所涉猎?

你是否对json数据库字段类型保持好奇?

那么我们一起看看postgresql对json字段类型的支持,我们会对下面几个问题保持关注

1、如何使用json数据类型?建表、增删改查等场景又是如何操作的?

2、应用场景是什么?

3、在编程的世界里:对比是一直存在的,json数据和text字段对比有何优势?

二、应用场景

很多时候我们需要在数据库中持久化一个对象,其有很多属性,我们是如何做的?

方式一:字符串存储

  • 1、数据库建模字段类型:text。
  • 2、将对象序列化成字符串,入库
  • 3、查询时再反序列化成对象

思考:这种操作可以满足我们的需求,带来了何种问题?

  • 1、有两个冗余步骤:入库前的序列化、查询数据后的反序列化。这带来了效率问题并且对数据库带来了冲击。
  • 2、这种数据的更新操作:需要先执行一次查询,然后反序列化,然后修改对象后,序列化成字符串、入库。这个时候又增加了很多额外的性能开销:1)多一步查询、同时伴随着序列化、反序列化

方式二:对象的属性平铺

将对象的属性平铺,每个字段一个数据库属性分别存储,这样就不会有冗余操作,然后其对下述场景仍然显得繁琐

  • 1、对象的属性很多——数据库会有很多字段
  • 2、对象嵌套——处理起来繁琐
  • 3、对象属性动态变化——某一天突然增加很多属性,这涉及到数据库表结构升级

方式三:json数据类型

  • 1、数据库建模字段类型:json
  • 2、json数据格式的好处

​ 1)如果想新增对象属性,直接通过update语句实施(不需要查询->修改->入库)

​ 2)如果想修改对象属性的值,直接通过update语句实施(不需要查询->修改->入库)

三、json数据类型操作

1、建表语句

create table if not exists json_study(id varchar(128) not null,somex json,primary key(id));

2、插入操作

insert into json_study (id,somex) values ('ne1','{"parent=1,Ip=1":"127.0.0.1","parent=1,Ip=2":"127.0.0.2"}');insert into json_study (id,somex) values ('ne2','{"parent=1,Ip=1":"127.0.0.1","parent=1,Ip=2":"127.0.0.2"}');insert into json_study (id,somex) values ('ne3','{"parent=1,Ip=1":"127.0.0.1"}');
![image.png](https://dev.zte.com.cn/upload/editor/20210903/1630651487909046843.png)

3、修改操作:修改json属性

update json_study set somex=somex::jsonb || '{"parent=1,Ip=1":"127.0.0.2"}'::jsonb where id='ne1'; 修改id=ne1记录,parent=1,Ip=1127.0.0.1修改为127.0.0.2

image.png

4、修改操作:增加json属性

update json_study set somex=somex::jsonb || '{"parent=1,Ip=3":"127.0.0.1"}'::jsonb where id='ne1'; 修改id=ne1记录,新增属性parent=1,Ip=3

image.png

5、修改属性:覆盖属性

update json_study set somex= '{"parent=1,Ip=1":"127.0.0.1"}'::jsonb where id='ne1';ne1-somex字段只保留parent=1,Ip=1一个属性

image.png

6、查询json指定属性的值

select id,somex->>'parent=1,Ip=1' from json_study where id='ne1';

image.png

7、模糊匹配json字段value:查询数量

select count(*) from json_study where cast("somex" as varchar) like '%"127.0.0.1"%' and id != 'ne1';

image.png

8、模糊匹配json字段value:有无多条记录

select 1=1 from json_study where cast("somex" as varchar) like '%"127.0.0.1"%' and id != 'ne1' limit 2;

image.png

注:当过滤数据,查看满足条件的记录有无多条记录时,推荐limit而不是count。这是因为,limit效率高于count,并且随着数据规模的增大,效率也会增大

四、场景实例化(kafka监听为例)

1、kafka监听场景

1)如果监听到增加json属性操作->update语句(不存在则插入)

2)如果监听到修改json属性操作->update操作(直接修改)

3)如果监听到删除json属性操作->update操作(小技巧:置空,当然也可以通过alert语句完成或者查询修改再入库)

优势:只有一条语句,无附加语句(查询、序列化、反序列化等)并且所有的操作sql也只有一条语句

4)需要监听id增加事件(否则上述json属性增加操作会报错)

5)增加监听id删除事件(清理冗余数据)

2、数据同步场景

由于某些原因,数据发送进程因重启或者异常导致消息没有发送成功。导致数据不一致。

这个时候,可以直接用update的覆盖模式完成修改,不用查询后对比再入库,十分便捷

五、思考

上述例子、id和属性事件有前后,kafka监听处理事件交给线程池。线程池调度顺序无法保证,有可能遇到id记录未插入、然后更新属性的场景。这种情形需要容错处理或者从sql语句上处理(监听到增加属性的sql语句改为:insert语句遇到主键冲突则更新)