PostgreSQL通过SQL创建或更新JSON

PostgreSQL通过SQL更新JSON

PostgreSQL支持json字段,json字段分为两种类型:json和jsonb,如果是新建系统,建议使用较新的jsonb类型.

在PostgreSQL中创建和删除jsonb字段都很方便,但是更新比较麻烦，一般采用导出至应用程序更新的方式，但是这种更新方式效率不如在数据库内部完成好。

例如我们需要更新的原始jsonb为:

{"职务":"程序员","个人简历":"2020年在某公司工作","年龄":23}

因为能力较强升职为项目经理,现在直接在数据库内部实现更新json的职务信息,其它不变.

1 首先创建和json结构体相同的类型

类型字段那怕原始JSON数据中没有也可以定义,充分考虑扩展的需要多定义一些字段,它不会影响现有数据和系统的运行.
例如下面的"入职时间"在存储的json中假设是没有的,保留是为了将来扩展需要.将来如要添加字段只需要修改类型和函数后重新创建即可.

drop type if exists json_userinfo_columns;
create type json_userinfo_columns as (职务 text,个人简历 text,年龄 integer,入职时间 Date);  

2 通过sql更新职务信息

with cte as(
    select 个人简历,年龄,入职时间 from json_populate_record(null::json_userinfo_columns,'{"职务":"程序员","个人简历":"2020年在某公司工作"}')
) select row_to_json(row('项目经理',个人简历,年龄,入职时间)::json_userinfo_columns) from cte;

3 通过自定义函数更新

函数的参数顺序参考自定义类型,其中最后一个参数jsonb类型为待更新的原始jsonb数据.

drop function if exists changeUserInfo(text,text,integer,Date,jsonb);
create or replace function changeUserInfo(text,text,integer,Date,jsonb)
	returns jsonb
as $$
	with cte as(
		select 职务,个人简历,年龄,入职时间 from jsonb_populate_record(null::json_userinfo_columns,$5)
	) select row_to_json(row(
		coalesce($1 ,职务),
		coalesce($2 ,个人简历),
		coalesce($3 ,年龄),
		coalesce($4 ,入职时间)
	)::json_userinfo_columns)::jsonb from cte;
$$ language sql;

3.1 创建json

直接创建一个新的jsonb,如不需要设置的字段直接传入参数设置为null,否则传入的值.

select changeUserInfo('项目经理'::text,null::text,24,'2020-01-01'::date,'{}'::jsonb);

3.2 修改json内容

可以局部修改或全部修改json内容,如果不需要修改json内容,则传入参数设置为null,否则对应的字段将使用传入的值

select changeUserInfo('项目经理'::text,null::text,24,'2020-01-01'::date,'{"职务":"程序员","个人简历":"2020年在某公司工作","年龄":23}'::jsonb);

3.3 直接修改表中的数据

将要修改的项目设置为非null即可,例如下面只修改职务,其它信息不变保持原来的.

update tab set jsonfield=changeUserInfo('项目经理'::text,null::text,null::integer,null::date,coalesce(jsonfield,'{}'::jsonb)) where objectid=1;