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
    }
}
Zur Übersicht
Simon Jakubowski

Mehr vom Devsquad...

Sophia Brandt

enter.js Recap

Sophia Brandt

Solid.js - Eine deklarative, effiziente und flexible JavaScript-Library zum Bauen von Benutzeroberflächen

Hallo, ich bin Jörg Herbst!

Ich bin der CEO von newcubator und freue mich über jede Nachricht!

* Pflichtfeld