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.