Storing & Querying Native JSON in MySQL0%
Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)

Storing & Querying Native JSON in MySQL

Advanced14 min read•Updated: 2026-09-12
Study Materials

Storing & Querying Native JSON in MySQL

Historically, storing semi-structured or polymorphic data in relational databases required either awkward Entity-Attribute-Value (EAV) anti-patterns or flat TEXT columns.

Starting in MySQL 5.7 and heavily expanded in MySQL 8.0, MySQL features a Native JSON Data Type. Unlike storing JSON strings in TEXT, native JSON provides:

  1. 1
    Automatic Document Validation: Rejects malformed JSON syntax on insert.
  2. 2
    Binary Storage Format: Documents are parsed into a fast binary tree format (B-Jdoc), enabling random-access key lookups without parsing the entire text string!

Creating Tables with JSON Columns

SQL
CREATE TABLE product_catalogs (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
attributes JSON NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

Inserting JSON Documents

You can insert valid JSON string literals directly:

SQL
INSERT INTO product_catalogs (name, price, attributes) VALUES
('Ultra Gaming Laptop', 1999.99, '{
"brand": "Alienware",
"specs": { "cpu": "i9-14900HX", "ram_gb": 32, "storage_tb": 2 },
"tags": ["gaming", "portable", "vr-ready"],
"warranty_years": 2
}'),
('Ergonomic Mechanical Keyboard', 149.50, '{
"brand": "Keychron",
"specs": { "switches": "Gateron Brown", "wireless": true },
"tags": ["accessories", "rgb"]
}');

If an application attempts to insert malformed JSON (e.g., missing closing quotes), MySQL immediately rejects the query: ERROR 3140 (22032): Invalid JSON text in argument 1 to function json_valid: "Syntax error".

Querying JSON Using Paths: -> and ->>

MySQL provides two JSON extraction operators:

  • -> (JSON_EXTRACT): Extracts data preserving JSON formatting (strings include quotes).
  • ->> (Inline Path Operator): Extracts data unquoted as plain SQL text!
SQL
-- 1. Extract brand (returns "Keychron" with quotes)
SELECT name, attributes->'$.brand' AS brand_json
FROM product_catalogs;
 
-- 2. Extract unquoted string (returns Keychron without quotes)
SELECT name, attributes->>'$.brand' AS brand_clean
FROM product_catalogs;
 
-- 3. Extract nested object value
SELECT name, attributes->>'$.specs.cpu' AS cpu_model
FROM product_catalogs;
 
-- 4. Extract array element by index (0-based)
SELECT name, attributes->>'$.tags[0]' AS primary_tag
FROM product_catalogs;

Filtering in WHERE Clauses with JSON

SQL
-- Find all products with 32GB RAM
SELECT name, price
FROM product_catalogs
WHERE attributes->>'$.specs.ram_gb' = '32';
 
-- Check array containment using JSON_CONTAINS
SELECT name, attributes->>'$.brand'
FROM product_catalogs
WHERE JSON_CONTAINS(attributes->'$.tags', '"gaming"');

Multiple Choice Questions

1. What is the primary advantage of MySQL's native JSON data type over storing JSON strings in a TEXT column?

A. JSON columns take zero disk space B. Automatic syntax validation and internal binary tree storage allowing fast random-access key retrieval C. JSON columns can only store English words D. Automatically sends data to MongoDB Answer: B Explanation: Native JSON validates syntax and stores documents in an optimized binary format, allowing key extraction without full document deserialization.

2. What is the difference between the -> and ->> operators in MySQL JSON queries?

A. -> is for numbers, ->> is for dates B. -> returns values with JSON formatting/quotes, while ->> returns unquoted plain text strings C. ->> is deprecated D. -> can only query root keys Answer: B Explanation: -> is synonymous with JSON_EXTRACT (retaining quotes), whereas ->> unquotes the resulting string value.

3. How do you reference the first element of an array inside a JSON path expression?

A. $.array(1) B. $.array[0] C. $.array.first D. $.array->1 Answer: B Explanation: JSON path syntax uses standard 0-based bracket indexing (e.g., $.tags[0]).

4. Which MySQL function tests whether a JSON document or array contains a specific candidate value?

A. JSON_MATCH() B. JSON_CONTAINS() C. JSON_HAS() D. JSON_EXISTS() Answer: B Explanation: JSON_CONTAINS(target_json, candidate_json) returns 1 if the target document contains the candidate element.

5. What happens if an INSERT statement provides invalid JSON text to a native JSON column?

A. MySQL converts it to an empty string B. MySQL rejects the statement with Error 3140 (Invalid JSON text) C. MySQL stores the file on disk as binary D. It triggers a server reboot Answer: B Explanation: Native JSON strictly validates syntax upon write; any malformed syntax causes the statement to fail immediately.

Next Lesson

Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Optimistic vs Pessimistic Concurrency ArchitectureModifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext