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 likelly 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). Important aspect is that this keys 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 dont 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}}

Postgres version is 12 unfortunatelly

Thank you!