MySQL JSON Functions: A Complete Guide to JSON_EXTRACT, JSON_SET, and Multi-Valued Indexes

MySQL 8.4 gives relational applications a native JSON type, path operators, typed extraction, relational projection, document modification, schema validation, and two practical indexing patterns. Those features are useful when part of a row is genuinely flexible. They do not turn every relational model into a good document model, and they do not make arbitrary JSON predicates indexable.

What the native JSON type guarantees

A MySQL JSON column rejects syntactically invalid JSON and stores valid documents in an internal format designed for element access. That is stronger than putting JSON-looking text in a LONGTEXT column, but it validates syntax rather than the application's business shape. A valid document can still omit a required key, put a string where the application expects a number, or contain an unexpectedly large array.

MySQL normalizes stored JSON. It can discard whitespace, resolve duplicate object keys, and order object keys for its internal representation. The manual explicitly warns that key ordering is not a stable contract between releases. Applications should compare decoded values, not serialized formatting or object-key order.

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  account_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(24) NOT NULL,
  attributes JSON NOT NULL,
  created_at DATETIME(6) NOT NULL,
  version BIGINT UNSIGNED NOT NULL DEFAULT 0,
  CHECK (JSON_TYPE(attributes) = 'OBJECT')
);

This design keeps operational keys relational while allowing a controlled attribute document. Before choosing JSON, write down which fields may vary, their maximum practical size, how they are validated, and which paths must support filtering or joins. The related MySQL JSON schema and performance guide covers that boundary in more detail.

Extract values without losing type intent

JSON_EXTRACT(document, path) returns a JSON value. For a JSON column, attributes->'$.channel' is shorthand for the same operation. A JSON string therefore remains JSON-quoted. The ->> operator is shorthand for unquoting the extracted value, which is convenient for display but does not by itself establish the numeric, temporal, or collation semantics a query needs.

SELECT
  id,
  attributes->'$.delivery' AS delivery_json,
  attributes->>'$.delivery.method' AS delivery_method,
  JSON_VALUE(
    attributes,
    '$.delivery.priority' RETURNING UNSIGNED
    NULL ON EMPTY
    ERROR ON ERROR
  ) AS delivery_priority
FROM orders
WHERE id = ?;

JSON_VALUE() is usually clearer when a scalar must become a specific SQL type. Its RETURNING clause can request numeric, temporal, character, or JSON output, while ON EMPTY and ON ERROR make missing-path and conversion behavior explicit. Without RETURNING, MySQL 8.4 returns VARCHAR(512). A JSON null at the selected path becomes SQL NULL. Invalid JSON or an invalid path is still an SQL error; ON ERROR is not a blanket exception handler for malformed input.

Choose error behavior by contract. Returning NULL for an optional key can be correct. Silently returning NULL for a required price or permission flag can hide bad data. For required fields, validate on write and consider ERROR ON EMPTY and ERROR ON ERROR in diagnostic queries and migrations.

Project arrays with JSON_TABLE()

JSON_TABLE() turns matches from a document into rows and typed columns. It is an implicitly lateral table function, so it can refer to a table listed earlier in the FROM clause; an alias is mandatory. FOR ORDINALITY numbers matches, EXISTS PATH reports whether a location is present, and NESTED PATH expands nested arrays.

SELECT o.id, line.line_no, line.sku, line.quantity
FROM orders AS o
JOIN JSON_TABLE(
  o.attributes,
  '$.lines[*]' COLUMNS (
    line_no FOR ORDINALITY,
    sku VARCHAR(64) PATH '$.sku' ERROR ON EMPTY ERROR ON ERROR,
    quantity INT UNSIGNED PATH '$.quantity' DEFAULT '1' ON EMPTY ERROR ON ERROR
  )
) AS line ON TRUE
WHERE o.account_id = ?;

Each array element can create a result row, so document cardinality matters. Filter the parent relation as tightly as the query permits, set deliberate types and error rules, and test empty arrays, absent paths, JSON null, objects where scalars are expected, and values that overflow the target type. Use a normalized child table when elements require foreign keys, independent updates, uniqueness, or frequent joins.

Modify and merge with precise semantics

The modification functions return a new logical document. JSON_SET() replaces existing paths and adds missing ones. JSON_INSERT() adds missing paths but leaves existing values unchanged. JSON_REPLACE() changes existing paths and ignores missing ones. JSON_REMOVE() removes paths that exist. When one call contains several path-value pairs, MySQL evaluates them from left to right; each pair sees the result of the previous pair.

UPDATE orders
SET attributes = JSON_SET(
      attributes,
      '$.delivery.method', ?,
      '$.delivery.expedited', CAST(? AS JSON)
    ),
    version = version + 1
WHERE id = ?
  AND version = ?;

Bind the method as a string and the boolean as valid JSON text. The version predicate is an optimistic-concurrency boundary: the caller can detect that another transaction changed the row instead of unknowingly overwriting a newer decision. A single UPDATE is atomic for the row, but business workflows spanning a read and a later write still need locking or version checks.

Do not treat the merge functions as synonyms. JSON_MERGE_PATCH() follows merge-patch behavior for objects: later members replace earlier members, a JSON null in the patch removes the corresponding object member, and an array patch replaces the earlier array as a unit. JSON_MERGE_PRESERVE() retains duplicate values, often by combining them into arrays, and concatenates arrays. That preservation can change a scalar into an array, so use it only when that output is part of the data contract. Write unit tests for duplicate keys, nulls, nested objects, and arrays before adopting either function in an API patch endpoint.

Index scalar paths deliberately

A JSON column is not indexed directly as one ordinary B-tree value. For a scalar path, expose an intentional SQL value through a generated column or a functional index. A named generated column makes the query contract visible and lets the application avoid repeating an expression:

ALTER TABLE orders
  ADD COLUMN channel_key VARCHAR(24)
    GENERATED ALWAYS AS (
      JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.channel'))
    ) STORED,
  ADD INDEX idx_orders_account_channel (account_id, channel_key);

EXPLAIN SELECT id
FROM orders
WHERE account_id = ? AND channel_key = ?;

JSON_UNQUOTE() matters for string extraction because JSON_EXTRACT() otherwise returns a quoted JSON string. Choose the generated column's length, character set, collation, and null behavior from the application contract. Profile existing rows before adding it: an unexpected object, oversized string, or incompatible conversion can make the DDL fail.

MySQL 8.4 can also index a scalar JSON_VALUE() expression directly:

CREATE INDEX idx_orders_priority ON orders (
  (JSON_VALUE(attributes, '$.delivery.priority' RETURNING UNSIGNED))
);

The query expression must match the indexed expression and result type closely enough for the optimizer to use it. Small differences in casts, operand order, quoting, or collation can change the plan. Querying a named generated column is often easier to maintain across application code. Whichever form you use, inspect EXPLAIN after loading representative distributions rather than assuming that an existing index will be selected.

Index selected JSON arrays with a multi-valued index

InnoDB multi-valued indexes create multiple secondary-index entries for one row. They are intended for same-typed scalar values held in a JSON array. This example indexes numeric category identifiers:

CREATE INDEX idx_products_category_ids ON products (
  (CAST(attributes->'$.category_ids' AS UNSIGNED ARRAY))
);

EXPLAIN SELECT id, name
FROM products
WHERE 42 MEMBER OF (attributes->'$.category_ids');

The optimizer can use this class of index for supported predicates involving MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS(). It is not a general array index. Only one multi-valued key part is allowed in an index; it cannot provide ordering, range scans, a covering index, a primary key, or a foreign-key target. Empty arrays add no index record, JSON null values are not permitted in indexed arrays, and creating the index uses ALGORITHM=COPY in MySQL 8.4. Those constraints affect both correctness and rollout time.

Validate every existing element against the cast type before DDL, decide how missing and empty arrays should behave, and cap array growth at the application boundary. Each element adds index work to writes. If an array is large, independently mutable, or joined often, a child table with one row per relationship is usually easier to constrain and operate.

Understand when a partial in-place update is possible

JSON_SET() does not guarantee a partial physical write. MySQL can optimize an update in place only when the target is a declared JSON column; the statement uses JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(); and the same column is both the input and target. Nested combinations of those functions can qualify.

For the storage optimization, changes must replace existing object or array values rather than add new elements. A replacement normally cannot be larger than the value it replaces, unless space released by an earlier partial update is sufficient. JSON_STORAGE_FREE() reports space freed by partial updates. Functions such as JSON_ARRAY_APPEND() are not on the eligibility list. MySQL still produces the correct logical document when an update is ineligible; it falls back to replacing the stored document.

Compact row-based binary logging is a separate setting. binlog_row_value_options=PARTIAL_JSON can log eligible JSON after-images more compactly, but it does not prove that the on-page update was partial. Test replication, change-data-capture consumers, backups, and point-in-time recovery before changing binary-log behavior.

Validate syntax, shape, and evolution

The native type validates JSON syntax. JSON_SCHEMA_VALID() can validate a document against the Draft 4 JSON Schema features supported by MySQL, while JSON_SCHEMA_VALIDATION_REPORT() provides diagnostic details. A CHECK constraint can enforce a schema, but the schema must be supplied inline because a check expression cannot refer to a variable.

ALTER TABLE orders ADD CONSTRAINT chk_order_attributes
CHECK (JSON_SCHEMA_VALID(
  '{"type":"object","required":["channel"],"properties":{"channel":{"type":"string","maxLength":24}}}',
  attributes
));

Treat that schema as versioned application logic. Audit existing rows before enabling the constraint, test the exact server version's supported Draft 4 behavior, and plan how old and new document shapes coexist during rolling deployments. MySQL does not support external schema resources through $ref, so do not design enforcement around a remote schema registry.

Parameterize data and allowlist query structure

Bind document values and row identifiers through the database driver's prepared-statement interface. Never concatenate request text into SQL. JSON paths, return types, operators, sort directions, and index expressions are query structure; some must be literals, and indexed expressions depend on stable syntax. If a product lets users choose a field, map a small allowlist of public field names to hard-coded SQL fragments. Reject unknown fields rather than escaping them into a path.

For structured input, serialize once with a real JSON encoder and bind it, using CAST(? AS JSON) where the SQL function must receive a document rather than a JSON string scalar. Apply request-size and array-length limits before the query. Do not hand-build JSON with string concatenation; JSON_OBJECT() and JSON_ARRAY() are safer when values already exist as SQL parameters.

A production test plan

• Correctness: cover missing paths, JSON null versus SQL NULL, wrong types, empty and large arrays, duplicate patch keys, Unicode, and concurrent updates.
• Plans: run EXPLAIN for common, rare, and absent values. Use EXPLAIN ANALYZE only when it is safe to execute the statement, because it runs the query.
• Writes: measure DDL duration, row size, index size, redo and binary-log volume, lock time, replica lag, and rollback behavior on a production-shaped copy.
• Operations: restore a backup containing the generated and multi-valued indexes, exercise failover, and verify that connectors and CDC tools preserve the intended JSON values.

There is no universal JSON performance number worth copying into a capacity plan. Document size, path selectivity, array cardinality, index choice, buffer residency, write rate, and hardware all change the result. The MySQL Performance Schema guide shows how to measure the deployed workload, while the MySQL architecture guide explains where row and index costs appear.

Official MySQL 8.4 documentation

• The JSON data type, paths, normalization, indexing, and partial updates
• JSON function reference
• Extraction, search predicates, and JSON_VALUE()
• JSON_TABLE() syntax and behavior
• Modification and merge functions
• Generated-column index matching
• Multi-valued indexes and restrictions
• JSON Schema validation functions
• Replication of JSON documents