> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# JSON in MySQL

> How to store, query, and index JSON data in MySQL: the JSON column type, path expressions, JSON functions, and how to index JSON fields with generated columns.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

MySQL's native `JSON` column type validates JSON on insert, stores it in a binary format for efficient access, and provides a set of functions for reading and modifying individual fields without parsing the full document.

## Storing JSON

```sql theme={null}
CREATE TABLE products (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL,
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES (
    'Laptop',
    '{"brand": "Acme", "ram_gb": 16, "tags": ["portable", "pro"]}'
);
```

MySQL validates the JSON on insert. Inserting malformed JSON raises an error.

## Reading JSON Fields

Use `JSON_EXTRACT(column, path)` or the `->` shorthand:

```sql theme={null}
-- These are equivalent
SELECT JSON_EXTRACT(attributes, '$.brand') FROM products;
SELECT attributes -> '$.brand' FROM products;
```

Both return a JSON value — the string `"Acme"` with quotes. To get a plain string without quotes, use `->>` (which is shorthand for `JSON_UNQUOTE(JSON_EXTRACT(...))`):

```sql theme={null}
SELECT attributes ->> '$.brand' FROM products;  -- returns: Acme
```

Path syntax:

* `$.key` — access an object field
* `$.array[0]` — access array element by index
* `$.array[*]` — all elements of an array
* `$.**` — recursive wildcard (all values at any depth)

## Filtering on JSON Fields

```sql theme={null}
-- Find all 16GB RAM products
SELECT name FROM products
WHERE attributes ->> '$.ram_gb' = '16';

-- Check if a key exists
SELECT name FROM products
WHERE JSON_EXTRACT(attributes, '$.brand') IS NOT NULL;

-- Check if an array contains a value
SELECT name FROM products
WHERE JSON_CONTAINS(attributes -> '$.tags', '"portable"');
```

Note that `->>` returns a string, so comparing to `'16'` (not `16`) is correct unless you cast explicitly: `CAST(attributes ->> '$.ram_gb' AS UNSIGNED)`.

## Modifying JSON

```sql theme={null}
-- Add or update a field
UPDATE products
SET attributes = JSON_SET(attributes, '$.color', 'silver')
WHERE id = 1;

-- Remove a field
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.tags')
WHERE id = 1;

-- Insert only if the key doesn't exist
UPDATE products
SET attributes = JSON_INSERT(attributes, '$.warranty_years', 2)
WHERE id = 1;
```

The key functions:

| Function | Behavior |
| :- | :- |
| `JSON_SET(doc, path, val)` | Set the value — inserts or replaces |
| `JSON_INSERT(doc, path, val)` | Insert only if the path doesn't exist |
| `JSON_REPLACE(doc, path, val)` | Replace only if the path exists |
| `JSON_REMOVE(doc, path)` | Remove the value at the path |

## Indexing JSON Fields

The JSON column itself can't be indexed directly. To index a specific JSON field, create a generated column and index that:

```sql theme={null}
ALTER TABLE products
    ADD COLUMN brand VARCHAR(100) GENERATED ALWAYS AS (attributes ->> '$.brand') VIRTUAL;

CREATE INDEX idx_brand ON products (brand);
```

Now `WHERE attributes ->> '$.brand' = 'Acme'` can use `idx_brand`. MySQL recognizes that the generated column expression matches the query condition.

This is covered in more detail in [Choosing MySQL Data Types](https://villagesql.com/docs/en/guides/choosing-data-types.md) — it's the recommended pattern whenever you query a JSON field frequently enough to need an index.

## JSON vs Normalized Columns

| | JSON column | Normalized columns |
| :- | :- | :- |
| Schema flexibility | Any structure, no migration needed | Fixed structure, migration required |
| Indexing | Only via generated columns | Index any column directly |
| Query complexity | Path expressions, JSON functions | Standard SQL |
| Joins | Not possible on JSON keys | Full JOIN support |
| Storage | Larger (binary JSON overhead) | More compact |
| Data integrity | No column-level constraints | `NOT NULL`, `DEFAULT`, foreign keys |

Use JSON when the structure is genuinely variable (user-configurable attributes, event metadata, third-party API payloads). Use normalized columns when the structure is fixed and you need to filter, join, or aggregate on the fields.

## Frequently Asked Questions

#### Can I index a JSON array for containment queries?

Not directly with a standard index. For `JSON_CONTAINS` queries, the only option is a full table scan unless you extract the array into a separate child table (normalized) or use a full-text index on a generated column that flattens the array. For high-throughput containment queries, normalization is the better path.

#### What's the difference between `->` and `->>`?

`->` returns a JSON value (strings include surrounding quotes). `->>` returns the unquoted string. For display, concatenation, and anything that consumes the text, `->>` is what you want. Equality still works through `->`, though: MySQL coerces the other operand to JSON first, so `attributes -> '$.brand' = 'Acme'` does match.

#### Does storing JSON hurt performance?

Reading a single field from a large JSON document requires parsing the binary structure, which is faster than parsing text but slower than reading a native column. For frequently accessed fields, the generated column + index pattern brings read performance in line with native columns.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `ERROR 3140: Invalid JSON` on insert | Fix the JSON syntax — MySQL validates on insert |
| `WHERE attributes -> '$.key' = 'value'` returns no rows | The quoting is not the cause — that comparison is JSON-aware and does match. Check the path itself, and that the value is a JSON string rather than a number |
| JSON field query not using an index | Add a generated column on the path expression and index that |
| `JSON_CONTAINS` with nested array not matching | Check path and quoting — string values inside `JSON_CONTAINS` must be quoted JSON: `'"value"'` not `'value'` |

## See also

* [Choosing MySQL Data Types](https://villagesql.com/docs/en/guides/choosing-data-types.md) — when to use JSON vs normalized columns
* [Generated Columns in MySQL](https://villagesql.com/docs/en/guides/generated-columns.md) — indexing JSON fields with virtual generated columns
* [Making HTTP Requests from SQL](https://villagesql.com/docs/en/guides/http-requests-in-mysql.md) — parsing JSON from HTTP API responses


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.