JSON Extract in MySQL: JSON_EXTRACT, -> and ->> Reference

TL;DR: To extract a value from JSON in MySQL, use JSON_EXTRACT(col, '$.key') or its shorthand col->'$.key'. Both return JSON, so strings keep their quotes; use col->>'$.key' to get plain unquoted text, and index the field through a generated column.

JSON_EXTRACT(json_doc, path[, path] ...) returns the part of a JSON document that a path selects, available since the JSON type arrived in MySQL 5.7.8. The path starts at $ (the document root), uses .key for object members and [n] for zero-based array positions. -> is shorthand for JSON_EXTRACT (MySQL 5.7.9) and ->> is shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) (MySQL 5.7.13). If no path matches, the result is NULL (MySQL JSON search functions).

Syntax Table: Functions, Operators and Paths

You want | Syntax | Returns for {"name": "Ana"}

JSON path syntax

Wildcard paths (*, **) and ranges always return an array (MySQL JSON path syntax).

How Do I Extract a Value From JSON in MySQL?

Use JSON_EXTRACT or ->> with a path. Given a customers table with a JSON profile column:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  profile JSON
);

INSERT INTO customers VALUES
 (1, 'ana@example.com', '{"plan": "pro", "seats": 5, "address": {"city": "Pune"}, "tags": ["vip", "trial"]}'),
 (2, 'ben@example.com', '{"plan": "free", "seats": 1, "address": {"city": "Austin"}, "tags": []}');

SELECT id,
       JSON_EXTRACT(profile, '$.plan')      AS plan_json,
       profile->>'$.plan'                   AS plan,
       profile->>'$.address.city'           AS city,
       profile->>'$.tags[0]'                AS first_tag
FROM customers;

Filter on a JSON value the same way:

SELECT email
FROM customers
WHERE profile->>'$.plan' = 'pro'
  AND JSON_EXTRACT(profile, '$.seats') >= 5;

Numbers compare correctly as JSON values, but compare strings through ->>: profile->'$.plan' = 'pro' compares a JSON string against SQL text and is a common source of empty results.

What Is the Difference Between -> and ->>?

-> returns JSON and ->> returns unquoted text. col->'$.k' equals JSON_EXTRACT(col, '$.k'); col->>'$.k' equals JSON_UNQUOTE(JSON_EXTRACT(col, '$.k')).

Both operators need a column on the left; for an expression or a string literal, use the function form.

JSON_VALUE: Cast a Scalar to a SQL Type

JSON_VALUE(doc, path RETURNING type), added in MySQL 8.0.21, extracts a scalar and casts it in one step. It is the cleanest choice for sums and date math:

SELECT SUM(JSON_VALUE(profile, '$.seats' RETURNING UNSIGNED)) AS total_seats
FROM customers;

You can add DEFAULT ... ON EMPTY and DEFAULT ... ON ERROR to control missing or bad values.

JSON_TABLE: Turn a JSON Array Into Rows

JSON_TABLE (MySQL 8.0.4+) expands an array into a relational result, which beats extracting $[0], $[1] one at a time:

SELECT c.id, t.tag
FROM customers c,
     JSON_TABLE(c.profile, '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')) AS t;

How Do I Index a JSON Field in MySQL?

You can't index a JSON column directly; index a generated column or a functional key part that extracts the field (MySQL secondary indexes and generated columns).

Generated column (MySQL 5.7+):

ALTER TABLE customers
  ADD COLUMN plan VARCHAR(20) AS (profile->>'$.plan') VIRTUAL,
  ADD INDEX idx_plan (plan);

-- The optimizer uses idx_plan for this query
SELECT id FROM customers WHERE profile->>'$.plan' = 'pro';

Functional index (MySQL 8.0.13+):

CREATE INDEX idx_city
  ON customers ((CAST(profile->>'$.address.city' AS CHAR(50)) COLLATE utf8mb4_bin));

The CAST ... COLLATE utf8mb4_bin matters: ->> returns utf8mb4_bin text, and the query must match the indexed expression for MySQL to use it.

Multi-valued index for arrays (MySQL 8.0.17+):

CREATE TABLE orders (
  id INT PRIMARY KEY,
  data JSON,
  INDEX idx_skus ((CAST(data->'$.sku_ids' AS UNSIGNED ARRAY)))
);

SELECT id FROM orders WHERE 1042 MEMBER OF (data->'$.sku_ids');

Multi-valued indexes serve MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS (CREATE INDEX, multi-valued). Check with EXPLAIN that the index appears under key.

Common JSON_EXTRACT Errors

• `ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract`: the column holds text that isn't valid JSON. Find bad rows with WHERE JSON_VALID(col) = 0.
• `ERROR 3143 (42000): Invalid JSON path expression`: usually a key with a space or a dash that isn't quoted. Write '$."order-id"'.
• Result is `NULL`: the path doesn't exist, or keys are case-sensitive ($.Plan is not $.plan).
• Result has quotes: you used JSON_EXTRACT or ->; switch to ->>.

Version Notes: MySQL vs MariaDB

MariaDB's function reference is at MariaDB JSON_EXTRACT. On Amazon RDS, Aurora MySQL, PlanetScale and Google Cloud SQL, the behaviour follows the MySQL version underneath.

If your team keeps product events or Stripe metadata in JSON columns and the people asking questions don't know path syntax, AI for Database lets them ask in plain English ("customers on the pro plan in Pune") and shows the ->> SQL it ran against your MySQL database. Try AI for Database free.

Related: the SQL cheat sheet, finding duplicates with SQL, COUNTIF in SQL, Postgres vs MySQL and our MySQL integration.