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

Need a 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

Need a 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

Need a 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

Need a 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'.