JSON data type in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with a JSON column named 'data'.

MySQL

CREATE TABLE users (id INT PRIMARY KEY, data [1]);

Drag options to blanks, or click blank then click option'

AJSON

BVARCHAR(255)

CTEXT

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR or TEXT instead of JSON for JSON data.

Using INT which is for numbers only.

The JSON data type allows storing JSON documents directly in MySQL.

2fill in blank

medium

Complete the query to insert a JSON object into the 'data' column.

MySQL

INSERT INTO users (id, data) VALUES (1, [1]);

Drag options to blanks, or click blank then click option'

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

B'name: Alice, age: 30'

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

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

Attempts:

3 left

💡 Hint

Common Mistakes

Missing quotes around keys or string values.

Using single quotes inside JSON instead of double quotes.

JSON strings must have keys and string values in double quotes.

3fill in blank

hard

Fix the error in the query to select the 'name' from the JSON column 'data'.

MySQL

SELECT data->>[1] FROM users WHERE id = 1;

Drag options to blanks, or click blank then click option'

A'$.name'

B'name'

C'age'

D'$.age'

Attempts:

3 left

💡 Hint

Common Mistakes

Using just 'name' without the JSON path syntax.

Using the wrong key like 'age' instead of 'name'.

Use the JSON path '$.name' to extract the 'name' key from JSON.

4fill in blank

hard

Fill both blanks to update the 'age' in the JSON column to 31 for user with id 1.

MySQL

UPDATE users SET data = JSON_SET(data, [1], [2]) WHERE id = 1;

Drag options to blanks, or click blank then click option'

A'$.age'

B31

C'31'

D'age'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the number 31, making it a string.

Using 'age' instead of the JSON path '$.age'.

Use JSON_SET with the JSON path '$.age' and the new integer value 31.

5fill in blank

hard

Fill all three blanks to select users where the JSON 'age' is greater than 25.

MySQL

SELECT * FROM users WHERE JSON_EXTRACT(data, [1]) [2] [3];

Drag options to blanks, or click blank then click option'

A'$.age'

B>

C25

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' for comparison.

Not using the JSON path syntax for the key.

Use JSON_EXTRACT with '$.age' and compare with > 25 to filter users older than 25.

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