Back to Blog

Working with JSON in MySQL

Complete Guide to Extract & Manipulate JSON Data

JSON in MySQL provides powerful capabilities for storing and querying semi-structured data. Since MySQL 5.7, you can store JSON documents in columns and use specialized functions to extract, manipulate, and query JSON data efficiently. This guide covers everything you need to know about working with JSON in MySQL, including structure, extraction methods, nested JSON handling, and practical examples.

Whether you're storing configuration data, user preferences, or complex nested structures, understanding MySQL's JSON functions will help you build efficient queries and maintain flexible data models.

Understanding JSON in MySQL

JSON Data Type

MySQL provides a native JSON data type that validates JSON documents and stores them efficiently. JSON columns are stored as binary format (similar to BLOB) but with automatic validation.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  metadata JSON
);

JSON Structure

JSON in MySQL supports objects, arrays, strings, numbers, booleans, and NULL. You can nest objects and arrays to any depth.

{
  "name": "John Doe",
  "email": "john@example.com",
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "country": "USA"
  },
  "tags": ["developer", "mysql", "json"],
  "preferences": {
    "theme": "dark",
    "notifications": true
  }
}

JSON Functions Overview

Extraction Functions

  • JSON_EXTRACT() - Extract value by path
  • -> - Extract JSON (returns JSON type)
  • ->> - Extract text (returns text type)
  • JSON_UNQUOTE() - Remove quotes from JSON string

Modification Functions

  • JSON_SET() - Add or update field
  • JSON_REPLACE() - Update existing field
  • JSON_INSERT() - Add new field
  • JSON_REMOVE() - Remove field

Utility Functions

  • JSON_VALID() - Validate JSON
  • JSON_LENGTH() - Get array/object length
  • JSON_SEARCH() - Search for value
  • JSON_TABLE() - Convert JSON to rows

Creation Functions

  • JSON_OBJECT() - Create JSON object
  • JSON_ARRAY() - Create JSON array
  • JSON_MERGE_PRESERVE() - Merge JSON objects

10 Practical Examples

Extract Simple Value from JSON

Extract a single value from a JSON column using JSON_EXTRACT or -> operator

Query:

SELECT 
  id,
  JSON_EXTRACT(metadata, '$.name') AS name,
  metadata->'$.email' AS email
FROM users;

Explanation:

Extracts name and email from metadata JSON column. JSON_EXTRACT() and -> operator are equivalent. The -> operator returns JSON type, while ->> returns text.

Use Case:

Basic JSON value extraction, simple data retrieval

Extract from Nested JSON

Extract values from nested JSON objects using dot notation

Query:

SELECT 
  id,
  JSON_EXTRACT(profile, '$.address.city') AS city,
  JSON_EXTRACT(profile, '$.address.country') AS country,
  profile->>'$.contact.phone' AS phone
FROM users;

Explanation:

Extracts nested values using dot notation. profile->>$.contact.phone uses ->> to return text directly without quotes.

Use Case:

Complex nested structures, hierarchical data extraction

Extract from JSON Array

Extract elements from JSON arrays using array index

Query:

SELECT 
  id,
  JSON_EXTRACT(tags, '$[0]') AS first_tag,
  JSON_EXTRACT(tags, '$[1]') AS second_tag,
  JSON_LENGTH(tags) AS tag_count
FROM products;

Explanation:

Extracts first and second elements from tags array. $[0] is first element, $[1] is second. JSON_LENGTH() returns array length.

Use Case:

Array element access, list processing, tag extraction

Extract All Array Elements

Extract all elements from a JSON array using JSON_TABLE

Query:

SELECT 
  u.id,
  u.name,
  tag.value AS tag
FROM users u
CROSS JOIN JSON_TABLE(
  u.tags,
  '$[*]' COLUMNS (value VARCHAR(50) PATH '$')
) AS tag;

Explanation:

JSON_TABLE() converts JSON array into rows. Each array element becomes a separate row, allowing you to work with array data as relational data.

Use Case:

Flattening arrays, array-to-rows conversion, tag processing

Filter Rows Based on JSON Value

Use JSON values in WHERE clause to filter results

Query:

SELECT * FROM products 
WHERE JSON_EXTRACT(metadata, '$.status') = 'active'
  AND JSON_EXTRACT(metadata, '$.price') > 100;

Explanation:

Filters products where status is active and price is greater than 100. JSON values can be used in WHERE, ORDER BY, and other clauses.

Use Case:

Conditional filtering, JSON-based search, dynamic queries

Update JSON Field

Update specific fields within a JSON column

Query:

UPDATE users 
SET metadata = JSON_SET(metadata, '$.last_login', NOW())
WHERE id = 123;

Explanation:

JSON_SET() updates or adds a field in JSON. Other functions: JSON_REPLACE() (only updates existing), JSON_INSERT() (only adds new).

Use Case:

Updating JSON fields, adding metadata, tracking changes

Merge JSON Objects

Combine multiple JSON objects into one

Query:

SELECT 
  id,
  JSON_MERGE_PRESERVE(
    JSON_OBJECT('id', id, 'name', name),
    metadata
  ) AS combined_json
FROM users;

Explanation:

JSON_MERGE_PRESERVE() merges JSON objects, keeping all keys. JSON_MERGE_PATCH() overwrites duplicate keys with later values.

Use Case:

Combining JSON data, merging configurations, data aggregation

Search Within JSON

Search for values or keys within JSON using JSON_SEARCH

Query:

SELECT * FROM products 
WHERE JSON_SEARCH(metadata, 'one', 'electronics', NULL, '$**.category') IS NOT NULL;

Explanation:

JSON_SEARCH() finds path to a value. 'one' returns first match, 'all' returns all. $**.category searches all category fields recursively.

Use Case:

Deep JSON search, finding values, recursive search

Validate JSON Structure

Check if a string is valid JSON before processing

Query:

SELECT 
  id,
  JSON_VALID(data) AS is_valid_json,
  CASE 
    WHEN JSON_VALID(data) THEN JSON_EXTRACT(data, '$.name')
    ELSE 'Invalid JSON'
  END AS name
FROM table_name;

Explanation:

JSON_VALID() returns 1 if valid JSON, 0 otherwise. Always validate before extracting to avoid errors.

Use Case:

Data validation, error prevention, safe JSON processing

Aggregate JSON Values

Calculate aggregates from JSON numeric values

Query:

SELECT 
  category,
  COUNT(*) AS product_count,
  SUM(CAST(JSON_EXTRACT(metadata, '$.price') AS DECIMAL(10,2))) AS total_revenue,
  AVG(CAST(JSON_EXTRACT(metadata, '$.price') AS DECIMAL(10,2))) AS avg_price
FROM products
GROUP BY category;

Explanation:

Extracts price from JSON, casts to DECIMAL for calculations. CAST() is essential for numeric operations on JSON values.

Use Case:

JSON-based calculations, financial aggregations, statistics

Tips & Tricks

Use ->> for Text Output

The ->> operator automatically unquotes JSON strings. Use it when you need text output instead of JSON type.

-- Instead of:
SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name;

-- Use:
SELECT data->>'$.name' AS name;

Index JSON Columns

Create generated columns and index them for better performance on JSON queries.

ALTER TABLE products 
ADD COLUMN category_text VARCHAR(50) 
  GENERATED ALWAYS AS (metadata->>'$.category') STORED,
ADD INDEX idx_category (category_text);

Use JSON_TABLE for Complex Queries

JSON_TABLE() converts JSON arrays/objects into relational rows, enabling JOINs and complex queries.

SELECT u.name, tag.value 
FROM users u
CROSS JOIN JSON_TABLE(
  u.tags, '$[*]' 
  COLUMNS (value VARCHAR(50) PATH '$')
) AS tag;

Validate Before Processing

Always use JSON_VALID() before extracting data to prevent errors from malformed JSON.

SELECT * FROM table_name 
WHERE JSON_VALID(json_column) = 1
  AND JSON_EXTRACT(json_column, '$.field') IS NOT NULL;

Use JSON_SET for Updates

JSON_SET() adds or updates fields. JSON_REPLACE() only updates existing fields. JSON_INSERT() only adds new fields.

-- Updates or adds field
UPDATE users 
SET metadata = JSON_SET(metadata, '$.last_login', NOW());

-- Only updates existing
UPDATE users 
SET metadata = JSON_REPLACE(metadata, '$.status', 'active');

Cast JSON Numbers for Calculations

JSON numbers are stored as strings. Always CAST to numeric types before calculations.

SELECT 
  SUM(CAST(JSON_EXTRACT(data, '$.price') AS DECIMAL(10,2))) AS total
FROM products;

Use JSON_MERGE_PRESERVE for Combining

JSON_MERGE_PRESERVE() keeps all keys from both objects. JSON_MERGE_PATCH() overwrites duplicates.

SELECT JSON_MERGE_PRESERVE(
  JSON_OBJECT('a', 1, 'b', 2),
  JSON_OBJECT('b', 3, 'c', 4)
) AS result;
-- Result: {"a": 1, "b": [2, 3], "c": 4}

Escape Special Characters in Paths

Use double backslashes to escape special characters in JSON paths.

-- For key with special characters
SELECT JSON_EXTRACT(data, '$."user-name"') AS name;

-- For keys with dots
SELECT JSON_EXTRACT(data, '$."user.email"') AS email;

Best Practices

✅ Use Generated Columns for Indexing

Create generated columns from frequently queried JSON fields and add indexes. This dramatically improves query performance on JSON columns.

✅ Validate JSON Before Processing

Always use JSON_VALID() before extracting data to prevent errors from malformed JSON. This is especially important when data comes from external sources.

✅ Use ->> for Text Comparisons

When comparing JSON values in WHERE clauses, use ->> operator to get text output. This avoids issues with JSON type comparisons and improves readability.

✅ Cast JSON Numbers for Calculations

JSON numbers are stored as strings. Always CAST to DECIMAL or numeric types before performing calculations or aggregations.

⚠️ Consider Schema Design

While JSON provides flexibility, consider if your data should be in JSON or normalized tables. Use JSON for truly variable or semi-structured data, not as a replacement for proper schema design.

Format Your MySQL Queries

Use our SQL Formatter tool to format, validate, and beautify your MySQL JSON queries for better readability and debugging.

Open SQL Formatter