Lesson 17 intermediate

JSON and Full-Text Search

Store flexible user settings in a JSON column: build documents with JSON_OBJECT, read them with -> and ->>, JSON_EXTRACT and JSON_VALUE, change them with JSON_SET, JSON_INSERT, JSON_REPLACE and JSON_REMOVE, search arrays with MEMBER OF and JSON_CONTAINS, turn JSON into rows with JSON_TABLE, aggregate into JSON, and index it; then add word search to Orbit with FULLTEXT indexes, MATCH ... AGAINST, boolean mode and the ngram parser.

Two features in one lesson, because Orbit needs both: per-user settings whose keys change with every app release, and a search box that finds notes by words. MySQL handles each without extra infrastructure.

Storing JSON

Lesson 5 added a settings JSON column to users. Fill it for Ana and Ben; Chen keeps the defaults (NULL):

UPDATE users
SET settings = JSON_OBJECT(
  'theme', 'dark',
  'language', 'pt-BR',
  'notifications', JSON_OBJECT('email', TRUE, 'push', FALSE)
)
WHERE id = 1;

UPDATE users
SET settings = '{"theme": "light", "language": "en", "shortcuts": ["n", "s", "/"]}'
WHERE id = 2;

MySQL validates every document on write; an invalid one fails with error 3140 (“Invalid JSON text”). It stores a binary format that lets the server read one key without parsing the whole document.

Reading values

A JSON path starts with $ (the whole document), then .key for object members and [n] for array elements, counting from 0. Two operators read paths:

  • col->'$.path' returns JSON (strings keep their quotes); it is short for JSON_EXTRACT(col, '$.path').
  • col->>'$.path' returns the value as plain text; it is short for JSON_UNQUOTE(JSON_EXTRACT(...)).
SELECT name,
       settings->'$.theme'                AS theme_json,
       settings->>'$.theme'               AS theme,
       settings->'$.notifications.email'  AS email_on,
       settings->>'$.shortcuts[0]'        AS first_shortcut
FROM users ORDER BY id;
+------------+------------+-------+----------+----------------+
| name       | theme_json | theme | email_on | first_shortcut |
+------------+------------+-------+----------+----------------+
| Ana Silva  | "dark"     | dark  | true     | NULL           |
| Ben Carter | "light"    | light | NULL     | n              |
| Chen Wei   | NULL       | NULL  | NULL     | NULL           |
+------------+------------+-------+----------+----------------+
3 rows in set (0.00 sec)

A missing path returns NULL, not an error. Use ->> whenever you compare or display text: WHERE settings->>'$.theme' = 'dark'.

JSON_VALUE (MySQL 8.0.21+) extracts and converts in one step, with a default for missing values:

SELECT name,
       JSON_VALUE(settings, '$.font_size' RETURNING UNSIGNED DEFAULT 16 ON EMPTY) AS font_size
FROM users ORDER BY id;

Changing documents

You rarely rewrite a whole document. These functions return a modified copy that you assign back:

FunctionExisting keyMissing key
JSON_SET(doc, path, value, ...)ReplacesAdds
JSON_INSERT(doc, path, value, ...)Leaves aloneAdds
JSON_REPLACE(doc, path, value, ...)ReplacesLeaves alone
JSON_REMOVE(doc, path, ...)RemovesNothing
JSON_ARRAY_APPEND(doc, path, value)Appends to the array at path
UPDATE users
SET settings = JSON_SET(settings, '$.theme', 'light', '$.font_size', 14)
WHERE id = 1;

SELECT settings FROM users WHERE id = 1;
+-----------------------------------------------------------------------------------------------------------+
| settings                                                                                                  |
+-----------------------------------------------------------------------------------------------------------+
| {"theme": "light", "language": "pt-BR", "font_size": 14, "notifications": {"push": false, "email": true}} |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Look at the key order: MySQL does not preserve the order you wrote. It sorts object keys by length, then alphabetically, to make lookups fast. Never rely on key order in JSON.

Merging two documents: JSON_MERGE_PATCH(a, b) follows RFC 7396, where keys in b replace keys in a and null removes a key, which is exactly what a “PATCH /settings” endpoint needs. JSON_MERGE_PRESERVE instead combines duplicate keys into arrays.

Tip: When JSON_SET, JSON_REPLACE or JSON_REMOVE change a column in place, InnoDB can perform a partial update and, with binlog_row_value_options = PARTIAL_JSON, log only the change instead of the whole document. Assigning a completely new document always rewrites it.

Searching inside documents

SELECT name FROM users WHERE '/' MEMBER OF (settings->'$.shortcuts');
SELECT name FROM users WHERE JSON_CONTAINS(settings, '"s"', '$.shortcuts');
SELECT name FROM users WHERE JSON_OVERLAPS(settings->'$.shortcuts', '["x", "n"]');

All three return Ben. MEMBER OF tests one value against an array, JSON_CONTAINS tests a whole candidate document, and JSON_OVERLAPS is true when two arrays share an element.

Inspecting documents:

FunctionExampleResult for Ana
JSON_KEYS(doc)JSON_KEYS(settings)["theme", "language", "font_size", "notifications"]
JSON_LENGTH(doc, path)JSON_LENGTH(settings)4
JSON_TYPE(value)JSON_TYPE(settings->'$.font_size')INTEGER
JSON_CONTAINS_PATH(doc, 'one', path)..., '$.shortcuts'0
JSON_VALID(text)JSON_VALID('{"a": 1}')1
JSON_PRETTY(doc)Indented output for reading

From JSON to rows: JSON_TABLE

JSON_TABLE turns parts of a document into a relational table you can join, filter and group:

SELECT u.name, s.pos, s.shortcut
FROM users u,
     JSON_TABLE(u.settings, '$.shortcuts[*]'
       COLUMNS (
         pos      FOR ORDINALITY,
         shortcut VARCHAR(5) PATH '$'
       )) AS s
ORDER BY u.id, s.pos;
+------------+-----+----------+
| name       | pos | shortcut |
+------------+-----+----------+
| Ben Carter |   1 | n        |
| Ben Carter |   2 | s        |
| Ben Carter |   3 | /        |
+------------+-----+----------+
3 rows in set (0.00 sec)

FOR ORDINALITY numbers the rows; PATH picks a value relative to each element. Users without shortcuts produce no rows; use LEFT JOIN JSON_TABLE(...) ON TRUE to keep them. JSON_TABLE is also the easiest way to load a JSON array posted by a client into a table in one INSERT ... SELECT.

From rows to JSON

JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG and JSON_OBJECTAGG build documents from query results, so MySQL can return exactly what an API sends:

SELECT JSON_OBJECT(
  'id', u.id,
  'name', u.name,
  'notes', (SELECT JSON_ARRAYAGG(n.title) FROM notes n WHERE n.user_id = u.id)
) AS doc
FROM users u WHERE u.id = 2;

For Ben this returns {"id": 2, "name": "Ben Carter", "notes": [...]} with his two titles. JSON_ARRAYAGG has no ORDER BY option; sort in a derived table first when the order of elements matters.

Indexing JSON

You cannot index a JSON column directly. Index an expression extracted from it instead.

For a scalar value, use a functional index. ->> returns text with the utf8mb4_bin collation, so cast it and match the collation your queries will use:

ALTER TABLE users ADD INDEX idx_users_theme
  ((CAST(settings->>'$.theme' AS CHAR(20)) COLLATE utf8mb4_bin));

SELECT name FROM users WHERE CAST(settings->>'$.theme' AS CHAR(20)) COLLATE utf8mb4_bin = 'dark';

A generated column (lesson 5) achieves the same and is easier to read in queries: theme VARCHAR(20) AS (settings->>'$.theme') VIRTUAL plus a normal index on theme.

For arrays, a multi-valued index (MySQL 8.0.17+) indexes every element and is used by MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS:

ALTER TABLE users ADD INDEX idx_users_shortcuts
  ((CAST(settings->'$.shortcuts' AS CHAR(5) ARRAY)));

When to use JSON at all? It is a good fit for settings, feature flags, API payloads and attributes that vary between rows. Values you join on, filter on constantly or need constraints for belong in real columns.

Full-text search

Orbit’s search box should find notes by the words in their title and body. LIKE '%word%' scans every row (lesson 13). A FULLTEXT index stores words instead:

ALTER TABLE notes ADD FULLTEXT INDEX ft_notes (title, body);

Query it with MATCH(columns) AGAINST(text), naming exactly the columns of the index:

SELECT id, title FROM notes
WHERE MATCH(title, body) AGAINST('database');
+----+------------+
| id | title      |
+----+------------+
|  3 | Book ideas |
+----+------------+
1 row in set (0.00 sec)

Natural language mode

The default mode treats the search text as words, scores each row by relevance and returns matches ordered by that score when MATCH is in WHERE. Select the score to show or sort by it:

SELECT id, title, MATCH(title, body) AGAINST('backup policy') AS score
FROM notes
WHERE MATCH(title, body) AGAINST('backup policy')
ORDER BY score DESC;

Words in the stopword list (the, a, for, is and others) are ignored, and so are words shorter than innodb_ft_min_token_size, which is 3 by default. A search for “is” or “go” finds nothing for that reason.

Boolean mode

IN BOOLEAN MODE gives users operators:

OperatorMeaningExample
+wordMust be present+deploy +migrations
-wordMust be absent+notes -meeting
word*Prefix matchdetect*
"a phrase"Exact phrase"backup policy"
no operatorOptional, raises the scoremysql tips
SELECT id, title FROM notes
WHERE MATCH(title, body) AGAINST('+deploy +migrations' IN BOOLEAN MODE);
+----+------------------+
| id | title            |
+----+------------------+
|  2 | Deploy checklist |
+----+------------------+
1 row in set (0.00 sec)

Boolean mode does not sort by relevance automatically; add ORDER BY with the score if you need it.

Other options

  • WITH QUERY EXPANSION runs the search twice, adding words from the best matches the second time. It finds related notes but also a lot of noise.
  • For Chinese, Japanese and Korean text, which has no spaces between words, create the index WITH PARSER ngram; it indexes overlapping character sequences (ngram_token_size, default 2).
  • Changes to innodb_ft_min_token_size or the stopword list only take effect after rebuilding the index (ALTER TABLE notes DROP INDEX ft_notes, ADD FULLTEXT ft_notes (title, body)).
  • Full-text search in MySQL is solid for a search box over thousands to a few million documents. For typo tolerance, synonyms, facets or very large corpora, a dedicated engine such as OpenSearch or Meilisearch is the next step.

Quick reference

TaskSQL
Build a documentJSON_OBJECT('k', v, ...), JSON_ARRAY(...)
Read as JSON / as textcol->'$.k' / col->>'$.k'
Read with type and defaultJSON_VALUE(col, '$.k' RETURNING UNSIGNED DEFAULT 0 ON EMPTY)
Add or replaceJSON_SET(col, '$.k', v)
RemoveJSON_REMOVE(col, '$.k')
Element in array'x' MEMBER OF (col->'$.arr')
JSON to rowsJSON_TABLE(col, '$.arr[*]' COLUMNS (...))
Index a scalarINDEX ((CAST(col->>'$.k' AS CHAR(20)) COLLATE utf8mb4_bin))
Index an arrayINDEX ((CAST(col->'$.arr' AS CHAR(20) ARRAY)))
Word searchFULLTEXT (a, b) + MATCH(a, b) AGAINST('...')
Search operatorsAGAINST('+a -b c*' IN BOOLEAN MODE)

Exercise

settings holds {“theme”: “dark”}. What does settings->’$.theme’ return compared with settings->>’$.theme’?

  1. Both return dark
  2. “dark” with quotes as JSON, and dark as plain text
  3. dark, and NULL because ->> only works on arrays
  4. An error, because paths need JSON_EXTRACT
Show answer

Answer: B. -> returns a JSON value, so strings keep their quotes. ->> unquotes the value, which is what you want for display and comparisons.

Exercise

Which function changes a JSON key only if it already exists?

  1. JSON_SET
  2. JSON_INSERT
  3. JSON_REPLACE
  4. JSON_MERGE_PRESERVE
Show answer

Answer: C. JSON_REPLACE replaces existing values and ignores missing paths. JSON_SET also adds missing keys, and JSON_INSERT only adds.

Exercise

A user searches for “go” and full-text search returns nothing, although a note says “go live friday”. Why?

  1. Full-text indexes only cover the title
  2. Natural language mode requires quotes
  3. The note was inserted after the index was created
  4. Words shorter than innodb_ft_min_token_size (3 by default) are not indexed
Show answer

Answer: D. InnoDB skips tokens shorter than the minimum length, as well as stopwords. Lowering the setting requires rebuilding the full-text index.

Key takeaways

  • Use JSON for settings and variable attributes; keep join and filter columns relational.
  • -> returns JSON, ->> returns text; missing paths give NULL. Key order is not preserved.
  • JSON_SET, JSON_INSERT, JSON_REPLACE and JSON_REMOVE edit documents; JSON_TABLE turns them into rows.
  • Index JSON through functional indexes, generated columns or multi-valued indexes.
  • FULLTEXT indexes with MATCH ... AGAINST replace LIKE '%word%'; boolean mode adds +, -, * and phrases.

FAQ

How big can a JSON document be?

Up to max_allowed_packet (64 MB by default). In practice, keep documents small: large documents make every update rewrite more data and increase memory use when they are read.

Is a JSON column slower than normal columns?

Reading a key from MySQL’s binary JSON format is fast, but the optimizer knows little about values inside documents unless you index them, and constraints and foreign keys cannot reach inside them. For hot query paths, extract the value into a generated column with an index.

Why does my full-text search return rows in a strange order?

In natural language mode, results are ordered by relevance only when MATCH appears in WHERE and there is no other ORDER BY. In boolean mode there is no implicit ordering. Select the MATCH(...) expression as a score and order by it explicitly.

Can I search JSON with a FULLTEXT index?

Not directly. Extract the text into a stored generated column and put the FULLTEXT index on that column.

↑ ↓ navigate Enter open Esc close