CONCEPT Cited by 2 sources

MySQL JSON extraction operator¶

MySQL exposes two shorthand operators for pulling a value out of a JSON column at a given JSONPath:

Operator	Long form	Result
`->`	`JSON_EXTRACT(col, path)`	Quoted JSON value (still a `JSON` expression)
`->>`	`JSON_UNQUOTE(JSON_EXTRACT(col, path))`	Unquoted scalar (string, number, boolean)

Both were added in MySQL 5.7 as ergonomic shorthand — the underlying functions remain available and are still used when the expression is programmatically generated.

Worked example¶

Given a songs table with a JSON column songs holding a string array:

SELECT songs->'$[3]' FROM songs;
-- +-----------------+
-- | songs -> '$[3]' |
-- +-----------------+
-- | "Ghost"         |
-- +-----------------+

SELECT songs->>'$[3]' FROM songs;
-- +------------------+
-- | songs ->> '$[3]' |
-- +------------------+
-- | Ghost            |
-- +------------------+

(Source: sources/2026-04-21-planetscale-the-mysql-json-data-type.)

The -> form preserves JSON semantics (note the surrounding quotes in the output). The ->> form produces the underlying scalar — almost always what you want in a WHERE comparison, a functional index expression, or a column projection rendered to an application.

Why it matters for indexing¶

The ->> form is the load-bearing one for functional indexing: a WHERE clause like

WHERE properties->>'$.request.email' = 'x@example.com'

can be backed by a functional index declared as

ALTER TABLE events ADD INDEX email_idx (
  ((properties->>'$.request.email'))
);

or by a named generated column materialising the same expression. The optimiser rewrites the raw-expression WHERE clause to use the index if the two expressions match byte-for-byte. See patterns/index-json-key-via-generated-column for the production-safe recipe.

Collation gotcha¶

When used with a functional index, JSON_UNQUOTE returns utf8mb4_bin but CAST(... AS CHAR(N)) defaults to utf8mb4_0900_ai_ci. A functional index that casts the extracted value silently fails to be used unless the CAST explicitly requests COLLATE utf8mb4_bin. Canonicalised as the functional-index collation-mismatch gotcha in Aaron Francis's indexing-JSON post ingest.

Composition with mutation¶

JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_SET, etc. take a JSON column + a path — the same path syntax used by -> and ->> on the read side. Worked example from the post:

UPDATE songs SET songs = JSON_ARRAY_APPEND(songs, '$', "One last song");
UPDATE songs SET songs = JSON_ARRAY_INSERT(songs, '$[0]', "First song");