JSON in MySQL: A Complete Guide

MySQL's JSON data type stores structured data in a single column. It supports indexing, validation, and partial updates. Use JSON when your data structure varies between rows or when you need flexible key-value storage. Use separate columns when the structure is fixed and you need to query individual fields efficiently.

MySQL added native JSON support in version 5.7. MySQL 8.0 added significant improvements including partial updates, multi-valued indexes, and JSON_TABLE. MySQL stores JSON in an optimized binary format that allows fast read access to document elements. PostgreSQL uses a different binary format (JSONB) with different indexing capabilities. Both are efficient for reads, but their approaches to indexing and updates differ.

The binary storage format matters more than you might think. MySQL does not store JSON as raw text. It parses the JSON on INSERT and stores it in a binary representation that allows MySQL to access specific keys without re-parsing the entire document. This means reads are faster than you would expect from a text-based format, but writes have some overhead from the parsing step.

Creating JSON Columns

-- Create a table with JSON column
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  attributes JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert JSON data
INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "ram": 16, "storage": 512, "ports": ["USB-C", "HDMI"]}'),
('Phone', '{"brand": "Apple", "storage": 256, "colors": ["black", "white", "blue"]}');

Querying JSON Data

Extracting Values

-- Extract a single value
SELECT name, attributes->'$.brand' as brand FROM products;
-- Returns: "Dell" (with quotes)

-- Extract without quotes
SELECT name, attributes->>'$.brand' as brand FROM products;
-- Returns: Dell (without quotes)

-- Extract nested values
SELECT name, attributes->>'$.ram' as ram FROM products;

-- Extract array elements
SELECT name, attributes->>'$.ports[0]' as first_port FROM products;

Filtering by JSON Values

-- Filter by JSON value
SELECT * FROM products
WHERE attributes->>'$.brand' = 'Dell';

-- Filter by numeric value
SELECT * FROM products
WHERE attributes->>'$.ram' > 8;

-- Filter by array contains
SELECT * FROM products
WHERE JSON_CONTAINS(attributes->'$.ports', '"USB-C"');

-- Filter by array length
SELECT * FROM products
WHERE JSON_LENGTH(attributes->'$.colors') > 2;

Indexing JSON Columns

You cannot directly index a JSON column, but you can create virtual generated columns from JSON values and index those.

This is the most important concept to understand about JSON performance in MySQL. Without generated columns, every JSON query does a full table scan and parses the JSON for every row. That might be acceptable for small tables with a few hundred rows, but it becomes unusable fast as data grows. The generated column trick turns a slow JSON query into a fast index lookup, and it is worth setting up for any field you plan to filter or join on regularly.

-- Add a generated column for indexing
ALTER TABLE products
ADD COLUMN brand VARCHAR(100)
GENERATED ALWAYS AS (attributes->>'$.brand') VIRTUAL;

-- Index the generated column
CREATE INDEX idx_products_brand ON products(brand);

-- Now this query uses the index
SELECT * FROM products WHERE brand = 'Dell';

For frequently queried JSON fields, this is the right approach. The generated column is virtual (not stored on disk) and the index makes lookups fast.

Virtual generated columns do not take up storage space because MySQL computes them on the fly when needed. However, the index on the generated column does take up space. This tradeoff is almost always worth it because the index prevents full table scans. If storage is a concern, you can use a stored generated column instead, which precomputes the value and stores it on disk at the cost of extra storage.

Modifying JSON Data

Partial Updates (MySQL 8.0+)

-- Update a single key
UPDATE products
SET attributes = JSON_SET(attributes, '$.ram', 32)
WHERE id = 1;

-- Insert a new key (only if it does not exist)
UPDATE products
SET attributes = JSON_INSERT(attributes, '$.weight', 1.5)
WHERE id = 1;

-- Remove a key
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.ports[1]')
WHERE id = 1;

Appending to Arrays

-- Append to an array
UPDATE products
SET attributes = JSON_ARRAY_APPEND(attributes, '$.ports', 'USB-A')
WHERE id = 1;

-- Replace entire array
UPDATE products
SET attributes = JSON_SET(attributes, '$.ports', JSON_ARRAY('USB-C', 'HDMI', 'USB-A'))
WHERE id = 1;

Aggregating JSON Data

-- Aggregate JSON values
SELECT
  attributes->>'$.brand' as brand,
  COUNT(*) as product_count,
  AVG(attributes->>'$.ram') as avg_ram
FROM products
GROUP BY brand;

-- Combine multiple rows into JSON array
SELECT JSON_ARRAYAGG(name) as product_names FROM products;

-- Combine into JSON object
SELECT JSON_OBJECTAGG(id, name) as product_map FROM products;

JSON Validation

MySQL validates JSON on INSERT and UPDATE. Invalid JSON is rejected.

Validation happens automatically, which means you do not need to write validation logic in your application for basic JSON structure checks. However, MySQL only validates the structure, not the semantics. It will accept a JSON object with the wrong types or missing required fields. If you need deeper validation (checking that a price field is a positive number, for example), you will need to handle that in your application layer or with a generated column constraint.

-- This works
INSERT INTO products (name, attributes) VALUES ('Test', '{"key": "value"}');

-- This fails (invalid JSON)
INSERT INTO products (name, attributes) VALUES ('Test', '{key: value}');
-- Error: Invalid JSON text

When to Use JSON vs Separate Columns

JSON Performance Considerations

• JSON queries are slower than column queries - MySQL must parse the JSON string for every query
• Partial updates are efficient - MySQL only modifies the changed part of the JSON
• Generated columns with indexes - Make JSON queries as fast as column queries
• JSON validation has overhead - Validating JSON on every INSERT/UPDATE adds processing time

-- Performance comparison
-- Slow: Querying JSON directly
SELECT * FROM products WHERE attributes->>'$.brand' = 'Dell';
-- Time: 45ms (full table scan, parse JSON for each row)

-- Fast: Querying indexed generated column
SELECT * FROM products WHERE brand = 'Dell';
-- Time: 0.5ms (index lookup)

Common JSON Patterns

Store User Preferences

CREATE TABLE user_preferences (
  user_id INT PRIMARY KEY,
  preferences JSON DEFAULT ('{}')
);

-- Store preferences
UPDATE user_preferences
SET preferences = JSON_SET(preferences, '$.theme', 'dark')
WHERE user_id = 1;

-- Read preferences
SELECT preferences->>'$.theme' as theme
FROM user_preferences WHERE user_id = 1;

Store API Responses

CREATE TABLE api_cache (
  id INT PRIMARY KEY AUTO_INCREMENT,
  endpoint VARCHAR(200),
  response JSON,
  cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Store Flexible Metadata

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200),
  metadata JSON DEFAULT ('{}')
);

-- Different products have different attributes
INSERT INTO products (name, metadata) VALUES
('Laptop', '{"cpu": "i7", "ram": 16}'),
('Shirt', '{"size": "L", "color": "blue"}');

Common Mistakes

1. Using JSON for structured, predictable data - If every row has the same fields, use proper columns instead of JSON. You lose type safety, indexing efficiency, and foreign key support
2. Querying JSON without generated columns - Direct JSON queries require parsing for every row. Create virtual generated columns and index them for frequently queried fields
3. Storing large documents in JSON - MySQL JSON columns can store up to 1GB, but large documents slow down queries and increase memory usage. Keep JSON focused and small

Key Takeaways

• Use JSON when your data structure varies between rows or comes from external sources
• Create virtual generated columns and indexes for JSON fields you query frequently
• MySQL validates JSON on INSERT and UPDATE, which adds overhead but ensures data integrity
• Partial updates (JSON_SET, JSON_INSERT) are efficient because MySQL only modifies the changed part
• For JSON-heavy workloads, PostgreSQL JSONB is generally faster than MySQL JSON

FAQ

Is MySQL JSON as fast as PostgreSQL JSONB?

No. PostgreSQL's JSONB is a binary format that is faster to query and easier to index. MySQL's JSON is stored in an internal binary format that supports direct key access, but complex path queries and updates require parsing. For JSON-heavy workloads, PostgreSQL is the better choice.

Can I use JSON for full-text search?

Yes, but it is not ideal. You can create a generated column from JSON text and add a full-text index. But if you need complex JSON querying, PostgreSQL is better.

How big can a JSON column be?

MySQL JSON columns can store up to 1GB of data. But large JSON documents slow down queries and waste memory. Keep JSON documents small and structured.

Can I use JSON columns in JOIN conditions?

Yes, but performance will be poor without generated columns. Create a generated column from the JSON field you want to join on, index it, and use that column in your JOIN condition.

What is the difference between -> and ->> operators?

The -> operator returns the JSON value with quotes for strings. The ->> operator returns the value without quotes (unquoted). Use ->> when you need the actual string value for comparisons and display.

Real-World JSON Usage Scenario

A booking platform stores hotel room metadata in a JSON column. Each room type has different attributes: a standard room has bed count and view type, a suite has a living area and kitchenette, and a penthouse has a rooftop terrace and private pool. The structure varies too much for a fixed schema, so JSON is the right choice.

CREATE TABLE rooms (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hotel_id INT NOT NULL,
  room_type VARCHAR(50) NOT NULL,
  attributes JSON NOT NULL,
  price_per_night DECIMAL(10,2) NOT NULL,
  INDEX idx_hotel (hotel_id)
);

INSERT INTO rooms (hotel_id, room_type, attributes, price_per_night) VALUES
(1, 'standard', '{"beds": 2, "view": "garden", "wifi": true}', 99.00),
(1, 'suite', '{"beds": 2, "living_area": true, "kitchenette": true, "minibar": true}', 249.00),
(1, 'penthouse', '{"beds": 3, "rooftop_terrace": true, "private_pool": true, "butler": true}', 599.00);

The platform queries rooms by specific features. A customer searching for rooms with a kitchenette runs a query against the JSON attributes. Without a generated column and index, this would scan every row and parse the JSON for each one. With a generated column, the query uses an index and returns results in milliseconds.

-- Add generated columns for common queries
ALTER TABLE rooms
ADD COLUMN has_kitchenette TINYINT GENERATED ALWAYS AS (JSON_CONTAINS(attributes, 'true', '$.kitchenette')) VIRTUAL,
ADD COLUMN has_pool TINYINT GENERATED ALWAYS AS (JSON_CONTAINS(attributes, 'true', '$.private_pool')) VIRTUAL;

CREATE INDEX idx_kitchenette ON rooms(has_kitchenette);
CREATE INDEX idx_pool ON rooms(has_pool);

-- Fast indexed query
SELECT * FROM rooms WHERE has_kitchenette = 1 AND hotel_id = 1;

The lesson here is that JSON works well when the schema is genuinely flexible, but you still need to identify which fields will be queried frequently and create generated columns for those. Fields that are only displayed (like wifi or minibar) can stay in the JSON without generated columns. Fields that are searched or filtered need the generated column treatment to avoid full table scans.

Another pattern that works well is using JSON for the storage layer but creating separate summary tables for common queries. You store the full JSON document for completeness, but extract the fields you query into a regular table for performance. This is common in event logging systems where the raw event is stored as JSON, but a summary table tracks counts and aggregates for dashboards.