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.

Practice

1. What is the main advantage of using the JSON data type in MySQL?

easy

A. It allows storing flexible, structured data in a single column.

B. It automatically creates indexes for faster queries.

C. It restricts data to fixed column types only.

D. It converts JSON data to XML format internally.

Solution

1. Step 1: Understand JSON data type purpose

   The JSON data type is designed to store flexible and structured data inside one column, unlike fixed columns.

2. Step 2: Compare options

   It allows storing flexible, structured data in a single column. correctly states the main advantage. Options B, C, and D are incorrect because JSON does not automatically create indexes, does not restrict data to fixed types, and does not convert JSON to XML.

3. Final Answer:

   It allows storing flexible, structured data in a single column. -> Option A

4. Quick Check:

   JSON stores flexible data = A [OK]

Hint: JSON type stores flexible data in one column [OK]

Common Mistakes:

• Thinking JSON restricts data to fixed types
• Assuming JSON auto-indexes all data
• Confusing JSON with XML conversion

2. Which of the following is the correct way to declare a column with JSON data type in MySQL?

easy

A. CREATE TABLE t1 (data TEXT JSON);

B. CREATE TABLE t1 (data VARCHAR(255));

C. CREATE TABLE t1 (data JSON);

D. CREATE TABLE t1 (data JSONB);

Solution

1. Step 1: Recall JSON column declaration syntax

   In MySQL, the JSON data type is declared simply as JSON without extra modifiers.

2. Step 2: Evaluate options

   CREATE TABLE t1 (data JSON); uses correct syntax: data JSON. CREATE TABLE t1 (data VARCHAR(255)); uses VARCHAR, not JSON. CREATE TABLE t1 (data TEXT JSON); mixes TEXT and JSON incorrectly. CREATE TABLE t1 (data JSONB); uses JSONB which is PostgreSQL-specific, not MySQL.

3. Final Answer:

   CREATE TABLE t1 (data JSON); -> Option C

4. Quick Check:

   MySQL JSON column = data JSON [OK]

Hint: Use JSON keyword alone to declare JSON column [OK]

Common Mistakes:

• Using VARCHAR or TEXT instead of JSON
• Adding unsupported modifiers like JSONB
• Mixing JSON with other types in declaration

3. Given the table users with a JSON column info storing {"age": 30, "city": "NY"}, what will this query return?

SELECT JSON_EXTRACT(info, '$.city') FROM users WHERE id = 1;

medium

A. "NY"

B. NY

C. city

D. NULL

Solution

1. Step 1: Understand JSON_EXTRACT function

   JSON_EXTRACT returns the JSON value at the specified path, including quotes for strings.

2. Step 2: Apply to given data

   The path '$.city' points to the string "NY". The function returns it with quotes, so the result is "NY".

3. Final Answer:

   "NY" -> Option A

4. Quick Check:

   JSON_EXTRACT returns quoted string = "NY" [OK]

Hint: JSON_EXTRACT returns JSON value with quotes for strings [OK]

Common Mistakes:

• Expecting unquoted string without quotes
• Confusing key name with value
• Assuming NULL if path exists

4. What is wrong with this query?

SELECT info->'$.age' FROM users;

Assuming info is a JSON column.

medium

A. The operator '->' is invalid in MySQL.

B. The query is correct and will return the age as a number.

C. The path syntax '$.age' is incorrect.

D. The operator '->' returns JSON, but the query expects plain text.

Solution

1. Step 1: Verify operator syntax

   The '->' operator is valid in MySQL for JSON column extraction.

2. Step 2: Verify path syntax

   The path '$.age' is correct for accessing the 'age' key.

3. Step 3: Verify output

   For a numeric age like 30, it returns 30 as a number, so the query is correct.

4. Final Answer:

   The query is correct and will return the age as a number. -> Option B

5. Quick Check:

   '->' valid, returns age as number [OK]

Hint: MySQL '->' extracts JSON as typed value [OK]

Common Mistakes:

• Thinking '->' is invalid syntax
• Confusing JSON and text return types
• Using wrong path syntax

5. You want to find all users whose JSON info column contains an age greater than 25. Which query correctly does this?

hard

A. SELECT * FROM users WHERE JSON_EXTRACT(info, 'age') > 25;

B. SELECT * FROM users WHERE info->>'age' > 25;

C. SELECT * FROM users WHERE info->'age' > 25;

D. SELECT * FROM users WHERE CAST(JSON_EXTRACT(info, '$.age') AS UNSIGNED) > 25;

Solution

1. Step 1: Understand JSON_EXTRACT returns JSON value

   JSON_EXTRACT returns a JSON value (e.g., '30'), which is a string with quotes, so direct comparison fails.

2. Step 2: Use CAST to convert JSON string to number

   CAST(... AS UNSIGNED) converts the extracted JSON string to a number for numeric comparison.

3. Step 3: Evaluate other options

   SELECT * FROM users WHERE JSON_EXTRACT(info, '$.age') > 25; compares JSON string directly, causing wrong results. SELECT * FROM users WHERE info->>'$.age' > 25; uses '->>' which returns text, but MySQL may treat it as string, not number. SELECT * FROM users WHERE info->'$.age' > 25; uses '->' which returns JSON, not suitable for numeric comparison.

4. Final Answer:

   SELECT * FROM users WHERE CAST(JSON_EXTRACT(info, '$.age') AS UNSIGNED) > 25; -> Option D

5. Quick Check:

   Cast JSON to number before comparison [OK]

Hint: Cast JSON extract to number for numeric comparisons [OK]

Common Mistakes:

• Comparing JSON strings directly to numbers
• Using '->' operator without casting
• Assuming '->>' always returns number