JSON, generated columns and window functions

Store and query semi-structured data, promote extracted values into indexable columns, and replace self-joins and correlated subqueries with window functions and CTEs.

The JSON type and its functions

INSERT INTO events (id, payload) VALUES
  (1, '{"type":"click","user":{"id":42},"tags":["a","b"]}');

SELECT JSON_EXTRACT(payload, '$.user.id') AS user_id FROM events;
SELECT payload ->> '$.type' AS type FROM events;        -- unquoted scalar

UPDATE events
SET payload = JSON_SET(payload, '$.handled', TRUE)
WHERE id = 1;

SELECT id FROM events
WHERE payload ->> '$.type' = 'click'
  AND JSON_CONTAINS(payload, '"b"', '$.tags');

A JSON column stores a binary representation, so path extraction is not a string parse. MySQL can still index an extracted value, but only through a generated column or a functional index.
->> returns a value; -> returns a JSON document. That is why payload -> '$.type' yields the quoted string rather than the bare text.
Comparisons inside JSON are typed. A JSON string and a JSON number are different values, so cast explicitly when you filter.
A JSON document is not a way to avoid schema design. Anything you filter, sort or join on should be promoted to a real typed column.

Generated columns

ALTER TABLE events
  ADD COLUMN event_type VARCHAR(20)
      GENERATED ALWAYS AS (payload ->> '$.type') VIRTUAL,
  ADD INDEX idx_event_type (event_type);

ALTER TABLE orders
  ADD COLUMN total_with_tax DECIMAL(12,2)
      GENERATED ALWAYS AS (ROUND(total * 1.20, 2)) STORED;

EXPLAIN SELECT id FROM events WHERE event_type = 'click';

Kind	Storage	Use when
`VIRTUAL`	Computed on read	Index or filter on it; costs no disk
`STORED`	Written on insert and update	You read it often, or you need it in a foreign key or a partition key

The expression must be deterministic and must reference columns of the same row. Adding a stored generated column to a large table rewrites it, so plan the change the same way you would plan any other migration.

Window functions and CTEs

WITH ranked AS (
  SELECT id, customer_id, total,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY placed_at DESC) AS rn,
         SUM(total)   OVER (PARTITION BY customer_id)  AS customer_total,
         LAG(total)   OVER (PARTITION BY customer_id
                            ORDER BY placed_at)        AS previous_total
  FROM orders
  WHERE status = 'paid'
)
SELECT * FROM ranked
WHERE rn <= 3
ORDER BY customer_id, rn;

Function	Behaviour
`ROW_NUMBER()`	1, 2, 3, 4 — every row gets a distinct number
`RANK()`	1, 2, 2, 4 — ties share a rank and leave gaps
`DENSE_RANK()`	1, 2, 2, 3 — ties share a rank with no gaps
`LAG()` / `LEAD()`	The previous or next row value inside the window
`SUM() OVER (...)`	A running total when the window is ordered
`NTILE(n)`	Splits the partition into n roughly equal buckets

A window function does not collapse rows. Unlike GROUP BY, it adds a computed column and keeps every row.
With an ORDER BY inside the window, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total. Leave the ORDER BY out for a whole-partition total.
A window alias cannot be referenced in the same SELECT's WHERE. Wrap the query in a CTE or a derived table and filter outside.
WITH RECURSIVE walks hierarchies, with cte_max_recursion_depth as the guard against a cycle that never terminates.

💡

Top-N per group is the classic use: number rows inside each group with ROW_NUMBER(), then keep the rows numbered 1 to n. It replaces the correlated subquery that used to be required and is usually easier for the optimiser to execute.

FAQ

JSON column or separate columns?

Separate typed columns for anything you filter, sort, join or constrain; a JSON column for sparse or genuinely variable attributes you mostly read as a whole. Indexing JSON works, but a real typed column is clearer and cheaper.

Does a window function replace GROUP BY?

No. GROUP BY reduces many rows to one per group, while a window function adds a value and keeps every row. Use them together: aggregate in one CTE, then rank the aggregate in the next.

Querying data: SELECT, joins and aggregation Views, stored procedures, functions and triggers

Last refreshed 2026-09-18.

JSON, generated columns and window functions

The JSON type and its functions

Generated columns

Window functions and CTEs

FAQ

Related