Increment counter inside jsob based on itself value

Question

I have an example table to demonstate a problem.

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

metadata is a jsonb column, and it might and most likely will contain some portion of semi-arbitrary data. What I want to do during update, create inside top level of this column data like {api_cnt: {'some_api_name': 1, 'other_api_name': 2, ...} and increment this counter (+1) on each update (that is 1+1 -> 2, 2+1 -> 3, etc). The important aspect is that this key's api_cnt or (and) nested keys (like some_api_name for example) might not exist beforehand.

The closest I could get there is the following:

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

But I don't know how to create nested record api_cnt -> some_api_name and how to increment value based on itself (new_value = old_value + 1)

Example 1 where keys do not exist beforehand:

--PSEUDOCODE
id=1, metadata = {'inner something': [1,2,3,4,5]}
update myteble set ...{api_cnt: {'some_api_name': increment+1, 'other_api_name': increment+1} ... where id = 1
RESULT -- {'inner something': [1,2,3,4,5], {api_cnt: {'some_api_name': 1, 'other_api_name': 1}}

Example 2 where keys do exist already:

--PSEUDOCODE
id=1, metadata = {'inner something': [1,2,3,4,5], {api_cnt: {'some_api_name': 1, 'other_api_name': 8}}
update myteble set ...{api_cnt: {'some_api_name': increment+1, 'other_api_name': increment+1} ... where id = 1
RESULT -- {'inner something': [1,2,3,4,5], {api_cnt: {'some_api_name': 2, 'other_api_name': 9}}

Answer 1

PostgreSQL 14 and higher supports indexes for access to jsonb:

I can write simple plpgsql function:

create or replace function increment(v jsonb, n text)
returns jsonb as $$
begin
  v['api_cnt'][n] := coalesce((v['api_cnt'][n])::int + 1, 1);
  return v;
end;
$$ language plpgsql;

create table foo(id int, v jsonb);
insert into foo values(1, '{}');

update foo set v = increment(v, 'some_api_name');
UPDATE 1
update foo set v = increment(v, 'some_api_name');
UPDATE 1
update foo set v = increment(v, 'some_other_name');
UPDATE 1
select * from foo;
┌────┬─────────────────────────────────────────────────────────┐
│ id │                            v                            │
╞════╪═════════════════════════════════════════════════════════╡
│  1 │ {"api_cnt": {"some_api_name": 2, "some_other_name": 1}} │
└────┴─────────────────────────────────────────────────────────┘
(1 row)

at the end, you don't need plpgsql function:

update foo set v['api_cnt']['some_api_name'] = to_jsonb(coalesce((v['api_cnt']['some_api_name'])::int + 1, 1)) 
 where id = 1;
UPDATE 1
select * from foo;
┌────┬─────────────────────────────────────────────────────────┐
│ id │                            v                            │
╞════╪═════════════════════════════════════════════════════════╡
│  1 │ {"api_cnt": {"some_api_name": 3, "some_other_name": 1}} │
└────┴─────────────────────────────────────────────────────────┘
(1 row)

update foo set v['api_cnt']['some_another_name'] = to_jsonb(coalesce((v['api_cnt']['some_another_api_name'])::int + 1, 1))
 where id = 1;
UPDATE 1
select * from foo;
┌────┬─────────────────────────────────────────────────────────────────────────────────┐
│ id │                                        v                                        │
╞════╪═════════════════════════════════════════════════════════════════════════════════╡
│  1 │ {"api_cnt": {"some_api_name": 3, "some_other_name": 1, "some_another_name": 1}} │
└────┴─────────────────────────────────────────────────────────────────────────────────┘

(1 row)

Attention!: all PostgreSQL values are immutable. Any update is implemented as an copy of value, then this copied value is modified and stored. Previous value is destroyed. Modification of any jsonb value is significantly slower than just modification of numeric column, and it can be very slow, if this jsonb value is large or large and indexed. Non atomic types like json, jsonb are not too good for frequently modified values. When are short, then it can work, but when are longer (what is longer depends on hw), then it can be pretty slow (against relational model).