Test Your MySQL JSON Data Type Knowledge with This Quiz

Progress

True / False

MySQL supports a JSON data type for storing JSON documents.

True False

MySQL introduced a native JSON data type in version 5.7 to store JSON documents efficiently.

True / False

In MySQL, JSON data type values are stored as plain text strings.

True False

JSON data type values are stored in an optimized binary format, not as plain text strings.

True / False

You can use the -> operator to extract a value from a JSON column in MySQL.

True False

The -> operator in MySQL allows you to extract a value from a JSON column.

True / False

The JSON_ARRAY function in MySQL can be used to create a JSON array from a list of values.

True False

The JSON_ARRAY function is used to create a JSON array from a list of values.

True / False

MySQL's JSON_EXTRACT function returns a JSON value directly.

True False

The JSON_EXTRACT function returns a JSON value, which can then be used or converted as needed.

True / False

The JSON_EXTRACT function returns a JSON value, which can then be used or converted as needed.

True False

MySQL automatically validates JSON data stored in a JSON column to ensure it is well-formed.

True / False

In MySQL, you cannot create an index on a specific field within a JSON column.

True False

MySQL allows the creation of indexes on specific fields within a JSON column using generated columns.

True / False

The JSON_MERGE_PATCH function in MySQL replaces the entire JSON document with the new data.

True False

The JSON_MERGE_PATCH function in MySQL modifies only the specified parts of a JSON document, not the entire document.

True / False

The JSON_SCHEMA_VALID function in MySQL can be used to validate JSON documents against a specified schema.

True False

As of MySQL 8.0, there is no JSON_SCHEMA_VALID function; schema validation must be done externally.

Q10

True / False

MySQL's JSON_SET function can add, update, or remove a value in a JSON document.

True False

The JSON_SET function can be used to add, update, or remove a value in a JSON document by specifying the path and value.

Q11

Single Choice

What version of MySQL introduced the JSON data type?

MySQL 5.5 MySQL 5.6 MySQL 5.7MySQL 8.0

The JSON data type was introduced in MySQL 5.7.

Q12

Single Choice

Which function is used to create a JSON array in MySQL?

JSON_OBJECT JSON_ARRAY JSON_MERGEJSON_PARSE

The JSON_ARRAY function is used to create a JSON array from a list of values.

Q13

Single Choice

Which operator is used to extract a value from a JSON column in MySQL?

. -> ->=>

The -> operator is used to extract a value from a JSON column in MySQL.

Q14

Single Choice

What does the JSON_UNQUOTE function do in MySQL?

Removes quotes from a JSON string. Converts JSON to plain text. Extracts a JSON value.Parses a JSON document.

The JSON_UNQUOTE function removes the quotes from a JSON string.

Q15

Single Choice

Which function is used to merge two JSON documents in MySQL?

JSON_MERGE JSON_APPEND JSON_SETJSON_CONCAT

The JSON_MERGE function is used to merge two JSON documents.

Q16

Single Choice

Which MySQL function is used to search for a value within a JSON document?

JSON_SEARCH JSON_FIND JSON_LOOKUPJSON_EXTRACT

The JSON_SEARCH function is used to search for a value within a JSON document.

Q17

Single Choice

How can you index a specific field within a JSON column in MySQL?

Using JSON_INDEX Creating a virtual column Directly indexing the JSON fieldUsing INDEX_JSON_FIELD

In MySQL, you can create a virtual (generated) column from a JSON field and then index that column.

Q18

Single Choice

Which function is used to modify parts of a JSON document in MySQL without affecting the entire document?

JSON_MODIFY JSON_SET JSON_UPDATEJSON_PATCH

The JSON_SET function is used to modify parts of a JSON document without affecting the entire document.

Q19

Single Choice

What does the JSON_REMOVE function do in MySQL?

Deletes a JSON document. Removes elements from a JSON array. Removes data from a JSON object.Removes a key from a JSON document.

The JSON_REMOVE function removes a key from a JSON document.

Q20

Single Choice

Which MySQL function converts a JSON document to a string?

JSON_QUOTE JSON_UNQUOTE JSON_STRINGIFYJSON_TO_STRING

The JSON_QUOTE function converts a JSON document to a string by quoting it as a JSON value.

Q21

Multiple Choice

Identify the correct SQL statement to create a table with a JSON column.

SQL Code

CREATE TABLE users (
 user_id INT PRIMARY KEY,
 user_data JSON
);

CREATE TABLE users ( user_id INT PRIMARY KEY, user_data JSON ); CREATE TABLE users ( user_id INT PRIMARY KEY, user_data TEXT ); CREATE TABLE users ( user_id INT PRIMARY KEY, user_data VARCHAR(255) );CREATE TABLE users ( user_id INT PRIMARY KEY, user_data BLOB );

The JSON data type is used to store JSON formatted data, allowing for complex data structures within a single column.

Q22

Multiple Choice

Choose the correct SQL statement to insert a JSON object into a JSON column.

SQL Code

INSERT INTO users (user_id, user_data)
VALUES (1, '{"name": "John Doe", "age": 30}');

INSERT INTO users (user_id, user_data) VALUES (1, '{"name": "John Doe", "age": 30}'); INSERT INTO users (user_id, user_data) VALUES (1, '{name: "John Doe", age: 30}'); INSERT INTO users (user_id, user_data) VALUES (1, JSON_OBJECT('name', 'John Doe', 'age', 30));INSERT INTO users (user_id, user_data) VALUES (1, "{'name': 'John Doe', 'age': 30}");

When inserting data into a JSON column, the JSON object must be formatted as a valid JSON string.

Q23

Multiple Choice

Determine the correct SQL statement to update a specific field in a JSON column.

SQL Code

UPDATE users
SET user_data = JSON_SET(user_data, '$.age', 31)
WHERE user_id = 1;

UPDATE users SET user_data = JSON_SET(user_data, '$.age', 31) WHERE user_id = 1; UPDATE users SET user_data = '{"age": 31}' WHERE user_id = 1; UPDATE users SET user_data = JSON_MODIFY(user_data, '$.age', 31) WHERE user_id = 1;UPDATE users SET user_data.age = 31 WHERE user_id = 1;

The JSON_SET function is used to update a specific field within a JSON object, without affecting the rest of the data.

Q24

Multiple Choice

Which SQL statement correctly creates an index on a JSON column's specific field?

SQL Code

CREATE INDEX idx_user_age
ON users((JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.age'))));

CREATE INDEX idx_user_age ON users((JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.age')))); CREATE INDEX idx_user_age ON users((JSON_EXTRACT(user_data, '$.age'))); CREATE INDEX idx_user_age ON users(JSON_EXTRACT(user_data, '$.age'));CREATE INDEX idx_user_age ON users(user_data.age);

Indexing a specific field within a JSON column requires the use of JSON_EXTRACT to extract the field value, which is then unquoted using JSON_UNQUOTE.

Q25

Multiple Choice

Select the correct SQL statement to query a specific field in a JSON column.

SQL Code

SELECT user_id, JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.name')) AS name
FROM users;

SELECT user_id, JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.name')) AS name FROM users; SELECT user_id, JSON_EXTRACT(user_data, '$.name') AS name FROM users; SELECT user_id, user_data->'$.name' AS name FROM users;SELECT user_id, user_data.name AS name FROM users;

The JSON_EXTRACT function is used to retrieve a specific field from a JSON column, and JSON_UNQUOTE removes the double quotes from the extracted value.

Q26

Multiple Choice

Determine the correct SQL statement to validate JSON data before inserting into a JSON column.

SQL Code

SET @json = '{"name": "John Doe", "age": 30}';
IF JSON_VALID(@json) THEN
 INSERT INTO users (user_id, user_data) VALUES (1, @json);
END IF;

SET @json = '{"name": "John Doe", "age": 30}'; IF JSON_VALID(@json) THEN INSERT INTO users (user_id, user_data) VALUES (1, @json); END IF; SET @json = '{"name": "John Doe", "age": 30}'; IF JSON_VALID(@json) = 1 THEN INSERT INTO users (user_id, user_data) VALUES (1, @json); END IF; INSERT INTO users (user_id, user_data) VALUES (1, JSON_CHECK('{"name": "John Doe", "age": 30}'));INSERT INTO users (user_id, user_data) VALUES (1, JSON_SAFE('{"name": "John Doe", "age": 30}'));

The JSON_VALID function checks whether a string contains valid JSON data, which is crucial for maintaining data integrity in JSON columns.

Q27

Multiple Choice

Which SQL statement correctly merges two JSON objects in a JSON column?

SQL Code

UPDATE users
SET user_data = JSON_MERGE(user_data, '{"location": "New York"}')
WHERE user_id = 1;

UPDATE users SET user_data = JSON_MERGE(user_data, '{"location": "New York"}') WHERE user_id = 1; UPDATE users SET user_data = JSON_SET(user_data, '$.location', 'New York') WHERE user_id = 1; UPDATE users SET user_data = user_data || '{"location": "New York"}' WHERE user_id = 1;UPDATE users SET user_data = JSON_APPEND(user_data, '$.location', 'New York') WHERE user_id = 1;

The JSON_MERGE function is used to combine two JSON objects, appending or updating keys in the existing JSON data.

Q28

Multiple Choice

Select the correct SQL statement to delete a specific key from a JSON object in a JSON column.

SQL Code

UPDATE users
SET user_data = JSON_REMOVE(user_data, '$.age')
WHERE user_id = 1;

UPDATE users SET user_data = JSON_REMOVE(user_data, '$.age') WHERE user_id = 1; UPDATE users SET user_data = JSON_DELETE(user_data, '$.age') WHERE user_id = 1; UPDATE users SET user_data = JSON_UNSET(user_data, '$.age') WHERE user_id = 1;UPDATE users SET user_data = JSON_REMOVE_KEY(user_data, '$.age') WHERE user_id = 1;

The JSON_REMOVE function is used to delete a specific key-value pair from a JSON object stored in a JSON column.

Q29

Multiple Choice

Determine the correct SQL statement to extract a nested JSON object from a JSON column.

SQL Code

SELECT JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.address.city')) AS city
FROM users
WHERE user_id = 1;

SELECT JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.address.city')) AS city FROM users WHERE user_id = 1; SELECT JSON_EXTRACT(user_data, '$.address.city') AS city FROM users WHERE user_id = 1; SELECT user_data->>'$.address.city' AS city FROM users WHERE user_id = 1;SELECT JSON_GET(user_data, '$.address.city') AS city FROM users WHERE user_id = 1;

The JSON_EXTRACT function can retrieve nested objects within a JSON structure, and JSON_UNQUOTE removes quotes from the result.

Q30

Multiple Choice

Which SQL statement correctly creates a table with a generated column based on a JSON field?

SQL Code

CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 order_data JSON,
 total_amount DECIMAL(10, 2) AS (JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.total')))
);

CREATE TABLE orders ( order_id INT PRIMARY KEY, order_data JSON, total_amount DECIMAL(10, 2) AS (JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.total'))) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, order_data JSON, total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (JSON_EXTRACT(order_data, '$.total')) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, order_data JSON, total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.total'))) STORED );CREATE TABLE orders ( order_id INT PRIMARY KEY, order_data JSON, total_amount DECIMAL(10, 2) VIRTUAL (JSON_EXTRACT(order_data, '$.total')) );

A generated column can be created based on a specific field within a JSON column using the JSON_UNQUOTE and JSON_EXTRACT functions.

MySQL Database Quiz Questions