Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select setseed(.42);
create table my_table(jbdata) with (fillfactor=100) as 
select jsonb_build_object('kws',case when .5>random() 
                                     then jsonb_build_object(random()::text,random())
                                     else '{}'::jsonb end,
                          'distinct',(random()*1e3)::int,
                          (array['count','total_count'])[(1+(.4>random())::int)],
                          (1e3+random()*6e3)::int)
from generate_series(1,1e5);

--create index jsonb_path_ops_idx on my_table using gin(jbdata jsonb_path_ops);
create index jsonb_ops_idx on my_table using gin(jbdata jsonb_ops);

select jbdata
from my_table tablesample bernoulli(.42)repeatable(.42) 
limit 10;

setseed

SELECT 1

SELECT 100000

CREATE INDEX

jbdata

{"kws": {}, "distinct": 81, "total_count": 2518}

{"kws": {"0.5428230028095153": 0.4848447815399721}, "count": 3981, "distinct": 814}

{"kws": {}, "count": 5447, "distinct": 832}

{"kws": {}, "count": 6726, "distinct": 862}

{"kws": {"0.29893793053305506": 0.9434883601422535}, "count": 3380, "distinct": 843}

{"kws": {}, "count": 5354, "distinct": 700}

{"kws": {}, "count": 3825, "distinct": 275}

{"kws": {"0.4390588751140678": 0.9974350614926453}, "count": 6128, "distinct": 738}

{"kws": {}, "distinct": 761, "total_count": 1862}

{"kws": {}, "count": 5510, "distinct": 259}

SELECT 10

vacuum analyze my_table;

VACUUM

begin;
explain analyze verbose
update my_table
set jbdata = jsonb_set(jbdata,
                       '{count}',
                       coalesce(jbdata->'count',jbdata->'total_count'),
                       true)-'total_count'
where jbdata ? 'total_count'            --jsonb_ops
--where jbdata @@ 'exists($.total_count)' --jsonb_path_ops
--where jbdata @? '$.total_count'         --jsonb_path_ops
RETURNING *;

select count(*) total,
       count(*)filter(where jbdata ? 'count') count_key_present,
       count(*)filter(where jbdata ? 'total_count' ) total_count_key_present
from my_table;

select jbdata
from my_table tablesample bernoulli(.42)repeatable(.42) 
limit 40;

rollback;

BEGIN

QUERY PLAN

Update on public.my_table  (cost=266.92..2643.84 rows=37374 width=38) (actual time=3.489..696.357 rows=39703 loops=1)

  Output: jbdata

  ->  Bitmap Heap Scan on public.my_table  (cost=266.92..2643.84 rows=37374 width=38) (actual time=3.370..110.376 rows=39703 loops=1)

        Output: (jsonb_set(jbdata, '{count}'::text[], COALESCE((jbdata -> 'count'::text), (jbdata -> 'total_count'::text)), true) - 'total_count'::text), ctid

        Recheck Cond: (my_table.jbdata ? 'total_count'::text)

        Heap Blocks: exact=1518

        ->  Bitmap Index Scan on jsonb_ops_idx  (cost=0.00..257.58 rows=37374 width=0) (actual time=3.170..3.171 rows=39703 loops=1)

              Index Cond: (my_table.jbdata ? 'total_count'::text)

Planning Time: 0.199 ms

Execution Time: 700.306 ms

EXPLAIN

total
count_key_present
total_count_key_present

100000
100000
0

SELECT 1

jbdata

{"kws": {"0.5428230028095153": 0.4848447815399721}, "count": 3981, "distinct": 814}

{"kws": {}, "count": 5447, "distinct": 832}

{"kws": {}, "count": 6726, "distinct": 862}

{"kws": {"0.29893793053305506": 0.9434883601422535}, "count": 3380, "distinct": 843}

{"kws": {}, "count": 5354, "distinct": 700}

{"kws": {}, "count": 3825, "distinct": 275}

{"kws": {"0.4390588751140678": 0.9974350614926453}, "count": 6128, "distinct": 738}

{"kws": {}, "count": 5510, "distinct": 259}

{"kws": {"0.27212562117501493": 0.35792897843778637}, "count": 4601, "distinct": 387}

{"kws": {"0.038441870654943866": 0.38611745759872296}, "count": 2674, "distinct": 552}

{"kws": {}, "count": 5595, "distinct": 282}

{"kws": {}, "count": 3091, "distinct": 231}

{"kws": {}, "count": 5739, "distinct": 721}

{"kws": {}, "count": 3857, "distinct": 508}

{"kws": {"0.6593312850150677": 0.40765373616342293}, "count": 1280, "distinct": 896}

{"kws": {"0.8930461991318954": 0.35256631987918396}, "count": 5180, "distinct": 831}

{"kws": {"0.6099438316702932": 0.3253088001775415}, "count": 5415, "distinct": 125}

{"kws": {"0.12230448478607947": 0.542634978808697}, "count": 2833, "distinct": 986}

{"kws": {"0.14981694990376515": 0.9709947749372925}, "count": 2255, "distinct": 992}

{"kws": {}, "count": 3022, "distinct": 641}

{"kws": {}, "count": 3137, "distinct": 292}

{"kws": {"0.6590487008124142": 0.6005338543960157}, "count": 2884, "distinct": 821}

{"kws": {"0.16708292716970163": 0.041508910772901864}, "count": 4528, "distinct": 871}

{"kws": {"0.9443210715823618": 0.23389658209416408}, "count": 2094, "distinct": 803}

{"kws": {"0.7926562077915884": 0.25818263129274177}, "count": 3979, "distinct": 125}

{"kws": {"0.2812357236479859": 0.5176863348256884}, "count": 5107, "distinct": 594}

{"kws": {}, "count": 4630, "distinct": 454}

{"kws": {}, "count": 6764, "distinct": 302}

{"kws": {"0.30187438968418867": 0.5259978976790984}, "count": 2832, "distinct": 503}

{"kws": {}, "count": 4251, "distinct": 758}

{"kws": {}, "count": 2703, "distinct": 450}

{"kws": {}, "count": 5574, "distinct": 329}

{"kws": {}, "count": 3039, "distinct": 153}

{"kws": {}, "count": 3098, "distinct": 840}

{"kws": {"0.025373603797073097": 0.09496733386201939}, "count": 5527, "distinct": 303}

{"kws": {}, "count": 1115, "distinct": 537}

{"kws": {}, "count": 3728, "distinct": 496}

{"kws": {}, "count": 5024, "distinct": 766}

{"kws": {"0.5595099240911636": 0.6436261965281569}, "count": 2484, "distinct": 746}

{"kws": {"0.12858293245381636": 0.07493588865483769}, "count": 1509, "distinct": 880}

SELECT 40

ROLLBACK