Creating JSON columns in PostgreSQL - Try It Yourself

Creating JSON Columns in PostgreSQL

📖 Scenario: You are working for a small online store that wants to store extra product details flexibly. Instead of adding many fixed columns, you will use a JSON column to hold these details.

🎯 Goal: Create a PostgreSQL table with a JSON column to store product details, add a configuration for default JSON data, insert product records using the JSON column, and finally add a constraint to ensure the JSON column is not empty.

📋 What You'll Learn

Create a table named products with columns id (integer primary key), name (text), and details (JSON).

Create a variable or setting for default JSON details with keys color and size.

Insert at least two products into the products table using the JSON details.

Add a check constraint to ensure the details JSON column is not empty.

💡 Why This Matters

🌍 Real World

Many modern applications store flexible data like product attributes or user preferences in JSON columns to avoid frequent schema changes.

💼 Career

Knowing how to create and manage JSON columns in databases is valuable for backend developers and data engineers working with semi-structured data.

1

Create the products table with a JSON column

Write a SQL statement to create a table called products with three columns: id as an integer primary key, name as text, and details as a JSON column.

PostgreSQL

-- Create the products table with id, name, and details columns
-- Your code here

Hint

Use CREATE TABLE with the column details typed as JSON.

2

Define default JSON details

Create a SQL variable or use a WITH clause named default_details that holds the JSON object {"color": "red", "size": "medium"}.

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    details JSON
);

-- Define default_details JSON object
-- Your code here

Hint

Use a WITH clause or a variable holding the JSON string cast to json.

3

Insert products using the JSON details

Insert two rows into the products table with id values 1 and 2, name values 'T-shirt' and 'Mug', and use the JSON object {"color": "red", "size": "medium"} for the details column.

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    details JSON
);

WITH default_details AS (
    SELECT '{"color": "red", "size": "medium"}'::json AS details
)

-- Insert two products using the JSON details
-- Your code here

Hint

Use INSERT INTO products (id, name, details) VALUES with the JSON string for details.

4

Add a check constraint to ensure details is not empty

Alter the products table to add a check constraint named details_not_empty that ensures the details JSON column is not an empty JSON object (i.e., it must have at least one key).

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    details JSON
);

WITH default_details AS (
    SELECT '{"color": "red", "size": "medium"}'::json AS details
)
INSERT INTO products (id, name, details) VALUES
(1, 'T-shirt', '{"color": "red", "size": "medium"}'),
(2, 'Mug', '{"color": "red", "size": "medium"}');

-- Add a check constraint to ensure details JSON is not empty
-- Your code here

Hint

Use ALTER TABLE with ADD CONSTRAINT and jsonb_object_length(details::jsonb) > 0 to check JSON is not empty.

Practice

1. What is the main purpose of creating a JSON column in a PostgreSQL table?

easy

A. To enforce strict relational data types

B. To create indexes automatically

C. To speed up numeric calculations

D. To store flexible, structured data inside a single column

Solution

1. Step 1: Understand JSON column usage

   JSON columns allow storing data that can have varying structure, unlike fixed columns.

2. Step 2: Compare with other options

   Options A, C, and D do not describe JSON columns' purpose; they relate to other database features.

3. Final Answer:

   To store flexible, structured data inside a single column -> Option D

4. Quick Check:

   JSON columns = flexible data storage [OK]

Hint: JSON columns store flexible data, not fixed types [OK]

Common Mistakes:

• Thinking JSON columns enforce strict types
• Confusing JSON with indexing features
• Assuming JSON speeds numeric calculations

2. Which of the following is the correct syntax to create a table with a JSON column named data?

easy

A. CREATE TABLE mytable (id INT, data JSONB STRING);

B. CREATE TABLE mytable (id INT, data JSON);

C. CREATE TABLE mytable (id INT, data TEXT JSON);

D. CREATE TABLE mytable (id INT, data STRING);

Solution

1. Step 1: Recall JSON column syntax

   In PostgreSQL, JSON columns are declared with type JSON or JSONB directly.

2. Step 2: Check each option

   CREATE TABLE mytable (id INT, data JSON); uses correct syntax. Options A, B, and C use invalid or mixed types.

3. Final Answer:

   CREATE TABLE mytable (id INT, data JSON); -> Option B

4. Quick Check:

   JSON column syntax = CREATE TABLE mytable (id INT, data JSON); [OK]

Hint: Use JSON or JSONB as column type directly [OK]

Common Mistakes:

• Using STRING or TEXT with JSON incorrectly
• Combining JSON with other types in declaration
• Missing commas or parentheses

3. Given the table created as CREATE TABLE products (id SERIAL, info JSONB); and the insert statement:
INSERT INTO products (info) VALUES ('{"name": "Pen", "price": 1.5}');
What will be the result of the query:
SELECT info->>'name' FROM products WHERE id = 1;

medium

A. {"name": "Pen", "price": 1.5}

B. 1.5

C. Pen

D. NULL

Solution

1. Step 1: Understand JSONB insert and query

   The JSONB column stores the JSON object. The operator ->> extracts the text value of the key.

2. Step 2: Analyze the query

   The query selects the 'name' field as text from the JSONB column for id 1, which is 'Pen'.

3. Final Answer:

   Pen -> Option C

4. Quick Check:

   info->>'name' = 'Pen' [OK]

Hint: Use ->> to get JSON text value by key [OK]

Common Mistakes:

• Using -> instead of ->> returns JSON, not text
• Expecting full JSON instead of single value
• Not matching the correct id in WHERE clause

4. You try to create a table with a JSON column using:
CREATE TABLE users (id INT, profile JSONB DEFAULT {});
But get a syntax error. What is the problem?

medium

A. Missing quotes around the default JSON value

B. JSONB columns cannot have default values

C. Empty braces {} are not valid default for JSONB

D. INT type cannot be used with JSONB columns

Solution

1. Step 1: Check default value syntax for JSONB

   Default JSON values must be valid JSON strings, so '{}' must be in single quotes.

2. Step 2: Identify the error cause

   Using {} without quotes causes syntax error. Correct is DEFAULT '{}'.

3. Final Answer:

   Missing quotes around the default JSON value -> Option A

4. Quick Check:

   Default JSON must be quoted [OK]

Hint: Always quote JSON default values as strings [OK]

Common Mistakes:

• Using unquoted braces {} as default
• Assuming JSONB cannot have defaults
• Confusing data types in table definition

5. You want to create a table orders with a JSONB column details that stores order info. You also want to ensure each order has a customer_id and a details JSON with a total field as a number. Which approach correctly creates the table and enforces the total field presence?

hard

A. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details->>'total' IS NOT NULL));

B. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, details JSONB DEFAULT '{}');

C. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL);

D. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details ? 'total'));

Solution

1. Step 1: Understand JSONB constraints

   To ensure 'total' key exists and is not null, use a CHECK with details->>'total' IS NOT NULL.

2. Step 2: Evaluate options

   CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details ? 'total')); checks only key presence (details ? 'total'), but does not check value is not null. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details->>'total' IS NOT NULL)); checks both key and value presence correctly.

3. Final Answer:

   CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details->>'total' IS NOT NULL)); -> Option A

4. Quick Check:

   CHECK ensures 'total' key with value [OK]

Hint: Use CHECK with details->>'key' IS NOT NULL to enforce JSON key presence [OK]

Common Mistakes:

• Using ? operator without checking value
• Not adding NOT NULL constraints
• Setting default empty JSON without validation