pgsql - update jsonb with behavior similar to monogdb $inc operator

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);

Installing mongodb as a service on windows

  1. Get the latest version of mongodb from here.
  2. Unpack the content of the archive wherever you need.
    1. [edit] looked at v3 at some point and could not find the binary - only msi available. in order to unpack msi use 7zip or type the following in the cmd (you may need admin rights); instaling msi will also extract it ;)
    2. msiexec /a filepath to MSI file /qb TARGETDIR=filepath to target folder
      
  3. Create a directory for the db data
  4. Prepare a mongodb config, for example:
  5. systemLog:
        destination: file
        path: "h:\\ere\\goes\\path\\to\\the\\logfile.log"
        quiet: true
        logAppend: true
    storage:
        journal:
            enabled: true
    net:
        port: 10005
        bindIp: 1270.0.0.1
    storage:
        dbPath: h:\\ere\\goes\\db\\path\\
    • starting with 2.6 cfg is in yaml, the old syntax is compatible though
    • spaces are used for indentation, tabs will not do (see)
    • paths need to be escaped
  6. Install mongodb as a service:
  7. mongod.exe --config h:\ere\goes\the\cfg\path --install
    
    • By default --install command will create a service named MongoDB; in order to adjust it have a look at the --serviceName, --serviceDisplayName and --serviceDescription params
    • By default the service will be configured to be runned under the ".\LocalSystem" not the "NT AUTHORITY\NetworkService" account as one coulde expect; If you want to specify a service user / pass use the --serviceUser and --servicePassword args

  8. And start it either through the services tab of the task manager or from the command line:
  9. net start serviceName


The service can be stopped through the services tab or from the command line:

net stop serviceName


To uninstall the service run:

mongod.exe --remove --serviceName sName