JSON data type in MySQL - Mini Project: Build & Apply

Working with JSON Data Type in MySQL

📖 Scenario: You are managing a small online store database. You want to store product details including name, price, and tags in a flexible way using MySQL's JSON data type.

🎯 Goal: Create a table with a JSON column to store product details, insert sample data, query JSON fields, and update JSON content.

📋 What You'll Learn

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

Insert three products with JSON details including name, price, and tags (an array).

Write a query to select product name and price from the JSON details column.

Update the tags array for a product by adding a new tag.

💡 Why This Matters

🌍 Real World

Storing flexible product information in JSON format allows easy updates and additions without changing table structure.

💼 Career

Many modern applications use JSON columns in databases to handle semi-structured data efficiently.

1

Create the products table with a JSON column

Write a SQL statement to create a table called products with an id column as an integer primary key and a details column of type JSON.

MySQL

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

Need a hint?

Use CREATE TABLE with id INT PRIMARY KEY and details JSON columns.

2

Insert three products with JSON details

Insert three rows into products with id values 1, 2, and 3. For each, insert JSON data into details with keys name, price, and tags (an array). Use these exact values: Product 1: name 'Pen', price 1.5, tags ['stationery', 'writing']; Product 2: name 'Notebook', price 3.0, tags ['stationery', 'paper']; Product 3: name 'Mug', price 5.0, tags ['kitchen', 'drinkware'].

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  details JSON
);
-- Insert three products with JSON details
-- Your code here

Need a hint?

Use INSERT INTO products (id, details) VALUES with JSON strings for details.

3

Query product name and price from JSON details

Write a SQL query to select the id, the name, and the price from the details JSON column in the products table. Use JSON_EXTRACT or the arrow operator ->> to get the values as text.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  details JSON
);

INSERT INTO products (id, details) VALUES
(1, '{"name": "Pen", "price": 1.5, "tags": ["stationery", "writing"]}'),
(2, '{"name": "Notebook", "price": 3.0, "tags": ["stationery", "paper"]}'),
(3, '{"name": "Mug", "price": 5.0, "tags": ["kitchen", "drinkware"]}');

-- Select id, name, and price from JSON details
-- Your code here

Need a hint?

Use details->>'$.name' and details->>'$.price' to extract JSON values as text.

4

Update the tags array for a product

Write a SQL statement to update the tags array inside the details JSON column for the product with id = 1. Add a new tag "office" to the existing tags array using JSON_ARRAY_APPEND.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  details JSON
);

INSERT INTO products (id, details) VALUES
(1, '{"name": "Pen", "price": 1.5, "tags": ["stationery", "writing"]}'),
(2, '{"name": "Notebook", "price": 3.0, "tags": ["stationery", "paper"]}'),
(3, '{"name": "Mug", "price": 5.0, "tags": ["kitchen", "drinkware"]}');

SELECT id, details->>'$.name' AS name, details->>'$.price' AS price FROM products;

-- Update tags array for product id 1 to add "office"
-- Your code here

Need a hint?

Use JSON_ARRAY_APPEND(details, '$.tags', '"office"') to add a tag.