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.