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
    评论
### 回答1: 要将 JSONB 字段的字符串类型转换为数组类型,您可以使用 PostgreSQLJSONB 函数。具体来说,您可以使用 JSONB_ARRAY_ELEMENTS 函数将 JSONB 字段的值转换为数组,然后使用 CAST 函数将数组元素转换为字符串类型。以下是示例代码: ```sql SELECT CAST(jsonb_array_elements('["foo", "bar", "baz"]') AS TEXT[]); ``` 在上面的代码中,我们将 JSONB 字符串 `["foo", "bar", "baz"]` 转换为数组类型,并使用 CAST 函数将数组元素转换为字符串类型。此查询将返回一个包含字符串元素的数组。 如果您想从一个 JSONB 字段中提取数组,您可以将 JSONB 字段名替换为字段值,如下所示: ```sql SELECT CAST(jsonb_array_elements('["foo", "bar", "baz"]') AS TEXT[]); ``` 这将返回一个包含 JSONB 字段值中的字符串元素的数组。 ### 回答2: 在pgsql中,可以使用相应的SQL函数将jsonb字段中的字符串类型转换为数组类型。可以通过使用jsonb_array_elements_text()函数来实现。下面是一个示例: 假设我们有一个表格名为"my_table",其中有一个名为"my_jsonb_column"的jsonb类型字段,其值为['apple', 'banana', 'orange']的字符串。我们想要将该字符串转换为数组类型。 可以使用以下SQL语句实现该功能: SELECT jsonb_array_elements_text(my_jsonb_column) AS my_array FROM my_table; 运行该语句后,将返回一个包含"apple"、"banana"和"orange"的结果集,这就是将字符串转换为数组类型的过程。 需要注意的是,通过使用jsonb_array_elements_text()函数将字符串转换为数组类型时,字符串必须符合JSON数组格式。如果字符串不符合JSON数组格式,转换过程将会失败。 希望对你有所帮助! ### 回答3: 在pgsql中,要将JSONB字段的字符串类型转为数组类型,可以使用内置的函数jsonb_array_elements_text()。 这个函数将JSONB数组中的每个元素转换为文本类型,并返回一个结果集。首先,需要使用jsonb_typeof()函数检查JSONB字段类型是否为数组。接下来,可以在SELECT语句中使用jsonb_array_elements_text()函数来转换JSONB字段的值为数组类型的结果。 下面是一个示例: ``` SELECT jsonb_array_elements_text(jsonb_column) AS array_value FROM table_name WHERE jsonb_typeof(jsonb_column) = 'array'; ``` 在上面的示例中,jsonb_column是要读取的JSONB字段的名称,table_name是包含该字段的表名。 这个查询将返回JSONB字段的每个数组元素的值,转换为数组类型的结果。 需要注意的是,如果读取的JSONB字段不是数组类型,上述查询将不会返回任何结果。在应用上述查询时,需要确保只针对包含数组类型值的JSONB字段使用。 通过使用这种方法,可以方便地将JSONB字段的字符串类型转换为数组类型,以便后续的操作和使用。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值