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

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

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

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

Need a hint?

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