PostgresSQL => Some CRUD operations with JSONB

test.sql

--------------------------------------------------------
-- Add key into a nested object and hard code its value
--------------------------------------------------------
UPDATE my.table
SET data = jsonb_set(data::jsonb, '{"user","config","application"}', '"GOOGLE"');
/*
-- before
{
  "user": {
    "fullname": "Barney Stinson",
    "job": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark"
    }
  }
}

-- after
{
  "user": {
    "fullname": "Barney Stinson",
    "job": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark",
      "application": "GOOGLE"
    }
  }
}
*/

--------------------------------------------------------
-- Copy key:value from a nested object to another
--------------------------------------------------------
UPDATE my.table
SET data = jsonb_set(data::jsonb, '{"user","config","job"}', data::jsonb#>'{user,job}');
/*
-- before
{
  "user": {
    "fullname": "Barney Stinson",
    "job": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark"
    }
  }
}

-- after
{
  "user": {
    "fullname": "Barney Stinson",
    "job": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark",
      "job": "unknown"
    }
  }
}
*/

--------------------------------------------------------
-- Add key into a nested object and get its value from another table column
--------------------------------------------------------
UPDATE my.table
SET data = jsonb_set(data::jsonb, '{"user","config","job"}', to_json(job)::jsonb);
/*
-- before
{
  "user": {
    "fullname": "Barney Stinson",
    "job": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark"
    }
  }
}

-- after
{
  "user": {
    "fullname": "Barney Stinson",
    "job": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark",
      "job": "Job result from another column"
    }
  }
}
*/

--------------------------------------------------------
-- remove nested object key:value
--------------------------------------------------------
UPDATE my.table
SET data = data::jsonb #- '{user,job}';
/*
-- before
{
  "user": {
    "fullname": "Barney Stinson",
    "job": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark"
    }
  }
}

-- after
{
  "user": {
    "fullname": "Barney Stinson",
    "config": {
      "notifications": false,
      "theme": "dark"
    }
  }
}
*/

--------------------------------------------------------
-- rename nested object key
--------------------------------------------------------
UPDATE my.table
SET data = jsonb_set(data::jsonb #- '{user,job}', '{user,profession}', data::jsonb#>'{user,job}');
/*
-- before
{
  "user": {
    "fullname": "Barney Stinson",
    "job": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark"
    }
  }
}

-- after
{
  "user": {
    "fullname": "Barney Stinson",
    "profession": "unknown",
    "config": {
      "notifications": false,
      "theme": "dark"
    }
  }
}
*/