How to Store JSON Data in MySQL and Query It?

Using MySQL 5.7 and above, you can directly store and query JSON data through JSON data types; 2. Define JSON columns and insert legal JSON data when creating a table, and MySQL will automatically verify its validity; 3. Use the -> and ->> operators to extract JSON field values, among which ->> can be used to remove string quotations for comparison; 4. Search for JSON content in WHERE clauses through functions such as JSON_CONTAINS, CAST, etc., and note that numerical comparison requires type conversion; 5. Use the JSON_SET, JSON_REPLACE and JSON_REMOVE functions to modify the JSON field; 6. Although JSON columns cannot be directly indexed, indexes can be created by generating columns (such as virtual columns based on JSON_UNQUOTE) to improve query performance; 7. It is recommended to extract frequently queried fields into generated columns and establish indexes to avoid over-reliance on large JSON object queries, and maintain core data structure to achieve a balance of flexibility and performance.

Storing and querying JSON data in MySQL is straightforward if you're using MySQL 5.7 or later, as these versions have built-in support for JSON data types. This allows you to store semi-structed data flexible while still benefiting from SQL querying capabilities. Here's how to do it effectively.

1. Storing JSON Data in MySQL

To store JSON, use the JSON data type when defining a column in your table.

Create a Table with a JSON Column

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

Insert JSON Data

You can insert valid JSON objects or arrays:

 INSERT INTO users (name, profile) VALUES (
    'Alice',
    '{
        "age": 30,
        "city": "New York",
        "hobbies": ["reading", "cycling"],
        "contact": {
            "email": "alice@example.com",
            "phone": "123-456-7890"
        }
    }'
);

MySQL validates the JSON on insertion. Invalid JSON will cause an error.

2. Querying JSON Data

MySQL provides functions and operators to extract and search within JSON fields.

Extract Values Using -> and ->>

• column->'$.path' returns the JSON value (with quotes for strings).
• column->>'$.path' returns the unquoted, scalar value (better for comparisons).

 -- Get the full contact object
SELECT profile->'$.contact' AS contact FROM users WHERE name = 'Alice';

-- Get the unquoted email
SELECT profile->>'$.contact.email' AS email FROM users WHERE name = 'Alice';

-- Get age and city
SELECT 
    profile->>'$.age' AS age,
    profile->>'$.city' AS city
FROM users;

Search Within JSON

Use JSON_CONTAINS() or path expressions in WHERE clauses.

 -- Find users who have "cycling" in hobbies
SELECT name FROM users 
WHERE JSON_CONTAINS(profile->'$.hobbies', '"cycling"');

-- Find users older than 25
SELECT name FROM users 
WHERE CAST(profile->>'$.age' AS UNSIGNED) > 25;

-- Find users from New York
SELECT name FROM users 
WHERE profile->>'$.city' = 'New York';

Note: Since ->> returns text, you may need to cast values (eg, to SIGNED , UNSIGNED , or DECIMAL ) for numeric comparisons.

3. Updating JSON Fields

Use JSON_SET() , JSON_REPLACE() , or JSON_REMOVE() to modify JSON content.

 -- Add or update a field
UPDATE users 
SET profile = JSON_SET(profile, '$.age', 31, '$.city', 'Boston')
WHERE name = 'Alice';

-- Append to an array
UPDATE users 
SET profile = JSON_ARRAY_APPEND(profile, '$.hobbies', 'swimming')
WHERE name = 'Alice';

-- Remove a field
UPDATE users 
SET profile = JSON_REMOVE(profile, '$.contact.phone')
WHERE name = 'Alice';

4. Indexing JSON Data (Indirectly)

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

Example: Index the Email Field Inside JSON

 ALTER TABLE users 
ADD COLUMN email VARCHAR(255) 
GENERATED ALWAYS AS (JSON_UNQUOTE(profile->>'$.contact.email')));

CREATE INDEX idx_email ON users(email);

Now queries filtering on email can use the index:

 SELECT name FROM users WHERE email = 'alice@example.com';

This greatly improves performance for frequent JSON-based looksups.

Best Practices

• Use JSON for flexible or optional attributes (eg, user preferences, settings).
• Keep frequently queried or critical fields (like email, status) as regular columns or use generated columns with indexes.
• Avoid storing large JSON blobs if you query them often—normalize when possible.
• Validate JSON structure in your application to prevent errors.

Storing JSON in MySQL gives you flexibility without sacrificing too much performance—especially when you combine it with generated columns and proper indexing. It's ideal for evolving schemas or hierarchical data that don't fit neatly into rigid tables.

The above is the detailed content of How to Store JSON Data in MySQL and Query It?. For more information, please follow other related articles on the PHP Chinese website!