JSON vs JSONB differences in PostgreSQL - Trade-offs & Expert Analysis

Overview - JSON vs JSONB differences

What is it?

JSON and JSONB are two ways PostgreSQL stores JSON data. JSON stores data as plain text, while JSONB stores it in a binary format. Both let you save and query JSON data, but they work differently under the hood. This affects how fast and flexible your queries are.

Why it matters

Without JSON and JSONB, storing complex data structures in a database would be hard and slow. JSONB improves performance and allows indexing, making data retrieval faster. Without these, developers would struggle to efficiently handle semi-structured data, limiting modern app capabilities.

Where it fits

Before learning JSON vs JSONB, you should understand basic JSON format and relational databases. After this, you can explore advanced JSON querying, indexing, and performance tuning in PostgreSQL.

Mental Model

Core Idea

JSON stores data as text exactly as you write it, while JSONB stores data in a binary form optimized for fast searching and indexing.

Think of it like...

Think of JSON as a handwritten letter you keep in a folder, and JSONB as a typed, well-organized digital document stored in a database. The letter is easy to read but slow to search through, while the digital document is structured for quick access and searching.

┌───────────────┐       ┌───────────────┐
│   JSON (Text) │──────▶│ Stores raw text│
│               │       │  as-is, slow  │
└───────────────┘       │  to search    │
                        └───────────────┘

┌───────────────┐       ┌───────────────┐
│  JSONB (Binary)│─────▶│ Stores parsed │
│                │      │  binary data  │
│                │      │ fast to query │
└───────────────┘      └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding JSON Data Format

Concept: JSON is a text format for storing data as key-value pairs and arrays.

JSON stands for JavaScript Object Notation. It looks like text with curly braces { } for objects and square brackets [ ] for lists. For example: {"name": "Alice", "age": 30}. This format is easy for humans to read and write.

Result

You can represent complex data structures in a simple text format.

Understanding JSON as plain text helps you see why storing it as-is is simple but can be slow for computers to process.

2

Foundation

How PostgreSQL Stores JSON

Concept: PostgreSQL supports two JSON storage types: JSON and JSONB.

The JSON type stores the exact text you input, preserving spaces and order. JSONB parses the JSON and stores it in a binary format that removes extra spaces and orders keys for faster access.

Result

You can choose between storing raw text or a processed binary form of JSON.

Knowing there are two storage methods sets the stage for understanding their tradeoffs.

3

Intermediate

Performance Differences Between JSON and JSONB

🤔Before reading on: do you think JSON or JSONB is faster for searching data? Commit to your answer.

Concept: JSONB is faster for querying because it stores data in a binary format optimized for search and indexing.

When you query JSON data, JSONB can use indexes and quickly find keys or values. JSON requires scanning the whole text, which is slower. For example, searching for a key in JSONB is much faster than in JSON.

Result

Queries on JSONB run faster, especially on large datasets.

Understanding that binary storage enables indexing explains why JSONB is preferred for performance-critical applications.

4

Intermediate

Differences in Data Storage and Size

🤔Before reading on: do you think JSONB always uses more or less disk space than JSON? Commit to your answer.

Concept: JSONB usually uses more disk space because it stores parsed data and indexes, but it can be more efficient for repeated queries.

JSON stores data exactly as text, so it can be smaller if the JSON is simple. JSONB stores extra metadata and orders keys, which can increase size. However, JSONB's structure speeds up access and reduces CPU work during queries.

Result

JSONB trades some storage space for faster query performance.

Knowing the storage tradeoff helps decide which type fits your needs: smaller size or faster queries.

5

Intermediate

Differences in Data Preservation

Concept: JSON preserves the exact input text, while JSONB normalizes data by removing whitespace and reordering keys.

If you insert {"a":1, "b":2} as JSON, it stays exactly like that. JSONB might store it as {"a":1, "b":2} without spaces. This means JSONB does not preserve formatting or key order.

Result

JSON is better if you need to keep the original JSON text unchanged.

Understanding this helps avoid surprises when formatting matters, like in logs or APIs.

6

Advanced

Indexing Capabilities of JSONB

🤔Before reading on: can you create indexes on JSON data stored as JSON? Commit to your answer.

Concept: JSONB supports indexing on keys and values, enabling fast lookups, while JSON does not support indexing.

PostgreSQL allows creating GIN or BTREE indexes on JSONB columns. This means queries filtering by JSON keys or values can use indexes and run quickly. JSON columns cannot use these indexes and require full scans.

Result

JSONB queries with indexes are much faster and scalable.

Knowing JSONB supports indexing is key to building efficient applications with JSON data.

7

Expert

Internal Parsing and Query Execution Differences

🤔Before reading on: do you think JSONB parsing happens once or every time you query? Commit to your answer.

Concept: JSONB parses and stores data once at insert time, while JSON parses text every time you query.

When you insert JSONB data, PostgreSQL parses it into a binary tree and stores it. Queries then operate on this binary form directly. For JSON, the text is parsed on each query, which adds overhead. This difference affects CPU usage and query speed.

Result

JSONB reduces repeated parsing costs, improving query efficiency.

Understanding when parsing happens clarifies why JSONB is better for frequent queries.

Under the Hood

JSON stores data as plain text exactly as input, so each query must parse this text to understand the structure. JSONB converts JSON into a binary tree structure at insert time, storing keys and values in a format optimized for quick access and indexing. This binary form allows PostgreSQL to skip parsing on queries and use indexes efficiently.

Why designed this way?

Originally, JSON was stored as text for simplicity and compatibility. As JSON usage grew, performance became critical, so JSONB was introduced to speed up queries and support indexing. The tradeoff was losing exact text preservation for faster access and smaller CPU load during queries.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Insert JSON │──────▶│ Store as Text │──────▶│ Parse on Query│
│   Data       │       │ (JSON type)   │       │ (Slow)        │
└───────────────┘       └───────────────┘       └───────────────┘

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Insert JSON │──────▶│ Parse & Store │──────▶│ Query Binary  │
│   Data       │       │ as Binary     │       │ Data Directly │
│               │       │ (JSONB type)  │       │ (Fast)        │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does JSONB preserve the exact text formatting of JSON input? Commit yes or no.

Common Belief: JSONB stores JSON exactly as you input it, preserving spaces and key order.

Reality: JSONB normalizes JSON by removing whitespace and reordering keys alphabetically.

Why it matters: Expecting exact formatting can cause confusion when JSONB output looks different, affecting applications relying on text format.

Quick: Can you create indexes on JSON columns? Commit yes or no.

Common Belief: You can create indexes on both JSON and JSONB columns equally.

Reality: Only JSONB supports indexing; JSON columns require full scans for queries.

Why it matters: Using JSON without indexing leads to slow queries on large datasets.

Quick: Is JSONB always smaller in storage size than JSON? Commit yes or no.

Common Belief: JSONB always uses less disk space than JSON because it's binary.

Reality: JSONB can use more space due to metadata and key ordering, though it speeds up queries.

Why it matters: Choosing JSONB expecting smaller size might lead to unexpected storage costs.

Quick: Does JSONB parsing happen at query time? Commit yes or no.

Common Belief: JSONB parses JSON text every time you query it, just like JSON.

Reality: JSONB parses JSON once at insert time and stores binary data for fast queries.

Why it matters: Misunderstanding this leads to wrong assumptions about CPU usage and query speed.

Expert Zone

1

JSONB's key ordering means that logically equivalent JSON objects with different key orders are stored identically, enabling better caching and indexing.

2

JSONB supports partial updates using functions like jsonb_set, which is not possible with plain JSON, allowing more efficient data modifications.

3

The choice between JSON and JSONB can affect replication and WAL (Write-Ahead Logging) size due to differences in storage format and update patterns.

When NOT to use

Avoid JSONB when you need to preserve exact JSON text formatting or key order, such as for logging or audit trails. Use plain JSON in these cases. Also, if your workload rarely queries JSON data and mostly stores it, JSON might be simpler.

Production Patterns

In production, JSONB is used for APIs and apps needing fast JSON queries and indexing, like filtering by keys or values. JSON is used when storing raw JSON documents for later processing or when exact text preservation is required. Indexes on JSONB columns are common for performance.

Connections

NoSQL Document Stores

JSONB in PostgreSQL provides similar capabilities to document stores like MongoDB by storing and indexing JSON data efficiently.

Understanding JSONB helps bridge relational and document database concepts, showing how relational databases can handle flexible schemas.

Data Serialization Formats

JSONB is a binary serialization of JSON, similar to how formats like Protocol Buffers or MessagePack serialize data for efficiency.

Knowing JSONB as a binary format clarifies why it is faster and more compact for querying than plain text JSON.

Compiler Optimization

JSONB's parsing at insert time and storing optimized binary data is like how compilers parse source code once and generate efficient machine code for repeated execution.

This connection shows how preprocessing data upfront can greatly improve runtime performance.

Common Pitfalls

#1 Using JSON type when you need fast queries and indexing.

Wrong approach:

CREATE TABLE data (info JSON);
SELECT * FROM data WHERE info->>'name' = 'Alice';

Correct approach:

CREATE TABLE data (info JSONB);
CREATE INDEX idx_name ON data USING GIN (info jsonb_path_ops);
SELECT * FROM data WHERE info->>'name' = 'Alice';

Root cause:

Not knowing JSON does not support indexing, leading to slow queries on large data.

#2 Expecting JSONB to preserve input formatting and key order.

Wrong approach:

INSERT INTO data (info) VALUES ('{"b":2, "a":1}');
SELECT info FROM data;

Correct approach:

Use JSON type if exact formatting matters:
INSERT INTO data (info) VALUES ('{"b":2, "a":1}'::json);

Root cause:

Misunderstanding that JSONB normalizes JSON structure, losing original formatting.

#3 Assuming JSONB always saves disk space compared to JSON.

Wrong approach:

Choosing JSONB solely to reduce storage without testing size impact.

Correct approach:

Test storage size for your data; use JSON if size is critical and queries are rare.

Root cause:

Believing binary format always means smaller size, ignoring metadata overhead.

Key Takeaways

JSON stores data as plain text preserving exact input, while JSONB stores data in a binary format optimized for fast querying and indexing.

JSONB supports indexing, making queries on JSON data much faster than JSON, which requires full scans.

JSONB normalizes JSON by removing whitespace and reordering keys, so it does not preserve original formatting.

Choosing between JSON and JSONB depends on whether you prioritize query speed and indexing or exact text preservation.

Understanding JSONB's internal binary storage and parsing at insert time explains its performance advantages over JSON.

Practice

1. Which of the following is a key difference between json and jsonb data types in PostgreSQL?

easy

A. json stores data as plain text, while jsonb stores data in a binary format.

B. jsonb preserves whitespace and formatting, json does not.

C. json supports indexing, jsonb does not.

D. jsonb cannot store nested objects, json can.

Solution

1. Step 1: Understand storage formats

   json stores JSON data as plain text, preserving the original formatting including whitespace.

2. Step 2: Understand jsonb storage

   jsonb stores JSON data in a binary format optimized for faster processing and indexing.

3. Final Answer:

   json stores data as plain text, while jsonb stores data in a binary format. -> Option A

4. Quick Check:

   Storage format difference = Option A [OK]

Hint: Remember: json = text, jsonb = binary for speed [OK]

Common Mistakes:

• Thinking jsonb preserves whitespace like json
• Assuming jsonb cannot index data
• Believing jsonb cannot store nested objects

2. Which of the following is the correct way to declare a column with jsonb type in PostgreSQL?

easy

A. CREATE TABLE data (info JSONB);

B. CREATE TABLE data (info JSONB[]);

C. CREATE TABLE data (info JSON);

D. CREATE TABLE data (info BINARYJSON);

Solution

1. Step 1: Review PostgreSQL syntax for JSONB

   The correct data type name is jsonb (case-insensitive), used directly as a column type.

2. Step 2: Check each option

   Option A uses JSONB correctly. Option A tries to declare an array of jsonb, which is valid but not the simplest declaration. Option A uses JSON type, not jsonb. Option A uses a non-existent type BINARYJSON.

3. Final Answer:

   CREATE TABLE data (info JSONB); -> Option A

4. Quick Check:

   Correct jsonb column syntax = Option A [OK]

Hint: Use JSONB directly as column type, no extra keywords [OK]

Common Mistakes:

• Confusing JSON and JSONB types
• Using invalid type names like BINARYJSON
• Adding unnecessary array brackets

3. Given the table items with a json column data storing '{"a":1, "b":2}', what will this query return?

SELECT data @> '{"a":1}'::jsonb FROM items;

medium

A. Error because data is json, but query uses jsonb operator

B. True for rows where data contains key "a" with value 1

C. False for all rows because data is json, not jsonb

D. Returns all rows regardless of content

Solution

1. Step 1: Understand operator compatibility

   The @> operator is defined for jsonb types, not for json. Using it on a json column causes an error.

2. Step 2: Analyze the query

   The query tries to use @> on data which is json, but the right side is cast to jsonb. This mismatch causes a type error.

3. Final Answer:

   Error because data is json, but query uses jsonb operator -> Option A

4. Quick Check:

   jsonb operator on json column = Error [OK]

Hint: jsonb operators need jsonb columns, not json [OK]

Common Mistakes:

• Assuming json and jsonb are interchangeable
• Ignoring type casting errors
• Expecting operator to work on json type

4. You have a table with a jsonb column but your query is slow when filtering by keys. What is the most likely cause?

medium

A. No GIN or BTREE index on the jsonb column

B. Using json type instead of jsonb

C. The JSON data is stored as plain text

D. The query uses LIKE operator on the jsonb column

Solution

1. Step 1: Understand indexing for jsonb

   jsonb supports GIN or BTREE indexes to speed up key/value lookups.

2. Step 2: Identify cause of slow queries

   If no index exists on the jsonb column, queries filtering by keys must scan all rows, causing slowness.

3. Final Answer:

   No GIN or BTREE index on the jsonb column -> Option A

4. Quick Check:

   Missing index on jsonb = slow queries [OK]

Hint: Add GIN index on jsonb for fast key lookups [OK]

Common Mistakes:

• Confusing json and jsonb types
• Using LIKE on jsonb instead of jsonb operators
• Ignoring indexing for jsonb columns

5. You want to store JSON data and frequently query nested keys with fast performance. Which approach is best?

hard

A. Store data as json and create expression indexes on keys

B. Store data as jsonb and create a GIN index on the column

C. Store data as text and parse JSON in application code

D. Store data as jsonb but avoid indexes for faster inserts

Solution

1. Step 1: Choose data type for fast queries

   jsonb is optimized for querying and indexing nested JSON data.

2. Step 2: Use appropriate indexing

   Creating a GIN index on the jsonb column enables fast key and path lookups.

3. Final Answer:

   Store data as jsonb and create a GIN index on the column -> Option B

4. Quick Check:

   jsonb + GIN index = best for fast nested queries [OK]

Hint: Use jsonb with GIN index for fast nested JSON queries [OK]

Common Mistakes:

• Using json type which is slower for queries
• Avoiding indexes to speed inserts but hurting queries
• Parsing JSON outside database causing overhead