MySQL JSON syntax: (1) Extraction: -> gets JSON, ->> gets text, $.path for nested ($.notifications.email). (2) Modification: JSON_SET(col, '$.path', value) updates/adds, JSON_INSERT() adds only, JSON_REPLACE() updates only, JSON_REMOVE() deletes. (3) Creation: JSON_OBJECT(key, val, ...) creates object, JSON_ARRAY(val, ...) creates array, JSON_ARRAYAGG() aggregates. (4) Search: JSON_CONTAINS(col, val, path) checks containment, JSON_KEYS(col) lists keys, JSON_LENGTH(col) gets length. (5) Indexing: ADD COLUMN col AS (json->>'$.path') STORED, CREATE INDEX ON col.

JSON type: Validates on INSERT, stores as binary, use for flexible schemas (MySQL 5.7+)

Extraction: -> gets JSON, ->> gets text, $.path for nested ($.notifications.email)

Modification: JSON_SET() updates/adds, JSON_INSERT() adds only, JSON_REPLACE() updates only, JSON_REMOVE() deletes

Creation: JSON_OBJECT(key, val) creates object, JSON_ARRAY(val) creates array, JSON_ARRAYAGG() aggregates

Search: JSON_CONTAINS(col, val, path) checks containment, JSON_KEYS() lists keys, JSON_LENGTH() gets length

Indexing: ADD COLUMN col AS (json->>'$.path') STORED, CREATE INDEX ON col for 100x faster queries

Path syntax: $.key for top-level, $.key.nested for nested, $[0] for array index

JSON type (5.7+), text-based storage, -> and ->> operators, generated columns for indexing

JSONB type (binary, 2-3x faster), GIN indexes (no generated columns needed), richer operators (@>, ?, &&)

JSON functions (2016+), no JSON type (use NVARCHAR), OPENJSON() to parse, computed columns for indexing

JSON type (12c+), JSON_TABLE() to parse, function-based indexes, JSON search index

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

OR

Matches rows when at least one condition is TRUE.

condition_a OR condition_b

Use parentheses when mixing OR with AND.

EXISTS

Tests whether a correlated or non-correlated subquery returns at least one row.

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'

EXTRACT

Pulls a single date/time component such as year, month, day, or hour from a temporal value.

EXTRACT(YEAR FROM order_date)