I was looking into upserting data into pgsql jsonb field but with a behavior similar to mongodb $inc operator.
upsert itself was solved by using:
insert ...
on conflict(cols) do update set ...
this snippet is just a dirty reminder on how jsonb can be updated with new keys:
select
d || jsonb_build_object(
'x', coalesce(cast(d->>'x' as int), 0) + 40,
'y', coalesce(cast(d->>'y' as int), 0) + 40
)
from (
select '{"x":5000, "z":100}'::jsonb as d
) as data
And a more complete example:
CREATE TABLE monitoring.usage_stats
(
id serial NOT NULL,
date timestamp without time zone NOT NULL,
application_id uuid NOT NULL,
organization_id uuid NOT NULL,
user_id uuid,
token_id uuid,
referrer text,
id_1 text,
id_2 text,
id_3 text,
id_4 text,
id_5 text,
counters jsonb NOT NULL DEFAULT '{}'::jsonb,
CONSTRAINT "PK_monitoring.usage_stats" PRIMARY KEY (id)
);
CREATE UNIQUE INDEX usage_stats_uq_usage_stats_composite_key
ON monitoring.usage_stats
USING btree
(date, application_id, organization_id, user_id, token_id, referrer COLLATE pg_catalog."default", id_1 COLLATE pg_catalog."default", id_2 COLLATE pg_catalog."default", id_3 COLLATE pg_catalog."default", id_4 COLLATE pg_catalog."default", id_5 COLLATE pg_catalog."default");
INSERT INTO monitoring.usage_stats (
date,
application_id,
organization_id,
user_id,
token_id,
referrer,
id_1,
id_2,
id_3,
id_4,
id_5,
counters
)
VALUES (
@date,
@applicationId,
@organizationId,
@userId,
@tokenId,
@referrer,
@id1,
@id2,
@id3,
@id4,
@id5,
@counters::jsonb
)
ON CONFLICT (date,application_id,organization_id,user_id,token_id,referrer,id_1,id_2,id_3,id_4,id_5)
DO UPDATE SET
counters = usage_stats.counters || jsonb_build_object('test',coalesce(cast(usage_stats.counters->>'test' as int), 0) + 2);