Updating Data in JSON Objects Using jsonb_set() and jsonb_insert()

So, JSONB in PostgreSQL is a powerful tool for storing complex hierarchical data structures. But what if you need to update a value inside a JSONB column? Like, say, changing a user's phone number or adding a new category to an array? Sounds simple, but JSONB isn’t a table where you can just change a value in a cell directly. To update data in a JSON object, we use special functions. Today’s main stars are:

• jsonb_set(): for changing or adding a value at a specific "path".
• jsonb_insert(): for inserting a new element into a JSON array.

Updating Data with jsonb_set()

The jsonb_set() function lets you change part of a JSONB object or add new keys and values to it.

General Syntax

jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean)

• target — our JSONB object that we want to update.
• path — an array of strings representing the path to the key you want to change.
• new_value — the value you want to add or replace.
• create_missing — a boolean (TRUE or FALSE) that tells if missing keys should be created.

Let’s check out a simple example. Imagine we have a users table with a profile column of type JSONB, where user profiles are stored. One of the users wants to update their phone number. How do we do that?

-- Create the table and add some data
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO users (profile)
VALUES ('{"name": "Otto", "contact": {"phone": "+1-495-123-45-67"}}');

-- Update the phone number
UPDATE users
SET profile = jsonb_set(profile, '{contact,phone}', '"8-800-555-35-35"', FALSE)
WHERE id = 1;

-- Check the result
SELECT profile FROM users WHERE id = 1;

Result:

{
  "name": "Otto",
  "contact": {
    "phone": "8-800-555-35-35"
  }
}

Wow! We changed the phone number! Notice that the path to the key is specified as an array of strings '{contact,phone}'.

Adding a New Key

If the key you want to update doesn’t exist, you can use create_missing = TRUE to create it:

UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"Berlin"', TRUE)
WHERE id = 1;

-- Check the result
SELECT profile FROM users WHERE id = 1;

Result:

{
  "name": "Otto",
  "contact": {
    "phone": "8-800-555-35-35"
  },
  "address": {
    "city": "Berlin"
  }
}

Now we’ve got a new address section. Pretty handy, right?

Inserting Data with jsonb_insert()

The jsonb_insert() function is used to add elements to arrays inside JSONB objects.

General Syntax

jsonb_insert(target jsonb, path text[], new_value jsonb, insert_after boolean)

• target — the target JSONB object.
• path — an array of strings representing the path to the array where you want to insert elements.
• new_value — the value you want to add.
• insert_after — a boolean. If FALSE, the element is inserted before the specified index; if TRUE — after.

Here’s an example. Let’s say we have a table where the profile column stores a list of user interests. We want to add a new interest to the array:

-- Add some data for the example
UPDATE users
SET profile = jsonb_set(profile, '{interests}', '["sports", "music"]', TRUE)
WHERE id = 1;

-- Insert a new interest "programming" at the start of the list
UPDATE users
SET profile = jsonb_insert(profile, '{interests,0}', '"programming"', FALSE)
WHERE id = 1;

-- Check the result
SELECT profile FROM users WHERE id = 1;

Result:

{
  "name": "Otto",
  "contact": {
    "phone": "8-800-555-35-35"
  },
  "address": {
    "city": "Berlin"
  },
  "interests": [
    "programming",
    "sports",
    "music"
  ]
}

Common Problems and How to Avoid Them

Working with jsonb_set() and jsonb_insert() can be a bit tricky if you don’t keep these things in mind:

1. Wrong path to the key. If you specify the wrong path or try to update a non-existent element without create_missing=TRUE, you’ll get an error or nothing will change. Always check your JSON structure.
2. Type mismatch. Remember, new_value must be JSONB. If you want to insert a string, make sure to wrap it in double quotes ('"value"').
3. Overwriting data. If you try to update an array without using the right functions, you might accidentally wipe out old data. Use jsonb_insert() for safely adding new elements.

Example of an error:

-- Silently ignored: the key is not created
UPDATE users
SET profile = jsonb_set(profile, '{contacts}', '"new contact"', FALSE)
WHERE id = 1;

This call will not throw an error, but it won’t change anything either: when create_missing = FALSE and the key is missing, the function returns the original object unchanged. This is often a hidden bug — changes are silently “lost”.

How to avoid it:

-- Set create_missing = TRUE
UPDATE users
SET profile = jsonb_set(profile, '{contacts}', '"new contact"', TRUE)
WHERE id = 1;

Where Is This Used in Real Life?

Working with JSONB isn’t just for fun, it’s a super important skill for tons of modern apps. Here are a few real-world examples:

• Storing user settings. JSONB is perfect for dynamic data structures like app settings that can be different for every user.
• Integrating with external APIs. JSONB is great for storing raw data from REST APIs that return JSON objects.
• Big data analytics. Nested JSON structures let you work with IoT data, logs, or analytics.

That’s it for our intro to updating JSON objects. Now you know how to insert, update, and add data to JSONB, plus how to dodge common mistakes. In the next lecture, you’ll learn how to merge JSONB objects and work with them even more efficiently!