PostgreSQL中基于自身值更新jsonb内嵌套计数器的方法

现有示例表myteble，结构如下：

create table myteble  (
    id   serial primary key ,
    metadata  jsonb
--   other fields
--   ......
)

其中metadata为jsonb类型列，存储半结构化数据。需求是执行更新操作时，在该列顶层创建{api_cnt: {'some_api_name': 1, 'other_api_name': 2, ...}}这样的嵌套结构，并实现计数器自增（比如1→2、2→3），且api_cnt或其嵌套键（如some_api_name）可能事先不存在。

目前已写出的最接近的SQL语句是：

update myteble
set metadata = jsonb_set(metadata, '{api_cnt}', '2', true)
where id = 1;

但不知道如何创建api_cnt -> some_api_name的嵌套记录，也不知道如何基于字段原有值实现自增（新值=旧值+1）。

示例场景 #

示例1：键事先不存在的情况 #

-- 初始状态
id=1, metadata = {'inner something': [1,2,3,4,5]}
-- 执行更新后期望结果
metadata = {'inner something': [1,2,3,4,5], 'api_cnt': {'some_api_name': 1, 'other_api_name': 1}}

示例2：键已存在的情况 #

-- 初始状态
id=1, metadata = {'inner something': [1,2,3,4,5], 'api_cnt': {'some_api_name': 1, 'other_api_name': 8}}
-- 执行更新后期望结果
metadata = {'inner something': [1,2,3,4,5], 'api_cnt': {'some_api_name': 2, 'other_api_name': 9}}

要实现嵌套JSON键的自增，需要结合jsonb_set和coalesce处理键不存在的默认值，以下是几种可行的写法：

单API键自增 #

如果只需要对单个API计数器（比如some_api_name）更新：

UPDATE myteble
SET metadata = jsonb_set(
    COALESCE(metadata, '{}'::jsonb),
    '{api_cnt, some_api_name}',
    (COALESCE(metadata -> 'api_cnt' ->> 'some_api_name', '0')::int + 1)::text::jsonb,
    true
)
WHERE id = 1;

• COALESCE(metadata, '{}'::jsonb)：避免metadata为null时出错，确保基础JSON对象存在。
• COALESCE(metadata -> 'api_cnt' ->> 'some_api_name', '0')：如果目标键不存在，默认取0，转成整数加1后再转回jsonb类型。
• 最后一个true参数允许自动创建不存在的路径（比如api_cnt或some_api_name）。

多个API键同时自增 #

如果要同时更新多个计数器（比如some_api_name和other_api_name），可以嵌套使用jsonb_set：

UPDATE myteble
SET metadata = jsonb_set(
    jsonb_set(
        COALESCE(metadata, '{}'::jsonb),
        '{api_cnt, some_api_name}',
        (COALESCE(metadata -> 'api_cnt' ->> 'some_api_name', '0')::int + 1)::text::jsonb,
        true
    ),
    '{api_cnt, other_api_name}',
    (COALESCE(metadata -> 'api_cnt' ->> 'other_api_name', '0')::int + 1)::text::jsonb,
    true
)
WHERE id = 1;

这个语句会依次处理每个嵌套键，无论键是否预先存在，都能完成自增操作。

简化合并写法 #

利用PostgreSQL的jsonb合并操作符||，可以更简洁地实现多键更新：

UPDATE myteble
SET metadata = metadata || jsonb_build_object(
    'api_cnt',
    COALESCE(metadata -> 'api_cnt', '{}'::jsonb) || jsonb_build_object(
        'some_api_name', (COALESCE(metadata -> 'api_cnt' ->> 'some_api_name', '0')::int + 1),
        'other_api_name', (COALESCE(metadata -> 'api_cnt' ->> 'other_api_name', '0')::int + 1)
    )
)
WHERE id = 1;

• ||操作符会合并两个jsonb对象，新对象的键会覆盖旧对象的同名键，同时保留未修改的键。
• 先构建更新后的api_cnt子对象，再合并到原metadata中，实现批量更新。

内容的提问来源于stack exchange，提问作者Aleksei Khatkevich