JSON data type in MySQL - Time & Space Complexity

Time Complexity: JSON data type

O(n)

Understanding Time Complexity

When working with JSON data type in MySQL, it's important to understand how the time to process JSON grows as the data size increases.

We want to know how the cost of querying or manipulating JSON changes when the JSON content gets bigger.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT JSON_EXTRACT(data, '$.items[0].name') AS first_item_name
FROM orders
WHERE id = 123;
    

This query extracts the name of the first item from a JSON column called data in the orders table for a specific order.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Parsing the JSON stored in the data column to find the requested path.
• How many times: Once per row returned by the query (here, one row because of WHERE id = 123).

How Execution Grows With Input

As the JSON data inside the data column grows larger, the time to parse and extract the requested value grows roughly in proportion to the size of the JSON.

Input Size (JSON length)	Approx. Operations
100 characters	100 operations
1,000 characters	1,000 operations
10,000 characters	10,000 operations

Pattern observation: The work grows linearly as the JSON size grows because the database must scan through the JSON text to find the requested data.

Final Time Complexity

Time Complexity: O(n)

This means the time to extract data from JSON grows in direct proportion to the size of the JSON content.

Common Mistake

[X] Wrong: "Extracting a value from JSON is always fast and constant time regardless of JSON size."

[OK] Correct: The database must parse the JSON text to find the value, so bigger JSON means more work and longer time.

Interview Connect

Understanding how JSON data processing scales helps you explain performance trade-offs when using JSON columns in databases.

This skill shows you can think about real data sizes and their impact on query speed.

Self-Check

"What if we indexed a specific JSON key using a generated column? How would that affect the time complexity of extracting that key?"

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