JSON data type in MySQL - Step-by-Step Execution

Concept Flow - JSON data type

Start: Insert JSON data

↓

Validate JSON format

Yes↓

Store JSON in column

↓

Query JSON data

↓

Extract or modify JSON parts

↓

Return results

↓

End

This flow shows how JSON data is inserted, validated, stored, queried, and returned in MySQL.

Execution Sample

MySQL

CREATE TABLE users (
  id INT PRIMARY KEY,
  info JSON
);

INSERT INTO users VALUES (1, '{"name": "Alice", "age": 30}');

SELECT info->>'$.name' AS name FROM users WHERE id=1;

This code creates a table with a JSON column, inserts JSON data, and queries a value from the JSON.

Execution Table

Step	Action	Input/Condition	Result/Output
1	Create table with JSON column	Define 'info' as JSON	Table 'users' created with 'info' column as JSON
2	Insert JSON data	Insert (1, '{"name": "Alice", "age": 30}')	Row inserted with valid JSON stored in 'info'
3	Validate JSON	Check '{"name": "Alice", "age": 30}'	Valid JSON format confirmed
4	Query JSON data	SELECT info->>'$.name' FROM users WHERE id=1	Returns 'Alice'
5	Return result	Output query result	Result set with one row: name = 'Alice'
6	End	No more steps	Execution complete

💡 Execution stops after query returns the JSON value 'Alice' successfully.

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
users table	empty	1 row with id=1, info={"name": "Alice", "age": 30}	unchanged	unchanged
info column (row 1)	null	{"name": "Alice", "age": 30}	{"name": "Alice", "age": 30}	{"name": "Alice", "age": 30}
query result	none	none	'Alice'	'Alice'

Key Moments - 3 Insights

Why does the JSON data need to be valid before insertion?

Because MySQL checks JSON format on insert (see Step 3). Invalid JSON causes an error and prevents storing.

How do we extract a value from JSON in a query?

Using the ->> operator with a JSON path (see Step 4), which returns the value as text.

Can we store any text in a JSON column?

No, only valid JSON text is accepted. Plain text without JSON structure will cause an error.

Visual Quiz - 3 Questions

Look at the execution table, what is the output of the query at Step 4?

A 30

B '{"name": "Alice", "age": 30}'

C 'Alice'

D NULL

💡 Hint

Check the 'Result/Output' column at Step 4 in the execution_table.

At which step does MySQL confirm the JSON data is valid?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the step mentioning 'Validate JSON format' in the execution_table.

If the JSON data was invalid, what would happen during insertion?

A Insertion fails with an error

B The row inserts with NULL in JSON column

C MySQL stores it as plain text

D MySQL converts it to valid JSON automatically

💡 Hint

Refer to the key moment about JSON validation and insertion errors.

Concept Snapshot

JSON data type in MySQL:
- Use JSON column to store JSON formatted text
- MySQL validates JSON on insert
- Query JSON using -> or ->> operators
- Extract parts with JSON path expressions
- Invalid JSON causes insert error

Full Transcript

This visual execution shows how MySQL handles the JSON data type. First, a table is created with a JSON column. When inserting data, MySQL checks if the JSON text is valid. If valid, it stores the JSON in the column. Later, queries can extract values from the JSON using operators like ->>. The example inserts a JSON object with name and age, then queries the name value, returning 'Alice'. Invalid JSON would cause an error and stop insertion. This step-by-step trace helps beginners see how JSON data is stored and accessed in MySQL.

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