JSON data type in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the JSON data type in MySQL?

The JSON data type stores JSON (JavaScript Object Notation) documents in a column. It allows you to store structured data like objects and arrays directly in the database.

beginner

How do you insert JSON data into a MySQL JSON column?

You insert JSON data as a string using single quotes, for example: INSERT INTO table (json_column) VALUES ('{"key": "value"}');

intermediate

What is one advantage of using the JSON data type over a plain text column?

MySQL validates the JSON format when inserting data and provides functions to query and manipulate JSON data efficiently.

intermediate

Name a MySQL function used to extract data from a JSON column.

JSON_EXTRACT(json_column, '$.key') extracts the value associated with 'key' from the JSON document stored in json_column.

advanced

Can you index a JSON column in MySQL?

Yes, you can create generated columns based on JSON values and index those columns for faster queries.

Which MySQL data type is designed to store JSON documents?

A VARCHAR

B TEXT

C JSON

D BLOB

The JSON data type is specifically designed to store JSON documents and validate their format.

How do you query a value from a JSON column in MySQL?

A Using CONCAT() function

B Using SUBSTRING() function

C Using CAST() function

D Using JSON_EXTRACT() function

JSON_EXTRACT() is used to retrieve values from JSON documents stored in JSON columns.

What happens if you try to insert invalid JSON into a JSON column?

A MySQL rejects the insert with an error

B MySQL stores it as plain text

C MySQL converts it to valid JSON automatically

D MySQL truncates the data

MySQL validates JSON format and rejects invalid JSON data on insert.

Which of these is a valid JSON document to insert into a JSON column?

A {"name": "Alice", "age": 30}

B {name: Alice, age: 30}

C [name: "Alice", age: 30]

D name: Alice, age: 30

Valid JSON requires keys and string values to be in double quotes.

How can you improve query performance on JSON data in MySQL?

A Store JSON as plain text

B Create generated columns from JSON values and index them

C Use only SELECT * queries

D Avoid indexing

Generated columns based on JSON values can be indexed to speed up queries.

Explain what the JSON data type is in MySQL and why it is useful.

Think about how JSON data is different from plain text.

Describe how you can query and index JSON data in MySQL.

Focus on functions and indexing strategies.