JSON vs JSONB differences in PostgreSQL - Visual Side-by-Side Comparison

Concept Flow - JSON vs JSONB differences

Store JSON as text

↓

Parse on read

↓

Slower reads

↓

Store JSONB as binary

↓

Parse on write

↓

Faster reads

↓

Supports indexing

JSON stores data as plain text and parses it when reading, while JSONB stores data in a binary format parsed on write, enabling faster reads and indexing.

Execution Sample

PostgreSQL

CREATE TABLE example (
  data_json JSON,
  data_jsonb JSONB
);

INSERT INTO example VALUES
('{"name": "Alice", "age": 30}', '{"name": "Alice", "age": 30}');

Creates a table with JSON and JSONB columns and inserts the same JSON data into both.

Execution Table

Step	Action	Data Stored	Parsing Time	Index Support	Query Speed
1	Insert JSON data	Text format	No parsing now	No	N/A
2	Insert JSONB data	Binary format	Parsing done now	Yes	N/A
3	Select JSON data	Text format	Parsing on read	No	Slower
4	Select JSONB data	Binary format	No parsing on read	Yes	Faster
5	Create index on JSONB	Binary format	N/A	Yes	Faster queries
6	Create index on JSON	Text format	N/A	No	No speedup
7	Query JSONB with index	Binary format	N/A	Yes	Fast
8	Query JSON without index	Text format	Parsing on read	No	Slow
9	End of demo	-	-	-	-

💡 Demonstrated JSON stores text parsed on read, JSONB stores binary parsed on write with indexing and faster queries.

Variable Tracker

Variable	Start	After Insert	After Select	After Index	Final
data_json	empty	text JSON stored	parsed on read	no index	slower queries
data_jsonb	empty	binary JSONB stored	no parse on read	index created	faster queries

Key Moments - 3 Insights

Why is JSON slower to query than JSONB?

Because JSON stores data as plain text and parses it every time you read it (see execution_table rows 3 and 8), while JSONB parses once on insert and stores in binary for faster access.

Can you create indexes on JSON columns?

No, JSON columns do not support indexing (execution_table rows 5 and 6). Only JSONB supports indexes, which speeds up queries.

Does JSONB store data exactly like JSON text?

No, JSONB stores data in a binary format that is not text but represents the same data structure, enabling faster operations (execution_table rows 2 and 4).

Visual Quiz - 3 Questions

Look at the execution_table, at which step is JSONB data parsed?

A Step 3 - Select JSON data

B Step 1 - Insert JSON data

C Step 2 - Insert JSONB data

D Step 4 - Select JSONB data

💡 Hint

Check the 'Parsing Time' column for JSONB insert step in execution_table row 2.

According to variable_tracker, what happens to data_jsonb after index creation?

A It remains unindexed and slow

B An index is created and queries become faster

C It is parsed on every read

D It converts back to text format

💡 Hint

Look at the 'After Index' and 'Final' columns for data_jsonb in variable_tracker.

From the execution_table, why is querying JSON slower than JSONB?

A Because JSON is parsed on read every time

B Because JSON supports indexing

C Because JSON is stored in binary

D Because JSONB stores text

💡 Hint

See the 'Parsing Time' and 'Query Speed' columns for JSON in execution_table rows 3 and 8.

Concept Snapshot

JSON stores data as plain text and parses it on every read.
JSONB stores data in a binary format parsed once on write.
JSONB supports indexing, making queries faster.
Use JSONB for better performance and indexing.
Use JSON if you need exact text preservation.

Full Transcript

This visual execution compares JSON and JSONB in PostgreSQL. JSON stores data as text and parses it each time you read it, which slows queries. JSONB stores data in a binary format parsed once when inserted, enabling faster reads and supporting indexes. The execution table shows steps inserting and selecting data, highlighting parsing times and indexing support. Variable tracking shows how data_json remains text and unindexed, while data_jsonb is binary and indexed. Key moments clarify why JSON is slower, why JSONB supports indexes, and how JSONB stores data differently. The quiz tests understanding of parsing steps, indexing effects, and query speed differences. The snapshot summarizes key points: JSON is text and slower, JSONB is binary, indexed, and faster. Choose JSONB for performance and indexing, JSON for exact text needs.

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