Modifying JSON Data

PostgreSQL provides a rich set of functions and operators for modifying JSONB data. An important concept to understand: JSONB functions return new values -- they do not modify data in place. To persist changes, you use them inside an UPDATE statement.

The || Merge Operator

The || operator merges two JSONB objects. When keys overlap, the right side wins.

-- Merge two objects
SELECT '{"theme": "dark"}'::jsonb || '{"language": "en"}'::jsonb;
-- Result: {"theme": "dark", "language": "en"}

Overwriting Keys

-- The right side overwrites matching keys
SELECT '{"theme": "dark", "font": 14}'::jsonb || '{"theme": "light"}'::jsonb;
-- Result: {"font": 14, "theme": "light"}

Adding a Key to Preferences

-- Add a "language" setting to preferences
SELECT
  username,
  preferences || '{"language": "en"}'::jsonb AS updated_preferences
FROM user_settings;

Persisting with UPDATE

-- Actually update the table
UPDATE user_settings
SET preferences = preferences || '{"language": "en"}'::jsonb
WHERE username = 'alice';

Merging Nested Objects

Important: || only merges at the top level. Nested objects are replaced entirely:

SELECT '{"notifications": {"email": true, "push": false}}'::jsonb
    || '{"notifications": {"sms": true}}'::jsonb;
-- Result: {"notifications": {"sms": true}}
-- The original email and push settings are LOST!

To merge nested objects, use jsonb_set() instead.

jsonb_set() -- Set a Value at a Path

jsonb_set() modifies a value at a specific path within a JSONB document.

jsonb_set(target, path, new_value [, create_if_missing])

• target: The original JSONB value
• path: Text array path to the key ('{key1,key2}')
• new_value: The new JSONB value to set
• create_if_missing: Boolean, default true -- create the key if it doesn't exist

Updating a Top-Level Key

-- Change theme to "light"
SELECT jsonb_set(preferences, '{theme}', '"light"')
FROM user_settings
WHERE username = 'alice';

Note: The new value must be valid JSON. Strings need inner quotes: '"light"'.

Updating a Nested Key

-- Change font_size to 16 inside the editor object
SELECT
  username,
  jsonb_set(preferences, '{editor,font_size}', '16') AS updated
FROM user_settings;

Adding a New Nested Key

-- Add a new "auto_save" key inside the editor object
SELECT jsonb_set(
  preferences,
  '{editor,auto_save}',
  'true'
) AS updated
FROM user_settings
WHERE username = 'alice';

Preventing Key Creation

If you only want to update existing keys (not create new ones), set the fourth argument to false:

-- This will NOT create "new_key" if it doesn't exist
SELECT jsonb_set(preferences, '{new_key}', '"value"', false)
FROM user_settings
WHERE username = 'alice';

Chaining jsonb_set for Multiple Updates

UPDATE user_settings
SET preferences = jsonb_set(
  jsonb_set(preferences, '{theme}', '"solarized"'),
  '{editor,font_size}', '18'
)
WHERE username = 'alice';

The - Operator (Delete Key)

The - operator removes a key from a JSONB object.

Removing a Top-Level Key

-- Remove the "theme" key
SELECT preferences - 'theme' AS without_theme
FROM user_settings
WHERE username = 'alice';

Removing Multiple Keys

Chain the - operator:

-- Remove both "theme" and "editor"
SELECT preferences - 'theme' - 'editor' AS minimal_prefs
FROM user_settings;

Removing a Nested Key with #-

The #- operator removes a key at a specified path:

-- Remove the "tab_size" key from inside "editor"
SELECT preferences #- '{editor,tab_size}' AS updated
FROM user_settings
WHERE username = 'alice';

Persisting Deletions

-- Actually remove a key
UPDATE user_settings
SET preferences = preferences - 'theme'
WHERE username = 'bob';

jsonb_build_object()

Builds a JSONB object from a list of alternating keys and values. Very useful for constructing JSON dynamically.

SELECT jsonb_build_object(
  'name', 'Alice',
  'role', 'admin',
  'active', true,
  'login_count', 42
);

Result:

{"name": "Alice", "role": "admin", "active": true, "login_count": 42}

Using Column Values

SELECT jsonb_build_object(
  'user', username,
  'theme', preferences ->> 'theme',
  'font_size', (preferences -> 'editor' ->> 'font_size')::int
) AS user_summary
FROM user_settings;

jsonb_build_array()

Builds a JSONB array from a list of values:

SELECT jsonb_build_array(1, 'two', true, null, '{"nested": "object"}'::jsonb);

Result:

[1, "two", true, null, {"nested": "object"}]

Combining with build_object

SELECT jsonb_build_object(
  'users', jsonb_build_array('alice', 'bob', 'charlie'),
  'count', 3
);

Result:

{"users": ["alice", "bob", "charlie"], "count": 3}

jsonb_agg() -- Aggregate Rows into a JSON Array

jsonb_agg() collects values from multiple rows into a single JSONB array. It's an aggregate function like SUM() or COUNT().

-- Collect all usernames into a JSON array
SELECT jsonb_agg(username) AS all_users
FROM user_settings;

Result:

["alice", "bob", "charlie", "diana", "eve", "frank", "grace", "heidi"]

Aggregating Objects

-- Collect user summaries as an array of objects
SELECT jsonb_agg(
  jsonb_build_object(
    'username', username,
    'theme', preferences ->> 'theme'
  )
) AS user_summaries
FROM user_settings;

Grouped Aggregation

-- Group users by theme, collecting usernames into arrays
SELECT
  preferences ->> 'theme' AS theme,
  jsonb_agg(username) AS users
FROM user_settings
GROUP BY preferences ->> 'theme';

theme	users
dark	["alice", "charlie", "eve", ...]
light	["bob", "diana", ...]

row_to_json() -- Convert Rows to JSON

Converts an entire row (or record) into a JSON object:

SELECT row_to_json(us) AS user_json
FROM user_settings us;

Each row becomes a JSON object with column names as keys.

Selecting Specific Columns

Use a subquery to control which columns appear:

SELECT row_to_json(t) AS user_json
FROM (
  SELECT username, preferences ->> 'theme' AS theme
  FROM user_settings
) t;

Result per row:

{"username": "alice", "theme": "dark"}

jsonb_strip_nulls()

Removes all keys with null values from a JSONB object:

SELECT jsonb_strip_nulls(
  '{"name": "Alice", "email": null, "age": 30, "phone": null}'::jsonb
);
-- Result: {"age": 30, "name": "Alice"}

jsonb_pretty()

Formats JSONB with indentation for human readability:

SELECT jsonb_pretty(preferences)
FROM user_settings
WHERE username = 'alice';

Result:

{
    "theme": "dark",
    "editor": {
        "tab_size": 4,
        "font_size": 14
    },
    "notifications": {
        "email": true,
        "push": false
    }
}

Complete Update Pattern

Here is a realistic pattern for modifying nested JSONB:

-- Enable push notifications and change theme in one UPDATE
UPDATE user_settings
SET preferences = jsonb_set(
  jsonb_set(
    preferences,
    '{notifications,push}',
    'true'
  ),
  '{theme}',
  '"solarized"'
)
WHERE username = 'alice'
RETURNING username, jsonb_pretty(preferences);

Try It Yourself

1. Use the || operator to add a {"language": "en"} setting to the preferences for all users. Show the result without updating.

2. Use jsonb_set to change the font_size to 18 inside the editor object. Show the before and after.

3. Use jsonb_build_object to create a summary object for each user containing their username and theme.

4. Use jsonb_agg to collect all usernames into a single JSON array, grouped by their theme preference.