20.10.2021

Postgres JSON beginner Class #2

How to Append / Modify `jsonb`?

jsonb_set

Replaces a part of the json

jsonb_set(
  jsonb_in:           jsonb
  path:               text[]
  replacement:        jsonb
  create_if_missing:  boolean default true
)

before

{
    "creation": {
        "minDate": "2021-01-01",
        "maxDate": "2021-10-01",
        "quotaRestricted": true
    },
    "update": {
        "maxDate": null,
        "minDate": null
    }
}

replace

-- Replace the configuration.creation with the Result of Appending configuration.creation with the new tuple   
UPDATE tool_configuration
SET configuration = JSONB_SET(configuration, '{"creation"}', configuration -> 'creation' || '{"enabled": true}')

after

{
    "creation": {
        "minDate": "2021-01-01",
        "maxDate": "2021-10-01",
        "quotaRestricted": true,
        "enabled": true
    },
    "update": {
        "maxDate": null,
        "minDate": null
    }
}

jsonb_insert

Append new information into a jsonb (not able to override any information)

jsonb_insert(
  jsonb_in:           jsonb
  path:               text[]
  replacement:        jsonb
  create_if_missing:  boolean default true
)

before

{
    "creation": {
        "minDate": "2021-01-01",
        "maxDate": "2021-10-01",
        "quotaRestricted": true
    },
    "update": {
        "maxDate": null,
        "minDate": null
    }
}

append

UPDATE tool_configuration
SET configuration = JSONB_INSERT(configuration, '{"creation", "validation"}', '{"enabled": true}')

after

{
    "creation": {
        "validation": {
           "enabled": true
        }
        "minDate": "2021-01-01",
        "maxDate": "2021-10-01",
        "quotaRestricted": true
    },
    "update": {
        "maxDate": null,
        "minDate": null
    }
}