Test Your MySQL JSON SELECT Skills with This Quiz

Progress

True / False

Question: In MySQL, you can select values from a JSON column using the ->> operator.

True False

The ->> operator in MySQL is used to extract values from JSON columns.

True / False

MySQL's JSON functions are available starting from version 5.6.

True False

JSON functions were introduced in MySQL version 5.7.

True / False

MySQL's JSON_EXTRACT function can be used to retrieve a specific element from a JSON document.

True False

The JSON_EXTRACT function is used to extract data from a JSON document at a specified path.

True / False

In MySQL, the JSON_UNQUOTE function removes the surrounding quotes from a JSON string.

True False

The JSON_UNQUOTE function removes quotes from a JSON string, converting it to a regular string.

True / False

MySQL's JSON_CONTAINS function can check if a JSON document contains a specific key or value.

True False

The JSON_CONTAINS function checks whether a specified value or key exists within a JSON document.

True / False

The JSON_SEARCH function in MySQL returns a boolean value indicating if the search string was found.

True False

The JSON_SEARCH function returns the path to the value if found, not a boolean.

True / False

In MySQL, you can use the JSON_TABLE function to transform a JSON document into a relational table.

True False

The JSON_TABLE function allows you to convert a JSON document into a relational table, making it easier to work with JSON data in SQL queries.

True / False

MySQL's JSON_SET function can add or update multiple values in a JSON document at once.

True False

The JSON_SET function can update or add multiple values within a JSON document in one go.

True / False

The JSON_REMOVE function in MySQL can only remove one key-value pair at a time from a JSON document.

True False

The JSON_REMOVE function can remove multiple key-value pairs from a JSON document at once by specifying multiple paths.

Q10

True / False

In MySQL, you can use the JSON_ARRAYAGG function to aggregate multiple rows into a JSON array.

True False

The JSON_ARRAYAGG function in MySQL aggregates values from multiple rows into a single JSON array. This is useful for creating JSON representations of result sets.

Q11

Single Choice

What function in MySQL is used to extract a value from a JSON document?

JSON_EXTRACT() JSON_QUERY() JSON_VALUE()JSON_GET()

The JSON_EXTRACT() function in MySQL is used to extract a value from a JSON document based on a specified path.

Q12

Single Choice

Given a JSON column named data with a JSON object {"name": "John", "age": 30}, how would you select the value of the "name" key?

SELECT JSON_VALUE(data, '$.name') FROM table_name; SELECT JSON_EXTRACT(data, '$.name') FROM table_name; SELECT JSON_GET(data, '$.name') FROM table_name;SELECT JSON_QUERY(data, '$.name') FROM table_name;

To select the value of the "name" key, JSON_EXTRACT() with the path $.name is used.

Q13

Single Choice

Which function can be used to check if a JSON document contains a specific key in MySQL?

JSON_CONTAINS() JSON_HAS_KEY() JSON_EXISTS()JSON_CONTAINS_PATH()

JSON_CONTAINS_PATH() is used to check if a JSON document contains a specific key or path.

Q14

Single Choice

How do you extract a JSON array element from a JSON column in MySQL?

A) JSON_EXTRACT(data, '$[index]') JSON_GET(data, '$[index') JSON_ARRAY_ELEMENT(data, '$[index]')JSON_QUERY(data, '$[index]')

Use JSON_EXTRACT() with an index to extract an element from a JSON array.

Q15

Single Choice

What is the output of the following query?

SQL Code

SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "Alice"}', '$.name'));

Alice "Alice" NULL""

JSON_UNQUOTE() removes quotes from the extracted JSON string value.

Q16

Single Choice

To find rows where a JSON column data contains a specific value, which function would you use?

JSON_CONTAINS() JSON_SEARCH() JSON_FIND()JSON_VALUE()

JSON_CONTAINS() checks if a JSON document contains a specified value.

Q17

Single Choice

How would you update a JSON document in a column named data to change the value of the key "status" to "active"?

SQL Code

UPDATE table_name
SET data = JSON_SET(data, '$.status', 'active');

Sets the entire data column to "active" Updates the "status" key within the data JSON column to "active" Adds a new key "status" with the value "active"Removes the "status" key from the data JSON column

JSON_SET() is used to update the value of an existing key in a JSON document.

Q18

Single Choice

Which MySQL function is used to add a new key-value pair to a JSON object in a column named data?

JSON_INSERT() JSON_ADD() JSON_UPDATE()JSON_APPEND()

JSON_INSERT() is used to add a new key-value pair to a JSON object.

Q19

Single Choice

What is the result of this query?

SQL Code

SELECT JSON_EXTRACT('{"person": {"name": "John", "age": 30}}', '$.person.age');

30 0 {"age": 30}"30"

JSON_EXTRACT() extracts the value of the "age" key from the JSON object.

Q20

Single Choice

Which function would you use to check if a JSON column data contains an object with a specific key-value pair in MySQL?

SQL Code

SELECT JSON_CONTAINS(data, '{"key": "value"}', '$');
What is this query checking for?

If the JSON document contains the key "key" with value "value" If the JSON document contains the value "value" in any key If the JSON document has a nested JSON object with the key "key"If the JSON document contains the exact JSON object '{"key": "value"}'

JSON_CONTAINS() checks if the JSON column contains the exact JSON object specified in the second argument.

Q21

Multiple Choice

Which SQL query extracts the value of 'firstName' from a JSON column 'profile' using JSON_EXTRACT?

SQL Code

SELECT JSON_EXTRACT(profile, '$.firstName') AS first_name
FROM users;

SELECT JSON_EXTRACT(profile, '$.firstName') AS first_name FROM users; SELECT JSON_EXTRACT(profile, '$.first_name') AS first_name FROM users; SELECT profile->>'$.firstName' AS first_name FROM users;SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.firstName')) AS first_name FROM users;

The JSON_EXTRACT function extracts the value associated with the 'firstName' key from the 'profile' JSON column in the users table.

Q22

Multiple Choice

Identify the SQL query that retrieves all users where the 'age' key in the 'profile' JSON column is greater than 25.

SQL Code

SELECT * FROM users
WHERE JSON_EXTRACT(profile, '$.age') > 25;

SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.age') > 25; SELECT * FROM users WHERE JSON_UNQUOTE(JSON_EXTRACT(profile, '$.age')) > 25; SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.age') = '25';SELECT * FROM users WHERE profile->>'$.age' > 25;

The query extracts the 'age' value from the 'profile' JSON column and compares it to 25, filtering the users accordingly.

Q23

Multiple Choice

Which SQL query uses JSON_UNQUOTE to extract and unquote the value of 'lastName' from a JSON column 'profile'?

SQL Code

SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.lastName')) AS last_name
FROM users;

SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.lastName')) AS last_name FROM users; SELECT JSON_EXTRACT(profile, '$.lastName') AS last_name FROM users; SELECT profile->>'$.lastName' AS last_name FROM users;SELECT JSON_UNQUOTE(profile->>'$.lastName') AS last_name FROM users;

The JSON_UNQUOTE function removes the surrounding quotes from the JSON_EXTRACT result, returning the value of 'lastName' as a plain string.

Q24

Multiple Choice

Determine the SQL query that checks if the 'profile' JSON column contains a key 'email'.

SQL Code

SELECT * FROM users
WHERE JSON_CONTAINS_PATH(profile, 'one', '$.email');

SELECT * FROM users WHERE JSON_CONTAINS_PATH(profile, 'one', '$.email'); SELECT * FROM users WHERE JSON_KEYS(profile) LIKE '%email%'; SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.email') IS NOT NULL;SELECT * FROM users WHERE JSON_SEARCH(profile, 'one', 'email') IS NOT NULL;

The JSON_CONTAINS_PATH function checks if the 'profile' JSON column contains the key 'email' and returns rows where this condition is true.

Q25

Multiple Choice

Which SQL query uses JSON_SET to update or insert a key 'city' with value 'New York' in a JSON column 'address'?

SQL Code

UPDATE users
SET address = JSON_SET(address, '$.city', 'New York');

UPDATE users SET address = JSON_SET(address, '$.city', 'New York'); UPDATE users SET address = JSON_INSERT(address, '$.city', 'New York'); UPDATE users SET address = JSON_REPLACE(address, '$.city', 'New York');UPDATE users SET address = JSON_SET(address, '$.city', 'New York') WHERE JSON_EXTRACT(address, '$.city') IS NULL;

The JSON_SET function inserts or updates the 'city' key with the value 'New York' in the 'address' JSON column for each user.

Q26

Multiple Choice

Which SQL query uses JSON_REMOVE to delete the key 'phone' from a JSON column 'contact_info'?

SQL Code

UPDATE users
SET contact_info = JSON_REMOVE(contact_info, '$.phone');

UPDATE users SET contact_info = JSON_REMOVE(contact_info, '$.phone'); UPDATE users SET contact_info = JSON_SET(contact_info, '$.phone', NULL); UPDATE users SET contact_info = JSON_DELETE(contact_info, '$.phone');UPDATE users SET contact_info = JSON_REMOVE(contact_info, '$.phone') WHERE JSON_CONTAINS_PATH(contact_info, 'one', '$.phone');

The JSON_REMOVE function deletes the 'phone' key from the 'contact_info' JSON column in the users table.

Q27

Multiple Choice

Identify the SQL query that uses JSON_ARRAY to create a JSON array from multiple columns.

SQL Code

SELECT JSON_ARRAY(firstName, lastName, age) AS user_info
FROM users;

SELECT JSON_ARRAY(firstName, lastName, age) AS user_info FROM users; SELECT JSON_OBJECT('firstName', firstName, 'lastName', lastName, 'age', age) AS user_info FROM users; SELECT JSON_MERGE(firstName, lastName, age) AS user_info FROM users;SELECT JSON_ARRAYAGG(firstName, lastName, age) AS user_info FROM users;

The JSON_ARRAY function creates a JSON array from the values of 'firstName', 'lastName', and 'age' columns for each user.

Q28

Multiple Choice

Which SQL query uses JSON_UNQUOTE to return an unquoted string from a JSON column 'metadata'?

SQL Code

SELECT JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.title')) AS title
FROM articles;

SELECT JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.title')) AS title FROM articles; SELECT JSON_EXTRACT(metadata, '$.title') AS title FROM articles; SELECT JSON_UNQUOTE(metadata->>'$.title') AS title FROM articles;SELECT metadata->>'$.title' AS title FROM articles;

The JSON_UNQUOTE function removes the quotes from the string value extracted from the 'metadata' JSON column, returning a plain string.

Q29

Multiple Choice

Determine the SQL query that uses JSON_MERGE_PATCH to merge two JSON columns 'preferences' and 'defaults'.

SQL Code

SELECT JSON_MERGE_PATCH(preferences, defaults) AS final_preferences
FROM user_settings;

SELECT JSON_MERGE_PATCH(preferences, defaults) AS final_preferences FROM user_settings; SELECT JSON_MERGE(preferences, defaults) AS final_preferences FROM user_settings; SELECT JSON_MERGE_PRESERVE(preferences, defaults) AS final_preferences FROM user_settings;SELECT JSON_MERGE_PATCH(defaults, preferences) AS final_preferences FROM user_settings;

The JSON_MERGE_PATCH function combines the 'preferences' and 'defaults' JSON columns into a single JSON object, applying the merge-patch strategy.

Q30

Multiple Choice

Which SQL query uses JSON_SEARCH to find if the key 'status' with value 'active' exists in a JSON column 'attributes'?

SQL Code

SELECT * FROM products
WHERE JSON_SEARCH(attributes, 'one', 'active', NULL, '$.status') IS NOT NULL;

SELECT * FROM products WHERE JSON_SEARCH(attributes, 'one', 'active', NULL, '$.status') IS NOT NULL; SELECT * FROM products WHERE JSON_CONTAINS(attributes, '"active"', '$.status'); SELECT * FROM products WHERE JSON_EXTRACT(attributes, '$.status') = 'active';SELECT * FROM products WHERE attributes->>'$.status' = 'active';

The JSON_SEARCH function searches for the 'status' key with the value 'active' within the 'attributes' JSON column, and the query returns rows where this condition is met.

MySQL Database Quiz Questions