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
    }
]

由于架构改造需要，需要将operatorType为1的整型数据调整为负数形式，对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的更多关于函数相关文章，请关注我后续的更新。