JSON vs JSONB differences in PostgreSQL - Hands-On Comparison

Understanding JSON vs JSONB Differences in PostgreSQL

📖 Scenario: You are working with a PostgreSQL database that stores user profile data in JSON format. You want to understand the differences between the json and jsonb data types to choose the best one for your application.

🎯 Goal: Build a simple PostgreSQL table using both json and jsonb columns, insert sample data, and write queries to observe how they behave differently.

📋 What You'll Learn

Create a table named user_profiles with two columns: profile_json of type json and profile_jsonb of type jsonb.

Insert the same JSON data into both columns for a user.

Write a query to select and compare the stored data from both columns.

Write a query to demonstrate how jsonb supports indexing or faster querying compared to json.

💡 Why This Matters

🌍 Real World

Many applications store flexible data structures in PostgreSQL using JSON types. Choosing between json and jsonb affects performance and functionality.

💼 Career

Understanding json vs jsonb is important for database developers and backend engineers working with PostgreSQL to optimize data storage and querying.

1

Create the user_profiles table with json and jsonb columns

Write a SQL statement to create a table called user_profiles with two columns: profile_json of type json and profile_jsonb of type jsonb.

PostgreSQL

-- Create the user_profiles table with profile_json and profile_jsonb columns
-- Your code here

Hint

Use CREATE TABLE with columns named exactly profile_json and profile_jsonb and types json and jsonb.

2

Insert the same JSON data into both json and jsonb columns

Insert a row into user_profiles with profile_json and profile_jsonb both set to the JSON value '{"name": "Alice", "age": 30, "city": "New York"}'.

PostgreSQL

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile_json JSON,
    profile_jsonb JSONB
);
-- Insert JSON data into both columns
-- Your code here

Hint

Use INSERT INTO user_profiles (profile_json, profile_jsonb) VALUES (...) with the exact JSON string for both columns.

3

Select and compare data from json and jsonb columns

Write a SQL query to select profile_json and profile_jsonb from user_profiles to see how the data is stored.

PostgreSQL

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile_json JSON,
    profile_jsonb JSONB
);

INSERT INTO user_profiles (profile_json, profile_jsonb) VALUES
('{"name": "Alice", "age": 30, "city": "New York"}', '{"name": "Alice", "age": 30, "city": "New York"}');

-- Select profile_json and profile_jsonb columns
-- Your code here

Hint

Use SELECT profile_json, profile_jsonb FROM user_profiles; to see both columns side by side.

4

Create an index on the jsonb column and query using a key

Create a GIN index on the profile_jsonb column. Then write a query to select rows where the name key in profile_jsonb equals 'Alice'.

PostgreSQL

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile_json JSON,
    profile_jsonb JSONB
);

INSERT INTO user_profiles (profile_json, profile_jsonb) VALUES
('{"name": "Alice", "age": 30, "city": "New York"}', '{"name": "Alice", "age": 30, "city": "New York"}');

SELECT profile_json, profile_jsonb FROM user_profiles;

-- Create a GIN index on profile_jsonb
-- Query rows where name = 'Alice' in profile_jsonb
-- Your code here

Hint

Use CREATE INDEX idx_profile_jsonb ON user_profiles USING GIN (profile_jsonb); to create the index. Then query with profile_jsonb ->> 'name' = 'Alice'.

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