JSON data type in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Extracting a value from JSON column

Given a MySQL table users with a JSON column profile storing data like {"age": 30, "city": "Boston"}, what is the output of this query?

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

MySQL

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

A NULL

B Boston

C "Boston"

D SyntaxError

💡 Hint

Remember JSON_EXTRACT returns the value as JSON, including quotes for strings.

JSON_EXTRACT returns the value as a JSON string, so string values are returned with quotes.

❓ query_result

intermediate

Filtering rows by JSON value

Consider a table orders with a JSON column details storing {"status": "shipped", "quantity": 5}. Which query returns all orders where status is exactly 'shipped'?

A SELECT * FROM orders WHERE JSON_EXTRACT(details, '$.status') = 'shipped';

B SELECT * FROM orders WHERE JSON_UNQUOTE(JSON_EXTRACT(details, '$.status')) = 'shipped';

C SELECT * FROM orders WHERE details->'$.status' = 'shipped';

D SELECT * FROM orders WHERE details->>'$.status' = 'shipped';

💡 Hint

Compare the extracted JSON string after removing quotes.

JSON_EXTRACT returns a JSON string with quotes, so use JSON_UNQUOTE to compare the actual string value.

📝 Syntax

advanced

Valid JSON path syntax in MySQL

Which of the following JSON path expressions is invalid in MySQL JSON functions?

A $.store.book[0].title

B $['store']['book'][0]['title']

C $.store.book[*].title

D $.store..book.title

💡 Hint

MySQL JSON path does not support recursive descent operator '..'.

MySQL JSON path syntax does not support the recursive descent operator '..', so option D is invalid.

❓ optimization

advanced

Indexing JSON columns for faster queries

You want to speed up queries filtering by a JSON key status inside a JSON column data. Which approach is best in MySQL 8.0+?

A Create a generated column extracting status and index it: ALTER TABLE t ADD COLUMN status VARCHAR(20) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.status'))) STORED; CREATE INDEX idx_status ON t(status);

B Create a fulltext index on the JSON column: CREATE FULLTEXT INDEX idx_data ON t(data);

C Create a normal index directly on the JSON column: CREATE INDEX idx_data ON t(data);

D Use a trigger to copy status to a separate table and index that table.

💡 Hint

MySQL supports indexing generated columns but not JSON columns directly.

MySQL cannot index JSON columns directly but can index generated columns that extract JSON values.

🧠 Conceptual

expert

Behavior of JSON_ARRAY_APPEND with non-array target

Given a JSON column info with value {"tags": "new"}, what is the result of:

UPDATE table SET info = JSON_ARRAY_APPEND(info, '$.tags', 'sale') WHERE id = 1;

What happens to the tags key?

A The query fails with an error because 'tags' is not an array

B The 'tags' key becomes an array: ["new", "sale"]

C The 'tags' key remains a string 'new' and 'sale' is appended as a new element

D The 'tags' key is replaced with the string 'sale'

💡 Hint

JSON_ARRAY_APPEND expects the target path to be an array or null.

If the target path is not an array or null, JSON_ARRAY_APPEND raises an error.

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