PGSQL:对JSONB类型数组的对象字段批量修改

背景

在业务表中有一个ticket表,其中的数据结构如下:

CREATE TABLE ticket (
  id serail4 PRIMARY KEY,
  operation_log JSONB
);

其中operation_log字段的数据结构为:

[
    {
        "remark":null,
        "operator":141400,
        "createTime":1644980417927,
        "operatorName":null,
        "operatorType":null
    },
    {
        "remark":null,
        "operator":5407,
        "createTime":1650967180449,
        "operatorName":null,
        "operatorType":1
    },
    {
        "remark":null,
        "operator":5407,
        "createTime":1650967207341,
        "operatorName":null,
        "operatorType":1
    }
]

由于架构改造需要,需要将operatorType1的整型数据调整为负数形式,对ticket表中所有的符合该条件的数据批量修改。

解决方案

基于jsonb_set实现数组字段更新

由于jsonb_set方法一次只支持更新数组对象中的第一个,如果数组中存在多个的话无法全部更新,因此借助pgsql中的aggregate函数,使用分组方法实现多次更新。

基于jsonb_set创建自定义函数
CREATE OR REPLACE FUNCTION jsonb_set_custom(x jsonb, y jsonb, p text[], z jsonb, b boolean)
RETURNS jsonb LANGUAGE sql IMMUTABLE AS
$$ SELECT jsonb_set(COALESCE(x, y), p, z, b) ; $$ ;
创建jsonb_set_agg自定义聚合函数
CREATE AGGREGATE jsonb_set_agg(x jsonb, p text[], z jsonb, b boolean)
( SFUNC = jsonb_set_custom, STYPE = jsonb
);
查询中使用聚合函数jsonb_set_agg拼装结果
SELECT
		ID AS sub_id,
		jsonb_set_agg ( operation_log, ARRAY [ (pos - 1) :: TEXT, 'operator'], ((((elem -> 'operator') :: text)::int * -1)::text)::jsonb ,false) val
	FROM
		ticket,
		jsonb_array_elements ( operation_log ) WITH ORDINALITY arr ( elem, pos )
	WHERE
		elem ->> 'operatorType' = '1'
		AND elem ->> 'operator' IS NOT NULL
		AND elem ->> 'operator' != 'null' group by sub_id
update覆盖数据库字段
-- 更新操作日志脚本
with sub as (SELECT
		ID AS sub_id,
		jsonb_set_agg ( operation_log, ARRAY [ (pos - 1) :: TEXT, 'operator'], ((((elem -> 'operator') :: text)::int * -1)::text)::jsonb ,false) val
	FROM
		ticket,
		jsonb_array_elements ( operation_log ) WITH ORDINALITY arr ( elem, pos )
	WHERE
		elem ->> 'operatorType' = '1'
		AND elem ->> 'operator' IS NOT NULL
		AND elem ->> 'operator' != 'null' group by sub_id
	)
UPDATE ticket
SET operation_log = sub.val 
FROM sub
WHERE
	ID = sub.sub_id ; 
执行结果
[
    {
        "remark":null,
        "operator":141400,
        "createTime":1644980417927,
        "operatorName":null,
        "operatorType":null
    },
    {
        "remark":null,
        "operator":-5407,
        "createTime":1650967180449,
        "operatorName":null,
        "operatorType":1
    },
    {
        "remark":null,
        "operator":-5407,
        "createTime":1650967207341,
        "operatorName":null,
        "operatorType":1
    }
]

写在最后

关于PGSQL的更多关于函数相关文章,请关注我后续的更新。

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值