MySQL JSON Functions: Complete Guide with Examples

Why Store JSON in MySQL?

Before MySQL 5.7, the only way to store JSON in a relational database was to serialize it as a TEXT or VARCHAR column. You could store the data, but querying inside it meant pulling entire rows into application code and parsing them there. It was slow, fragile, and made filtering on JSON attributes nearly impossible at scale.

MySQL 5.7 introduced a native JSON data type along with a full suite of JSON functions. MySQL 8.0 extended this further with JSON_TABLE, JSON_OVERLAPS, and schema validation. Today, MySQL's JSON support is mature enough to handle three common use cases:

• Flexible schemas — product catalogs, user preferences, and feature flags where different rows need different attributes
• API response storage — persisting webhook payloads, third-party API responses, or event logs without defining every field upfront
• Configuration storage — per-tenant settings, A/B test configurations, and feature toggles where a flat column-per-setting approach would require constant schema migrations

The key advantage of the native JSON type over TEXT is validation and performance. MySQL validates JSON on insert, rejecting malformed documents immediately. It also stores JSON in an optimized binary format that allows path-based access without parsing the entire document on every read.

This guide covers every major MySQL JSON function with working examples you can run today. All examples target MySQL 5.7+ and are compatible with MySQL 8.0.

JSON_EXTRACT — Reading JSON Data

JSON_EXTRACT(json_doc, path) is the foundation of MySQL JSON querying. It reads a value from a JSON document at a specified path. The path syntax always starts with $, which represents the root of the document.

Path syntax rules:

• $ — the root document
• $.key — access a top-level object key
• $.nested.key — access a nested object key
• $.array[0] — access the first element of an array (zero-indexed)
• $.array[*] — access all elements of an array

-- Sample table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES
('Running Shoe', '{"brand": "Nike", "color": "blue", "sizes": [8, 9, 10, 11], "weight_oz": 9.5}'),
('Laptop', '{"brand": "Dell", "ram_gb": 16, "storage": {"type": "SSD", "size_gb": 512}}'),
('Coffee Maker', '{"brand": "Breville", "color": "silver", "capacity_cups": 12}');

-- Extract a top-level string
SELECT name, JSON_EXTRACT(attributes, '$.brand') AS brand
FROM products;
-- Returns: "Nike", "Dell", "Breville" (with quotes)

-- Extract a nested value
SELECT name, JSON_EXTRACT(attributes, '$.storage.size_gb') AS ssd_size
FROM products
WHERE name = 'Laptop';
-- Returns: 512

-- Extract from an array by index
SELECT name, JSON_EXTRACT(attributes, '$.sizes[0]') AS smallest_size
FROM products
WHERE name = 'Running Shoe';
-- Returns: 8

Notice that JSON_EXTRACT returns strings with surrounding double quotes. The value "Nike" is returned as "Nike" with literal quotes included. This matters when comparing values in WHERE clauses — use JSON_UNQUOTE or the ->> shorthand to strip the quotes for clean string comparisons.

The -> and ->> Shorthand Operators

MySQL provides two operator shorthands that make JSON queries more readable. They are column-level operators, not standalone functions.

The -> operator is equivalent to JSON_EXTRACT(column, path). It returns the value with JSON encoding intact — strings include surrounding quotes.

The ->> operator is equivalent to JSON_UNQUOTE(JSON_EXTRACT(column, path)). It strips the quotes and returns a plain scalar value. This is what you almost always want for string comparisons and display.

-- Using -> (keeps quotes on strings)
SELECT name, attributes->'$.brand' AS brand
FROM products;
-- Returns: "Nike", "Dell", "Breville"

-- Using ->> (strips quotes)
SELECT name, attributes->>'$.brand' AS brand
FROM products;
-- Returns: Nike, Dell, Breville

-- Using ->> in a WHERE clause (correct)
SELECT name
FROM products
WHERE attributes->>'$.brand' = 'Nike';
-- Returns: Running Shoe

-- Using -> in a WHERE clause (will NOT match)
SELECT name
FROM products
WHERE attributes->'$.brand' = 'Nike';
-- Returns: 0 rows — because the stored value is "Nike" with quotes

This is one of the most common bugs when working with MySQL JSON for the first time. Always use ->> when filtering on string values in a WHERE clause.

JSON_OBJECT and JSON_ARRAY — Creating JSON

JSON_OBJECT(key, value, key, value, ...) constructs a JSON object from alternating key-value pairs. JSON_ARRAY(value, value, ...) constructs a JSON array from a list of values. Both are useful for building JSON dynamically from query results.

-- Build a JSON object from column values
SELECT
    JSON_OBJECT(
        'customer_id', id,
        'name', CONCAT(first_name, ' ', last_name),
        'email', email,
        'created', DATE_FORMAT(created_at, '%Y-%m-%d')
    ) AS customer_json
FROM customers
LIMIT 3;

-- Build a JSON array of product names per category
SELECT
    category,
    JSON_ARRAYAGG(name) AS products
FROM products
GROUP BY category;
-- Returns one row per category with all product names as a JSON array

-- Nest JSON_OBJECT inside JSON_ARRAY
SELECT JSON_ARRAY(
    JSON_OBJECT('env', 'production', 'debug', false),
    JSON_OBJECT('env', 'staging', 'debug', true)
) AS config_options;

JSON_ARRAYAGG (MySQL 5.7.22+) is particularly useful for aggregating rows into a JSON array in GROUP BY queries — similar to array_agg in PostgreSQL. It returns NULL for empty groups, so wrap it in COALESCE(JSON_ARRAYAGG(...), JSON_ARRAY()) if you need an empty array instead of NULL.

JSON_SET, JSON_INSERT, JSON_REPLACE — Updating JSON

MySQL provides three functions for writing values into an existing JSON document. They all take the same signature — function(json_doc, path, value [, path, value ...]) — but differ in their behavior when the path exists or does not exist.

Function	Path exists	Path does not exist
JSON_SET	Replaces the value	Inserts the value
JSON_INSERT	Does nothing	Inserts the value
JSON_REPLACE	Replaces the value	Does nothing

-- JSON_SET: upsert behavior (insert or replace)
UPDATE products
SET attributes = JSON_SET(
    attributes,
    '$.color', 'red',        -- replaces existing "blue"
    '$.in_stock', true       -- inserts new key
)
WHERE name = 'Running Shoe';

-- JSON_INSERT: safe add (never overwrites)
UPDATE products
SET attributes = JSON_INSERT(
    attributes,
    '$.discount_pct', 10     -- only inserted if key does not exist
)
WHERE name = 'Laptop';

-- JSON_REPLACE: update existing only
UPDATE products
SET attributes = JSON_REPLACE(
    attributes,
    '$.ram_gb', 32           -- only updated if '$.ram_gb' already exists
)
WHERE name = 'Laptop';

You can chain multiple path-value pairs in a single call. MySQL applies them left to right, so each subsequent operation sees the result of the previous one. This lets you do multiple updates in a single statement without nesting function calls.

JSON_REMOVE — Deleting Keys

JSON_REMOVE(json_doc, path [, path ...]) removes one or more keys or array elements from a JSON document. It returns the modified document without the specified paths. Paths that do not exist are silently ignored.

-- Remove a single key
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.color')
WHERE name = 'Coffee Maker';

-- Remove multiple keys in one call
UPDATE products
SET attributes = JSON_REMOVE(
    attributes,
    '$.weight_oz',
    '$.sizes[2]'     -- removes the third element (index 2) from the sizes array
)
WHERE name = 'Running Shoe';

-- Verify the result
SELECT attributes FROM products WHERE name = 'Running Shoe';
-- sizes array now has 3 elements: [8, 9, 11]

When removing array elements by index, note that the array shifts after each removal. If you remove index 1 and index 2 in the same call, the second removal operates on the array after the first removal. Remove array elements from the end first (highest index to lowest) if order matters.

JSON_CONTAINS and JSON_SEARCH — Querying JSON

JSON_CONTAINS(target, candidate [, path]) checks whether a JSON document contains a specific value. It returns 1 if found, 0 if not. JSON_SEARCH(json_doc, one_or_all, search_str) finds the path to a matching string value, similar to a search-within-document operation.

-- JSON_CONTAINS: does the sizes array contain size 10?
SELECT name
FROM products
WHERE JSON_CONTAINS(attributes->'$.sizes', '10');
-- Returns: Running Shoe

-- JSON_CONTAINS: does the document have a specific nested object?
SELECT name
FROM products
WHERE JSON_CONTAINS(
    attributes,
    '{"type": "SSD"}',
    '$.storage'
);
-- Returns: Laptop

-- JSON_SEARCH: find the path of a specific brand value
SELECT
    name,
    JSON_SEARCH(attributes, 'one', 'Breville') AS path_to_value
FROM products;
-- Returns: "$.brand" for the Coffee Maker row

-- JSON_SEARCH with wildcard: find any key containing "Ni" (uses % like LIKE)
SELECT
    name,
    JSON_SEARCH(attributes, 'one', 'Ni%') AS match_path
FROM products;
-- Returns: "$.brand" for the Running Shoe row

-- JSON_OVERLAPS (MySQL 8.0+): do two JSON arrays share any elements?
SELECT name
FROM products
WHERE JSON_OVERLAPS(attributes->'$.sizes', '[9, 10]');
-- Returns all products whose sizes array contains 9 or 10

The second argument to JSON_SEARCH is either 'one' (return the first match path) or 'all' (return an array of all matching paths). Use 'all' when the search string might appear in multiple keys and you need every occurrence.

Indexing JSON with Generated Columns

MySQL does not allow you to create an index directly on a JSON column or a JSON path expression. However, you can create a generated column that extracts a specific path, and then index that generated column. This is the standard MySQL pattern for making JSON queries fast at scale.

Generated columns come in two flavors. A virtual generated column computes its value on every read and stores nothing on disk — low storage cost, slightly more CPU on reads. A stored generated column persists the computed value to disk — uses extra storage but reads are faster since the value is pre-computed. For indexing purposes, virtual columns work fine.

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

-- Create an index on the generated column
CREATE INDEX idx_products_brand ON products(brand);

-- Now this query uses the index
SELECT name, brand
FROM products
WHERE brand = 'Nike';
-- EXPLAIN shows: key = idx_products_brand

-- You can also use the original JSON path — MySQL is smart enough
-- to use the index if the generated column expression matches
SELECT name
FROM products
WHERE attributes->>'$.brand' = 'Nike';
-- Also uses idx_products_brand

-- Numeric generated column for range queries
ALTER TABLE products
ADD COLUMN ram_gb INT
    GENERATED ALWAYS AS (attributes->>'$.ram_gb') STORED;

CREATE INDEX idx_products_ram ON products(ram_gb);

-- Range query now uses the index
SELECT name, ram_gb
FROM products
WHERE ram_gb >= 16
ORDER BY ram_gb DESC;

Generated column indexes make the difference between a full-table scan on every JSON query and sub-millisecond lookups. For any JSON attribute you filter or sort by regularly, a generated column index is worth adding.

Real-World Example: E-commerce Product Attributes

E-commerce is the classic use case for MySQL JSON. A clothing store sells shoes, shirts, and electronics. Each category has entirely different attributes — shoes have sizes and widths, shirts have sizes and fabric, electronics have RAM and storage. Rather than creating a sparse table with 50 columns where most are NULL for any given product, you store category-specific attributes in a JSON column.

CREATE TABLE catalog_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(50) NOT NULL,
    base_price DECIMAL(10,2) NOT NULL,
    attributes JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO catalog_products (sku, name, category, base_price, attributes) VALUES
('SHOE-001', 'Air Runner Pro', 'footwear',  129.99,
 '{"brand":"Nike","gender":"unisex","sizes":[7,8,9,10,11,12],"colors":["black","white","blue"],"drop_mm":8}'),
('SHIRT-042', 'Merino Base Layer', 'apparel', 79.99,
 '{"brand":"Patagonia","gender":"mens","sizes":["S","M","L","XL"],"fabric":"merino","warmth_rating":3}'),
('ELEC-201', 'ProBook 15', 'electronics', 1199.00,
 '{"brand":"HP","ram_gb":16,"storage_gb":512,"storage_type":"NVMe","display_inches":15.6,"weight_kg":1.8}');

-- Find all footwear available in size 10
SELECT sku, name, base_price
FROM catalog_products
WHERE category = 'footwear'
  AND JSON_CONTAINS(attributes->'$.sizes', '10');

-- Find electronics with at least 16GB RAM under $1500
SELECT sku, name, base_price,
       attributes->>'$.ram_gb' AS ram,
       attributes->>'$.brand' AS brand
FROM catalog_products
WHERE category = 'electronics'
  AND (attributes->>'$.ram_gb') + 0 >= 16
  AND base_price < 1500.00;

-- Build a structured response object per product
SELECT
    sku,
    name,
    JSON_OBJECT(
        'price',    base_price,
        'brand',    attributes->>'$.brand',
        'category', category,
        'specs',    attributes
    ) AS product_card
FROM catalog_products
ORDER BY base_price;

The + 0 in the numeric comparison (attributes->>'$.ram_gb') + 0 >= 16 forces MySQL to cast the extracted string to a number. Without it, MySQL compares lexicographically and '9' would be greater than '16'. Alternatively, use CAST(attributes->>'$.ram_gb' AS UNSIGNED) for explicit type safety. With a generated column and index, this query becomes a true numeric range scan.

How AI2SQL Generates JSON Queries

Writing MySQL JSON queries from scratch is error-prone. The path syntax is easy to mistype, the difference between -> and ->> trips up everyone at least once, and remembering which function does upsert versus insert-only is not something most people keep in working memory.

AI2SQL handles this by letting you describe the query in plain English and generating the correct MySQL JSON syntax instantly. You describe your schema — including which columns are JSON and what their structure looks like — and then ask for what you need.

Example prompts that AI2SQL handles well:

• "Find all products where the attributes JSON contains a brand equal to Nike"
• "Update the attributes column to set color to red for all footwear products"
• "Group products by the brand field inside attributes JSON and count each"
• "Add a generated column that extracts the ram_gb from the attributes JSON and create an index on it"

AI2SQL also handles cross-dialect differences. If you are migrating from PostgreSQL (which uses -> and ->> operators with different semantics) or from MongoDB query syntax to MySQL, you can describe what you want and get the MySQL-specific version without manually translating each function.

For teams writing production queries against JSON columns, the real value is speed and correctness on first try. Instead of opening the MySQL docs to remember whether JSON_INSERT or JSON_SET is the right function for a given situation, you describe the behavior you want and let the AI generate and explain the right choice.

Frequently Asked Questions

Which MySQL version introduced native JSON support?

MySQL 5.7.8 introduced the native JSON data type and JSON functions. MySQL 8.0 extended support with JSON_TABLE, JSON_OVERLAPS, JSON_SCHEMA_VALID, and multi-valued indexes on JSON arrays. Before MySQL 5.7, JSON had to be stored as TEXT with no built-in query functions.

What is the difference between JSON_SET, JSON_INSERT, and JSON_REPLACE?

JSON_SET inserts if the path is missing and replaces if it exists (upsert). JSON_INSERT only inserts if the path does not exist — it never overwrites. JSON_REPLACE only replaces if the path already exists — it does nothing for missing paths. For most update operations, JSON_SET is what you want.

How do I index a JSON field in MySQL?

Create a generated column that extracts the JSON path, then index the generated column. For example: ADD COLUMN brand VARCHAR(100) GENERATED ALWAYS AS (attributes->>'$.brand') VIRTUAL, then CREATE INDEX idx_brand ON products(brand). MySQL 8.0 also supports multi-valued indexes for indexing JSON arrays directly using INDEX ((CAST(attributes->'$.sizes' AS UNSIGNED ARRAY))).

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

-> is shorthand for JSON_EXTRACT — it returns values with JSON encoding, so strings include surrounding double quotes. ->> is shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) — it strips the quotes and returns a plain string. Always use ->> when comparing string values in WHERE clauses to avoid quote-mismatch bugs.