Hands-on MySQL JSON and NoSQL Features Exercises
JSON and NoSQL Features Exercises with solutions [20 exercises with solution]
1. Insert JSON Data into a Column
Write a MySQL query to insert a new record into a MySQL table that includes a JSON column containing semi-structured data.
2. Query JSON Data with JSON_EXTRACT
Write a MySQL query to extract a specific value from a JSON document stored in a column using the JSON_EXTRACT function.
3. Update a JSON Document with JSON_SET
Write a MySQL query to update a value within a JSON document stored in a column using the JSON_SET function.
4. Validate JSON Data using JSON_VALID
Write a MySQL query to check if the data in a JSON column is valid using the JSON_VALID function.
5. Extract Nested JSON Value
Write a MySQL query to extract a nested value from a JSON document stored in a column.
6. Merge two JSON Documents Using JSON_MERGE_PRESERVE
Write a MySQL query to merge two JSON documents, preserving duplicate keys, using the JSON_MERGE_PRESERVE function.
7. Create a Generated Column from JSON Data
Write a MySQL query to add a generated column that extracts a specific value from a JSON column and index it for faster querying.
8. Convert Row Data to JSON Using JSON_OBJECT
Write a MySQL query to convert relational row data into a JSON object using the JSON_OBJECT function.
9. Aggregate Data into a JSON Array with JSON_ARRAYAGG
Write a MySQL query to aggregate multiple row values into a JSON array using the JSON_ARRAYAGG function.
10. Update Multiple Values in a JSON Column Using JSON_SET
Write a MySQL query to update several key-value pairs within a JSON document stored in a column using the JSON_SET function.
11. Retrieve JSON Object Keys Using JSON_KEYS
Write a MySQL query to extract all keys from a JSON document stored in the Details column of the Products table using the JSON_KEYS function.
12. Search for a Specific Value in JSON Using JSON_CONTAINS
Write a MySQL query to find products whose details JSON column contains the key-value pair "color": "red" using the JSON_CONTAINS function.
13. Remove a Key from a JSON Document using JSON_REMOVE
Write a MySQL query to remove the key "warranty" from the JSON document in the Details column of the Products table using the JSON_REMOVE function.
14. Replace a Value in a JSON Document using JSON_REPLACE
Write a MySQL query to replace the value of the "model" key in the details JSON column of the Products table using the JSON_REPLACE function.
15. Extract JSON Array Length Using JSON_LENGTH
Write a MySQL query to determine the number of elements in a JSON array stored in the Reviews column of the Products table using the JSON_LENGTH function.
16. Create a Table with a JSON Column
Write a MySQL query to create a new table called UserProfiles that includes a JSON column for storing user settings.
17. Append Data to a JSON Array Using JSON_ARRAY_APPEND
Write a MySQL query to append a new review to the Reviews JSON array in the Products table using the JSON_ARRAY_APPEND function.
18. Using JSON_ARRAYAGG to Convert Data to JSON in MySQL
Write a MySQL query to aggregate multiple rows of product information into a single JSON array of objects using JSON_ARRAYAGG.
19. Query and Unquote JSON Data using JSON_UNQUOTE
Write a MySQL query to extract and unquote the "brand" value from the JSON document in the Details column using JSON_UNQUOTE.
20. Retrieve Only Rows with Valid JSON Data Using JSON_VALID
Write a MySQL query to select records from the Products table where the details column contains valid JSON data, using the JSON_VALID function.
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics