Introduction

MySQL can store JSON natively, which is useful when part of your data is nested or varies from row to row. Searching that JSON efficiently requires knowing the difference between extracting a value, checking whether a path contains something, and indexing frequently queried attributes.

Querying JSON by Path

Suppose you store order metadata in a JSON column.

1CREATE TABLE orders (
2    id BIGINT PRIMARY KEY AUTO_INCREMENT,
3    details JSON NOT NULL
4);
5
6INSERT INTO orders (details) VALUES
7('{"status":"paid","customer":{"name":"Ava","tags":["vip","email"]}}'),
8('{"status":"pending","customer":{"name":"Ben","tags":["sms"]}}');

To filter by a scalar field, extract the value at a JSON path.

1SELECT
2    id,
3    details->>'$.customer.name' AS customer_name
4FROM orders
5WHERE details->>'$.status' = 'paid';

The ->> operator returns an unquoted text value, which is convenient for comparisons. The -> operator returns JSON. That difference matters because comparing JSON and comparing strings are not the same thing.

Searching Arrays and Nested Values

For arrays or nested objects, JSON_CONTAINS and JSON_SEARCH are often the right tools.

SELECT id
FROM orders
WHERE JSON_CONTAINS(details, JSON_QUOTE('vip'), '$.customer.tags');

That query finds rows where the tags array contains vip.

If you need to search for a text value anywhere under a path, use JSON_SEARCH.

SELECT id
FROM orders
WHERE JSON_SEARCH(details, 'one', 'Ben', NULL, '$.customer.name') IS NOT NULL;

JSON_SEARCH returns the matching path when it finds one, so checking for IS NOT NULL is the usual pattern in a WHERE clause.

Making Searches Faster

JSON queries can be flexible, but they are not free. If you frequently filter on the same JSON property, create a generated column and index that column.

1ALTER TABLE orders
2ADD COLUMN status_text VARCHAR(20)
3    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(details, '$.status'))) STORED,
4ADD INDEX idx_orders_status_text (status_text);

After that, the query becomes simpler and more index-friendly:

SELECT id
FROM orders
WHERE status_text = 'paid';

This is a practical pattern when JSON is convenient for storage but some fields behave like normal relational columns in your query workload.

When JSON Fits and When It Does Not

JSON works well for optional metadata, nested payloads, or data coming from external APIs. It is less ideal when the same property is queried constantly, joined across tables, or constrained by relational rules. In those cases, a standard column is often the better design.

A good rule is to keep the flexible part flexible and promote hot fields into indexed columns once the access pattern becomes clear.

Common Pitfalls

The most common mistake is forgetting that JSON_EXTRACT returns JSON. If you compare the result directly to a plain SQL string, the match may fail because of quoting. Use ->>, JSON_UNQUOTE, or a generated column when you need text semantics.

Another mistake is expecting MySQL to optimize arbitrary JSON expressions automatically. Without generated columns or functional indexes, many JSON filters become full table scans.

Be careful with path spelling and array positions. JSON paths are case-sensitive with respect to the keys stored in the document, and one wrong path silently produces NULL, which can make debugging slow.

Finally, do not turn an entire relational schema into one JSON blob just because MySQL allows it. JSON is a tool for specific flexibility needs, not a replacement for thoughtful schema design.

Summary

• Use ->> or JSON_UNQUOTE(JSON_EXTRACT(...)) when you need text values from JSON.
• Use JSON_CONTAINS for membership checks and JSON_SEARCH for path-based text lookup.
• Add generated columns and indexes for frequently queried JSON attributes.
• Expect poor performance if you search JSON heavily without indexing strategy.
• Keep JSON for flexible data, and move hot fields into normal columns when needed.