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

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

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

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

Hint

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

Practice

1. What is the main advantage of using the JSON data type in MySQL?

easy

A. It allows storing flexible, structured data in a single column.

B. It automatically creates indexes for faster queries.

C. It restricts data to fixed column types only.

D. It converts JSON data to XML format internally.

Solution

1. Step 1: Understand JSON data type purpose

   The JSON data type is designed to store flexible and structured data inside one column, unlike fixed columns.

2. Step 2: Compare options

   It allows storing flexible, structured data in a single column. correctly states the main advantage. Options B, C, and D are incorrect because JSON does not automatically create indexes, does not restrict data to fixed types, and does not convert JSON to XML.

3. Final Answer:

   It allows storing flexible, structured data in a single column. -> Option A

4. Quick Check:

   JSON stores flexible data = A [OK]

Hint: JSON type stores flexible data in one column [OK]

Common Mistakes:

• Thinking JSON restricts data to fixed types
• Assuming JSON auto-indexes all data
• Confusing JSON with XML conversion

2. Which of the following is the correct way to declare a column with JSON data type in MySQL?

easy

A. CREATE TABLE t1 (data TEXT JSON);

B. CREATE TABLE t1 (data VARCHAR(255));

C. CREATE TABLE t1 (data JSON);

D. CREATE TABLE t1 (data JSONB);

Solution

1. Step 1: Recall JSON column declaration syntax

   In MySQL, the JSON data type is declared simply as JSON without extra modifiers.

2. Step 2: Evaluate options

   CREATE TABLE t1 (data JSON); uses correct syntax: data JSON. CREATE TABLE t1 (data VARCHAR(255)); uses VARCHAR, not JSON. CREATE TABLE t1 (data TEXT JSON); mixes TEXT and JSON incorrectly. CREATE TABLE t1 (data JSONB); uses JSONB which is PostgreSQL-specific, not MySQL.

3. Final Answer:

   CREATE TABLE t1 (data JSON); -> Option C

4. Quick Check:

   MySQL JSON column = data JSON [OK]

Hint: Use JSON keyword alone to declare JSON column [OK]

Common Mistakes:

• Using VARCHAR or TEXT instead of JSON
• Adding unsupported modifiers like JSONB
• Mixing JSON with other types in declaration

3. Given the table users with a JSON column info storing {"age": 30, "city": "NY"}, what will this query return?

SELECT JSON_EXTRACT(info, '$.city') FROM users WHERE id = 1;

medium

A. "NY"

B. NY

C. city

D. NULL

Solution

1. Step 1: Understand JSON_EXTRACT function

   JSON_EXTRACT returns the JSON value at the specified path, including quotes for strings.

2. Step 2: Apply to given data

   The path '$.city' points to the string "NY". The function returns it with quotes, so the result is "NY".

3. Final Answer:

   "NY" -> Option A

4. Quick Check:

   JSON_EXTRACT returns quoted string = "NY" [OK]

Hint: JSON_EXTRACT returns JSON value with quotes for strings [OK]

Common Mistakes:

• Expecting unquoted string without quotes
• Confusing key name with value
• Assuming NULL if path exists

4. What is wrong with this query?

SELECT info->'$.age' FROM users;

Assuming info is a JSON column.

medium

A. The operator '->' is invalid in MySQL.

B. The query is correct and will return the age as a number.

C. The path syntax '$.age' is incorrect.

D. The operator '->' returns JSON, but the query expects plain text.

Solution

1. Step 1: Verify operator syntax

   The '->' operator is valid in MySQL for JSON column extraction.

2. Step 2: Verify path syntax

   The path '$.age' is correct for accessing the 'age' key.

3. Step 3: Verify output

   For a numeric age like 30, it returns 30 as a number, so the query is correct.

4. Final Answer:

   The query is correct and will return the age as a number. -> Option B

5. Quick Check:

   '->' valid, returns age as number [OK]

Hint: MySQL '->' extracts JSON as typed value [OK]

Common Mistakes:

• Thinking '->' is invalid syntax
• Confusing JSON and text return types
• Using wrong path syntax

5. You want to find all users whose JSON info column contains an age greater than 25. Which query correctly does this?

hard

A. SELECT * FROM users WHERE JSON_EXTRACT(info, 'age') > 25;

B. SELECT * FROM users WHERE info->>'age' > 25;

C. SELECT * FROM users WHERE info->'age' > 25;

D. SELECT * FROM users WHERE CAST(JSON_EXTRACT(info, '$.age') AS UNSIGNED) > 25;

Solution

1. Step 1: Understand JSON_EXTRACT returns JSON value

   JSON_EXTRACT returns a JSON value (e.g., '30'), which is a string with quotes, so direct comparison fails.

2. Step 2: Use CAST to convert JSON string to number

   CAST(... AS UNSIGNED) converts the extracted JSON string to a number for numeric comparison.

3. Step 3: Evaluate other options

   SELECT * FROM users WHERE JSON_EXTRACT(info, '$.age') > 25; compares JSON string directly, causing wrong results. SELECT * FROM users WHERE info->>'$.age' > 25; uses '->>' which returns text, but MySQL may treat it as string, not number. SELECT * FROM users WHERE info->'$.age' > 25; uses '->' which returns JSON, not suitable for numeric comparison.

4. Final Answer:

   SELECT * FROM users WHERE CAST(JSON_EXTRACT(info, '$.age') AS UNSIGNED) > 25; -> Option D

5. Quick Check:

   Cast JSON to number before comparison [OK]

Hint: Cast JSON extract to number for numeric comparisons [OK]

Common Mistakes:

• Comparing JSON strings directly to numbers
• Using '->' operator without casting
• Assuming '->>' always returns number