JSONB modification functions in PostgreSQL - Deep Dive

Overview - JSONB modification functions

What is it?

JSONB modification functions in PostgreSQL let you change parts of JSON data stored in a special binary format called JSONB. These functions allow you to add, update, or remove keys and values inside JSONB columns without rewriting the entire JSON document. This makes working with JSON data inside databases faster and more flexible.

Why it matters

Without JSONB modification functions, changing JSON data inside a database would mean rewriting the whole JSON document every time you want to update a small part. This would be slow and inefficient, especially for large JSON objects. These functions solve this by letting you modify only the parts you want, saving time and resources.

Where it fits

Before learning JSONB modification functions, you should understand basic JSON and JSONB data types in PostgreSQL and how to query JSONB data. After this, you can learn about indexing JSONB for performance and advanced JSONB querying techniques.

Mental Model

Core Idea

JSONB modification functions let you surgically change parts of a JSON document stored in the database without rewriting the whole thing.

Think of it like...

Imagine a large book where you want to change a single sentence. Instead of rewriting the entire book, you use sticky notes to replace just that sentence. JSONB modification functions are like those sticky notes for JSON data.

┌─────────────────────────────┐
│        JSONB Document       │
│  {                         │
│    "name": "Alice",      │
│    "age": 30,             │
│    "city": "Paris"       │
│  }                         │
└─────────────┬───────────────┘
              │
  ┌───────────▼─────────────┐
  │ JSONB Modification Func │
  │  - Add key              │
  │  - Update value         │
  │  - Remove key           │
  └───────────┬─────────────┘
              │
┌─────────────▼─────────────┐
│ Modified JSONB Document    │
│  {                         │
│    "name": "Alice",     │
│    "age": 31,            │
│    "country": "France"  │
│  }                         │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding JSONB Data Type

Concept: Learn what JSONB is and how it stores JSON data efficiently in PostgreSQL.

JSONB is a binary format for JSON data in PostgreSQL. Unlike plain JSON text, JSONB stores data in a way that is faster to query and modify. It supports indexing and allows you to work with JSON data as a native PostgreSQL type.

Result

You can store JSON data in a column with type JSONB and query it efficiently.

Understanding JSONB as a binary, query-friendly format is key to appreciating why modification functions exist.

2

Foundation

Basic JSONB Querying Techniques

Concept: Learn how to extract data from JSONB using simple operators.

You can use operators like -> and ->> to get JSON objects or text values from JSONB columns. For example, data->'name' returns the JSON value of the key 'name'.

Result

You can read parts of JSONB data easily without modifying it.

Knowing how to read JSONB data is necessary before learning how to change it.

3

Intermediate

Using jsonb_set to Update Values

🤔Before reading on: do you think jsonb_set replaces the whole JSON or just a part? Commit to your answer.

Concept: jsonb_set lets you update or add a value at a specific path inside a JSONB document.

The function jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean) updates the JSONB document at the given path with new_value. If create_missing is true, it adds keys if they don't exist.

Result

You can change or add nested keys without rewriting the entire JSON document.

Understanding jsonb_set shows how PostgreSQL allows precise updates inside JSONB, improving efficiency.

4

Intermediate

Removing Keys with - and #- Operators

🤔Before reading on: do you think removing a key from JSONB changes the original or returns a new JSONB? Commit to your answer.

Concept: PostgreSQL provides operators to remove keys or elements from JSONB data.

The - operator removes a key from the top-level JSONB object. The #- operator removes a key or element at a specified path. Both return a new JSONB without modifying the original data in place.

Result

You can delete keys or nested elements from JSONB data easily.

Knowing these operators helps you clean or prune JSONB data without complex queries.

5

Intermediate

Concatenating JSONB with || Operator

Concept: You can merge two JSONB documents using the || operator.

The || operator combines two JSONB objects. If keys overlap, the right operand's value overwrites the left's. This is useful for adding or updating multiple keys at once.

Result

You can quickly combine JSONB data to build or update documents.

Concatenation provides a flexible way to modify JSONB by merging changes instead of single key updates.

6

Advanced

Using jsonb_insert for Precise Insertions

🤔Before reading on: do you think jsonb_insert can add keys inside arrays or only objects? Commit to your answer.

Concept: jsonb_insert lets you insert new elements at a specific position in JSONB arrays or objects.

jsonb_insert(target jsonb, path text[], new_value jsonb, insert_after boolean) inserts new_value at the path location. For arrays, insert_after controls if insertion is before or after the index.

Result

You can add elements inside arrays or objects at exact positions.

jsonb_insert gives fine control over JSONB structure changes beyond simple updates.

7

Expert

Performance Implications of JSONB Modifications

🤔Before reading on: do you think JSONB modification functions update data in place or rewrite parts internally? Commit to your answer.

Concept: JSONB modification functions create new JSONB values internally; they do not modify data in place.

When you use jsonb_set or operators like -, PostgreSQL creates a new JSONB value with the changes applied. This means large JSONB documents can be costly to modify repeatedly. Indexes on JSONB can help speed up queries but do not speed up modifications.

Result

Understanding this helps you design your JSONB usage to avoid performance bottlenecks.

Knowing that JSONB modifications rewrite data internally explains why careful design and indexing are critical for performance.

Under the Hood

PostgreSQL stores JSONB data as a binary tree structure optimized for fast access. When you modify JSONB using functions like jsonb_set, PostgreSQL traverses this tree, creates a new tree with the changes, and stores it as a new value. The original JSONB data remains unchanged until replaced. This copy-on-write approach ensures data integrity and concurrency safety.

Why designed this way?

This design balances flexibility and safety. Modifying JSONB in place would risk corrupting data or causing race conditions. Copy-on-write allows multiple transactions to work safely with JSONB data. Also, binary storage enables indexing and faster queries compared to plain JSON text.

┌───────────────┐
│ Original JSONB│
│  Tree Structure│
└───────┬───────┘
        │
        ▼
┌─────────────────────────────┐
│ jsonb_set or operator called │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│ Traverse tree to target path │
│ Create new nodes with changes│
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│ New JSONB tree with changes  │
│ Stored as new value          │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does jsonb_set modify the original JSONB data in place? Commit yes or no.

Common Belief: jsonb_set changes the JSONB data directly inside the database without creating a new copy.

Reality: jsonb_set returns a new JSONB value with the changes; it does not modify the original data in place.

Why it matters: Assuming in-place modification can lead to confusion about transaction behavior and data consistency.

Quick: Can the - operator remove nested keys deep inside JSONB? Commit yes or no.

Common Belief: The - operator can remove keys at any depth inside JSONB documents.

Reality: The - operator only removes top-level keys; to remove nested keys, you must use the #- operator with a path.

Why it matters: Misusing - instead of #- can cause failed queries or unexpected results when trying to remove nested keys.

Quick: Does concatenating JSONB with || merge arrays by combining elements? Commit yes or no.

Common Belief: The || operator merges arrays inside JSONB by combining their elements.

Reality: The || operator merges JSONB objects by key; for arrays, it replaces the left array with the right one instead of merging elements.

Why it matters: Expecting array merging can cause bugs when updating JSONB arrays with ||.

Quick: Is jsonb_insert limited to objects only? Commit yes or no.

Common Belief: jsonb_insert only works with JSONB objects, not arrays.

Reality: jsonb_insert works with both JSONB objects and arrays, allowing insertion at specific positions.

Why it matters: Not knowing this limits the ability to manipulate JSONB arrays precisely.

Expert Zone

1

jsonb_set with create_missing=false will not add new keys if the path does not exist, which can silently fail if not checked.

2

Repeated modifications on large JSONB documents can cause bloat in storage due to copy-on-write, requiring vacuuming to reclaim space.

3

Indexes on JSONB fields speed up queries but do not improve the speed of modification functions, so design should minimize frequent large updates.

When NOT to use

Avoid heavy use of JSONB modification functions for very large JSON documents that change frequently; instead, consider normalizing data into relational tables or using specialized document stores like MongoDB for high update workloads.

Production Patterns

In production, JSONB modification functions are often used to update user preferences or settings stored as JSON, where only small parts change. They are combined with partial indexes on JSONB keys to optimize read performance while keeping update costs manageable.

Connections

Immutable Data Structures

JSONB modification functions follow an immutable update pattern similar to functional programming data structures.

Understanding immutability in programming helps grasp why JSONB modifications create new values instead of changing originals.

Copy-on-Write File Systems

The way PostgreSQL handles JSONB modifications resembles copy-on-write file systems that create new data blocks on changes.

Knowing copy-on-write systems clarifies the performance and storage tradeoffs of JSONB updates.

Document Stores (e.g., MongoDB)

JSONB modification functions provide some document store features inside a relational database.

Comparing JSONB to document stores helps understand when to use PostgreSQL JSONB versus specialized NoSQL databases.

Common Pitfalls

#1 Trying to update a nested key with jsonb_set but forgetting to set create_missing to true.

Wrong approach:

UPDATE table SET data = jsonb_set(data, '{address,city}', '"New York"');

Correct approach:

UPDATE table SET data = jsonb_set(data, '{address,city}', '"New York"', true);

Root cause:

By default, jsonb_set does not add missing keys, so nested paths must exist or create_missing must be true.

#2 Using the - operator to remove a nested key inside JSONB.

Wrong approach:

SELECT data - 'address' - 'city' FROM table;

Correct approach:

SELECT data #- '{address,city}' FROM table;

Root cause:

The - operator only removes top-level keys; nested keys require the #- operator with a path.

#3 Expecting the || operator to merge arrays inside JSONB documents.

Wrong approach:

SELECT '{"arr": [1,2]}'::jsonb || '{"arr": [3,4]}'::jsonb;

Correct approach:

Manually merge arrays using jsonb functions or application logic, as || replaces arrays instead of merging.

Root cause:

The || operator merges objects by keys but replaces arrays entirely.

Key Takeaways

JSONB modification functions let you update parts of JSON data efficiently without rewriting the whole document.

These functions work by creating new JSONB values internally, following an immutable data pattern.

Operators like jsonb_set, -, #- and || provide flexible ways to add, update, remove, and merge JSONB data.

Understanding the difference between top-level and nested key operations is crucial to avoid mistakes.

Performance considerations mean you should design JSONB usage carefully, especially for large or frequently updated data.

Practice

1. What does the PostgreSQL function jsonb_set do when used on a JSONB column?

easy

A. It creates a new JSONB column in the table.

B. It deletes the entire JSONB column from the table.

C. It converts JSONB data into plain text format.

D. It updates or adds a value at a specified path inside the JSONB data.

Solution

1. Step 1: Understand the purpose of jsonb_set

   The function jsonb_set is designed to update or add a value at a specific location inside JSONB data using a path.

2. Step 2: Compare with other options

   The other options describe actions unrelated to jsonb_set's purpose, such as creating or deleting columns or converting data types.

3. Final Answer:

   It updates or adds a value at a specified path inside the JSONB data. -> Option D

4. Quick Check:

   jsonb_set updates JSONB data [OK]

Hint: jsonb_set changes or adds JSONB values at paths [OK]

Common Mistakes:

• Thinking jsonb_set deletes data
• Confusing jsonb_set with data type conversion
• Assuming jsonb_set creates new columns

2. Which of the following is the correct syntax to remove a key named "age" from a JSONB column data in PostgreSQL?

easy

A. UPDATE table SET data = data - 'age';

B. UPDATE table SET data = jsonb_set(data, '{age}', NULL);

C. UPDATE table SET data = data + 'age';

D. UPDATE table SET data = jsonb_remove(data, 'age');

Solution

1. Step 1: Identify the correct operator for key removal

   In PostgreSQL, the minus operator (-) removes a key from JSONB data, so data - 'age' removes the key "age".

2. Step 2: Check other options for correctness

   UPDATE table SET data = jsonb_set(data, '{age}', NULL); tries to set the key to NULL but does not remove it. UPDATE table SET data = data + 'age'; adds a key, which is incorrect. UPDATE table SET data = jsonb_remove(data, 'age'); uses a non-existent function jsonb_remove.

3. Final Answer:

   UPDATE table SET data = data - 'age'; -> Option A

4. Quick Check:

   Minus operator removes JSONB key [OK]

Hint: Use minus (-) operator to remove JSONB keys [OK]

Common Mistakes:

• Using jsonb_set with NULL to remove keys
• Trying to add keys to remove them
• Using non-existent functions like jsonb_remove

3. Given the JSONB column data with value '{"name": "Alice", "details": {"age": 30}}', what will be the result of this query?

SELECT jsonb_set(data, '{details,age}', '31', false) FROM table;

medium

A. {"name": "Alice", "details": {"age": 31}}

B. {"name": "Alice", "details": {"age": "31"}}

C. {"name": "Alice", "details": {"age": 30}}

D. {"name": "Alice", "details": {"age": null}}

Solution

1. Step 1: Understand jsonb_set parameters

   The function jsonb_set updates the value at path '{details,age}' to '31' (a JSON string) because the new value is given as text, not as a number.

2. Step 2: Check the effect of the 'create_missing' flag

   The last parameter is false, so it updates existing key without creating new ones. The age key is updated to the string '31', not the number 31.

3. Final Answer:

   {"name": "Alice", "details": {"age": "31"}} -> Option B

4. Quick Check:

   jsonb_set updates value as text unless cast [OK]

Hint: jsonb_set inserts text values unless cast to JSONB [OK]

Common Mistakes:

• Assuming '31' is numeric without casting
• Expecting no change when create_missing is false
• Confusing JSON string and number types

4. You want to update a nested key in a JSONB column but your query

UPDATE table SET data = jsonb_set(data, '{info,score}', 100);

throws an error. What is the likely cause?

medium

A. The new value 100 is not cast to JSONB, causing a type error.

B. The path '{info,score}' is invalid syntax and must be a string.

C. jsonb_set cannot update nested keys, only top-level keys.

D. The data column is not JSONB type.

Solution

1. Step 1: Check the new value type in jsonb_set

   The third argument must be JSONB type. Passing 100 as an integer without casting causes a type error.

2. Step 2: Verify other options

   The path '{info,score}' is invalid syntax and must be a string. is incorrect because the path syntax is valid. jsonb_set cannot update nested keys, only top-level keys. is false; jsonb_set supports nested keys. The data column is not JSONB type. might cause error but question implies the error is from the query syntax.

3. Final Answer:

   The new value 100 is not cast to JSONB, causing a type error. -> Option A

4. Quick Check:

   jsonb_set value must be JSONB type [OK]

Hint: Cast new value to JSONB to avoid type errors [OK]

Common Mistakes:

• Passing plain integers without casting
• Misunderstanding path syntax
• Assuming jsonb_set can't update nested keys

5. You have a JSONB column data storing user info. You want to increment the "visits" count inside "stats" by 1, but visits might be missing. Which query correctly updates data to add or increment visits?

hard

A. UPDATE table SET data = data || '{"stats":{"visits":1}}';

B. UPDATE table SET data = jsonb_set(data, '{stats,visits}', '1');

C. UPDATE table SET data = jsonb_set(data, '{stats,visits}', to_jsonb((COALESCE((data->'stats'->>'visits')::int, 0) + 1)));

D. UPDATE table SET data = jsonb_set(data, '{visits}', to_jsonb(1));

Solution

1. Step 1: Understand the need to increment or add visits

   The query must get the current visits count, convert it to integer, handle missing key with COALESCE to 0, add 1, then convert back to JSONB.

2. Step 2: Analyze each option

   UPDATE table SET data = jsonb_set(data, '{stats,visits}', to_jsonb((COALESCE((data->'stats'->>'visits')::int, 0) + 1))); correctly uses COALESCE and to_jsonb to update visits inside stats. UPDATE table SET data = jsonb_set(data, '{stats,visits}', '1'); sets visits to string '1' always. UPDATE table SET data = data || '{"stats":{"visits":1}}'; overwrites stats with visits=1, losing other keys. UPDATE table SET data = jsonb_set(data, '{visits}', to_jsonb(1)); updates visits at top level, not inside stats.

3. Final Answer:

   UPDATE table SET data = jsonb_set(data, '{stats,visits}', to_jsonb((COALESCE((data->'stats'->>'visits')::int, 0) + 1))); -> Option C

4. Quick Check:

   Use COALESCE and to_jsonb for safe increment [OK]

Hint: Use COALESCE and cast to increment JSONB numbers safely [OK]

Common Mistakes:

• Overwriting nested objects accidentally
• Not casting string to int before increment
• Updating wrong JSONB path