MENU


JSON, Full Text, and Spatial Data

Not every value fits a scalar column. Product attributes differ by kind, reviews are free text searched by word, and stores sit on a globe. MySQL keeps all three in ordinary InnoDB tables: a binary JSON type with paths and indexes, FULLTEXT indexes with relevance ranking, and geometry types with ellipsoidal math. The small shop data set is deliberate: it exposes the tokenizer and axis-order rules that surprise people on real tables.

3.9.1 Storing JSON Documents in a Column

A JSON column is not TEXT with a label. MySQL parses each value on write, rejects invalid ones, and stores a binary format whose header indexes keys and offsets, so the server jumps to $.pages without scanning. Parsing normalizes too: whitespace goes, a duplicated key keeps its last value, and keys are sorted in an order the manual says may change. Product 1's 37 characters occupy 40 bytes (JSON_STORAGE_SIZE()). Since MySQL 8.0.17, JSON_SCHEMA_VALID() checks a document against a JSON Schema (draft 4), and inside a CHECK constraint it turns the column into a typed document store.

INSERT INTO products (sku, title, category_id, price, attributes)

VALUES ('BK-BAD-01', 'Broken', 2, 1.00, '{"format": "ebook", pages: 10}');

SELECT CAST('{"pages": 412, "format": "paperback", "pages": 400}' AS JSON) AS normalized;

ALTER TABLE products ADD CONSTRAINT chk_attributes CHECK (JSON_SCHEMA_VALID('{

"type": "object",

"properties": {

"format": {"enum": ["paperback", "hardcover", "ebook"]},

"pages": {"type": "integer", "minimum": 1}}}', attributes));

UPDATE products SET attributes = '{"format": "audiobook"}' WHERE id = 1;

Listing 3.76: Validation, normalization, and a JSON Schema CHECK constraint

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 20 in

value for column 'products.attributes'.

+---------------------------------------+

| normalized |

+---------------------------------------+

| {"pages": 400, "format": "paperback"} |

+---------------------------------------+

ERROR 3819 (HY000): Check constraint 'chk_attributes' is violated.

Output of Listing 3.76 JSON_SCHEMA_VALIDATION_REPORT() names the failing rule (for {"pages": 0}, "schema-failed-keyword": "minimum"). With no required list, the mug's color still passes. Promote values that every row has, and that you join or sort on, to real columns.

3.9.2 Reading and Writing JSON Values

A path starts at $, descends with .key or [n], and accepts [last], ranges such as [0 to 1], the wildcards .* and [*], and ** for any depth. col->path is shorthand for JSON_EXTRACT() and returns JSON, so attributes->'$.format' prints "ebook" in quotes; col->>path also unquotes it to ebook, the form to display or compare. JSON_VALUE(attributes, '$.pages' RETURNING UNSIGNED) extracts one scalar with a type. For writing, JSON_INSERT() adds only missing paths, JSON_REPLACE() changes only existing ones, and JSON_SET() does both; each returns a new document.

SET @j = '{"a": 1, "b": [2, 3]}';

SELECT 'JSON_INSERT' AS fn, JSON_INSERT(@j, '$.a', 10, '$.c', 4) AS result

UNION ALL SELECT 'JSON_SET', JSON_SET(@j, '$.a', 10, '$.c', 4)

UNION ALL SELECT 'JSON_REPLACE', JSON_REPLACE(@j, '$.a', 10, '$.c', 4)

UNION ALL SELECT 'JSON_REMOVE', JSON_REMOVE(@j, '$.b[0]');

Listing 3.77: The write functions compared

+--------------+--------------------------------+

| fn | result |

+--------------+--------------------------------+

| JSON_INSERT | {"a": 1, "b": [2, 3], "c": 4} |

| JSON_SET | {"a": 10, "b": [2, 3], "c": 4} |

| JSON_REPLACE | {"a": 10, "b": [2, 3]} |

| JSON_REMOVE | {"a": 1, "b": [3]} |

+--------------+--------------------------------+

Output of Listing 3.77 Under the hood: An UPDATE that assigns JSON_SET(), JSON_REPLACE() or JSON_REMOVE() of the same column, adds no member, and does not grow the value is done in place. Replacing product 6's "paperback" with "ebook" left 4 free bytes, reported by JSON_STORAGE_FREE().

3.9.3 Constructing Objects and Arrays

JSON_OBJECT(key, value, ...) and JSON_ARRAY(value, ...) build documents from SQL values and map the types: JSON_OBJECT('sku', sku, 'price', price, 'in_stock', stock > 0) on the out-of-stock ebook gave {"sku": "BK-SQL-02", "price": 29.00, "in_stock": false}, with the DECIMAL exact and the comparison a real boolean. JSON_ARRAY_APPEND() adds to the end of an array and JSON_ARRAY_INSERT() at a position such as '$.tags[0]'. The trap is passing JSON as a plain string: JSON_SET(attributes, '$.tags', '["mysql", "sql"]') stores one string, which JSON_TYPE() reports as STRING. Wrap such text in CAST(... AS JSON), as the listing does when it gives the books the tags arrays that the rest of this section searches, flattens and indexes.

UPDATE products p

JOIN (VALUES ROW(1, '["php", "web"]'), ROW(2, '["mysql", "sql"]'),

ROW(3, '["mysql", "indexing"]'), ROW(4, '["php", "laravel", "web"]'),

ROW(6, '["linux", "server"]')) AS t (id, tags) ON t.id = p.id

SET p.attributes = JSON_SET(p.attributes, '$.tags', CAST(t.tags AS JSON));

UPDATE products SET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'best-seller')

WHERE id = 4;

SELECT attributes FROM products WHERE id = 4;

Listing 3.78: Tagging the books with arrays

+-----------------------------------------------------------------------------------------+

| attributes |

+-----------------------------------------------------------------------------------------+

| {"tags": ["php", "laravel", "web", "best-seller"], "pages": 520, "format": "hardcover"} |

+-----------------------------------------------------------------------------------------+

Output of Listing 3.78 To turn a whole result set into one array, use the aggregates JSON_ARRAYAGG() and JSON_OBJECTAGG() from Section 3.8.2, and PHP then needs a single json_decode() (Chapter 4).

3.9.4 Searching, Merging, and Inspecting JSON

value MEMBER OF (array) tests one element, JSON_CONTAINS(target, candidate) requires every candidate element, JSON_OVERLAPS(a, b) requires any shared element, and JSON_CONTAINS_PATH(doc, 'one'|'all', path, ...) tests for keys. The first three are the predicates a multi-valued index can serve (Section 3.9.6). Mind the NULLs: a product without tags is not "not tagged php" but unknown.

SELECT sku, 'php' MEMBER OF (attributes->'$.tags') AS php,

JSON_CONTAINS(attributes->'$.tags', '["php", "web"]') AS both_,

JSON_OVERLAPS(attributes->'$.tags', '["sql", "web"]') AS either,

JSON_CONTAINS_PATH(attributes, 'one', '$.color', '$.count') AS merch,

JSON_LENGTH(attributes, '$.tags') AS n_tags, JSON_KEYS(attributes) AS `keys`

FROM products WHERE id IN (4, 5, 7);

Listing 3.79: Array predicates and inspection functions

+-----------+------+-------+--------+-------+--------+-----------------------------+

| sku | php | both_ | either | merch | n_tags | keys |

+-----------+------+-------+--------+-------+--------+-----------------------------+

| BK-LAR-01 | 1 | 1 | 1 | 0 | 4 | ["tags", "pages", "format"] |

| BK-UX-01 | NULL | NULL | NULL | 0 | NULL | ["pages", "format"] |

| AC-MUG-01 | NULL | NULL | NULL | 1 | NULL | ["color", "capacity_ml"] |

+-----------+------+-------+--------+-------+--------+-----------------------------+

Output of Listing 3.79 JSON_SEARCH(attributes, 'one', 'lin%') finds where a string sits: "$.tags[0]" on the Linux book. JSON_MERGE_PATCH() follows RFC 7396 (HTTP PATCH): later values win, arrays are replaced, and null deletes a key, so merging {"pages": 188, "tags": ["mysql"], "format": "ebook"} with {"pages": 196, "tags": ["sql"], "format": null} gave {"tags": ["sql"], "pages": 196}. JSON_MERGE_PRESERVE() keeps both sides, giving "pages": [188, 196]. The old JSON_MERGE() raises deprecation warning 1287.

3.9.5 Flattening Documents with JSON_TABLE

JSON_TABLE(doc, path COLUMNS (...)) is a table function: it turns the nodes matched by path into rows with typed columns, so joins, GROUP BY and window functions work on JSON. Because it may refer to p.attributes, it is implicitly LATERAL (Section 3.6.5).

SELECT jt.tag, COUNT(*) AS n, GROUP_CONCAT(p.sku ORDER BY p.sku) AS skus

FROM products AS p,

JSON_TABLE(p.attributes, '$.tags[*]' COLUMNS (tag VARCHAR(20) PATH '$')) AS jt

GROUP BY jt.tag HAVING n > 1;

Listing 3.80: One row per tag, then an ordinary GROUP BY

+-------+---+---------------------+

| tag | n | skus |

+-------+---+---------------------+

| mysql | 2 | BK-SQL-01,BK-SQL-02 |

| php | 2 | BK-LAR-01,BK-PHP-01 |

| web | 2 | BK-LAR-01,BK-PHP-01 |

+-------+---+---------------------+

Output of Listing 3.80 The comma join acts as an inner join: an empty path yields no rows, so only the 5 tagged products took part; LEFT JOIN JSON_TABLE(...) AS jt ON TRUE kept all 8. Columns can also be FOR ORDINALITY counters, EXISTS PATH flags or NESTED PATH expansions, with fallbacks: on the mug, format VARCHAR(10) PATH '$.format' DEFAULT '"merch"' ON EMPTY gave merch (the default is JSON text), and color TINYINT PATH '$.color' DEFAULT '-1' ON ERROR gave -1, as "black" will not cast.

3.9.6 Indexing JSON with Generated and Multi-Valued Indexes

A JSON column cannot be indexed directly. For a scalar path, index a generated column or a functional index (Section 3.4.8). For an array, MySQL 8.0.17 added the multi-valued index, CAST(expr AS type ARRAY), with one entry per element, serving MEMBER OF(), JSON_CONTAINS() and JSON_OVERLAPS(). Before it existed, the MEMBER OF query below was a table scan.

ALTER TABLE products

ADD INDEX idx_tags ((CAST(attributes->'$.tags' AS CHAR(20) ARRAY))),

ADD COLUMN pages SMALLINT UNSIGNED AS (attributes->>'$.pages') VIRTUAL,

ADD INDEX idx_pages (pages);

EXPLAIN FORMAT=JSON INTO @m

SELECT sku FROM products WHERE 'php' MEMBER OF (attributes->'$.tags');

EXPLAIN FORMAT=JSON INTO @o

SELECT sku FROM products WHERE JSON_OVERLAPS(attributes->'$.tags', '["sql", "linux"]');

EXPLAIN FORMAT=JSON INTO @p SELECT sku FROM products WHERE attributes->>'$.pages' > 400;

SELECT JSON_EXTRACT(@m, '$**.index_access_type', '$**.index_name') AS member_of,

JSON_EXTRACT(@o, '$**.index_access_type', '$**.index_name') AS overlaps,

JSON_EXTRACT(@p, '$**.index_access_type', '$**.index_name') AS pages_expr\G

Listing 3.81: A multi-valued index on tags and a virtual column on pages

*************************** 1. row ***************************

member_of: ["index_lookup", "idx_tags"]

overlaps: ["index_range_scan", "idx_tags"]

pages_expr: ["index_range_scan", "idx_pages"]

Output of Listing 3.81 The last query never named pages, but its expression matched the column's definition, so the optimizer substituted idx_pages. A multi-valued index allows one array key part, cannot be primary or covering, and rejects a JSON null element (error 3903). JSON comparison is case-sensitive, so 'PHP' MEMBER OF (...) matched 0 rows against 2 for 'php'.

3.9.7 Full-Text Indexes and MATCH ... AGAINST

LIKE '%chapter%' cannot use a B-tree and knows nothing about words. A FULLTEXT index is an inverted index: the parser splits text into tokens, and InnoDB records the rows and positions of each. MATCH (cols) AGAINST ('words') searches in natural language mode by default and scores rows higher for words rare in the collection; in WHERE without ORDER BY, rows come back best first, so AGAINST ('testing chapter') returned review 6 (score 1.01) before review 2 (0.30), which lacks "testing". The MATCH column list must equal an index's list exactly.

ALTER TABLE reviews ADD FULLTEXT INDEX ft_body (body);

ALTER TABLE products ADD FULLTEXT INDEX ft_title (title);

SELECT SUM(MATCH (body) AGAINST ('index') > 0) AS `index`,

SUM(MATCH (body) AGAINST ('indexes') > 0) AS indexes,

SUM(MATCH (body) AGAINST ('B-trees') > 0) AS `b-trees`,

SUM(MATCH (body) AGAINST ('the') > 0) AS the FROM reviews;

Listing 3.82: What the default tokenizer really indexes

+-------+---------+---------+------+

| index | indexes | b-trees | the |

+-------+---------+---------+------+

| 0 | 1 | 1 | 0 |

+-------+---------+---------+------+

Output of Listing 3.82 Each zero is a parser rule: no stemming (index misses "indexes"), hyphens split words (B-trees matched via "trees"), and "the" is one of 36 default stopwords. Tokens shorter than innodb_ft_min_token_size (3) are never indexed, so sql matched a title and UX none; MyISAM's ft_min_word_len is 4. The first FULLTEXT index adds a hidden FTS_DOC_ID column and rebuilds the table (warning 124); these two indexes created 22 auxiliary fts_ tables.

3.9.8 Boolean Mode, Relevance, and Stopwords

IN BOOLEAN MODE adds operators: + requires a word, - excludes it, * matches a prefix, "..." a phrase, > and < change a word's weight, ~ makes it count against a row, "a b" @8 requires the words within eight of each other, and parentheses group. Results are unsorted unless you ORDER BY the MATCH expression.

SELECT GROUP_CONCAT(IF(MATCH (body) AGAINST ('+chapter -testing' IN BOOLEAN MODE), id, NULL))

AS `+chapter -testing`,

GROUP_CONCAT(IF(MATCH (body) AGAINST ('index*' IN BOOLEAN MODE), id, NULL)) AS `index*`,

GROUP_CONCAT(IF(MATCH (body) AGAINST ('"window functions"' IN BOOLEAN MODE), id, NULL))

AS `"window functions"`,

GROUP_CONCAT(IF(MATCH (body) AGAINST ('+the +chapter' IN BOOLEAN MODE), id, NULL))

AS `+the +chapter`

FROM reviews\G

Listing 3.83: Boolean operators on the review bodies

*************************** 1. row ***************************

+chapter -testing: 2

index*: 3

"window functions": 2

+the +chapter: NULL

Output of Listing 3.83 index* fixes the stemming miss, but a required stopword (+the) can never match. >testing lifted review 6 from 1.01 to 2.01 and ~testing cut it to 0.0102. InnoDB ranks a word as TF x IDF x IDF, with IDF = log10(rows / matching rows), so tiny tables distort scores: on three rows that all say "MySQL", all three came back scoring 0.0000000019. MyISAM, by contrast, drops a word found in half the rows.For your own stopwords, fill a one-column value VARCHAR(30) table, SET SESSION innodb_ft_user_stopword_table = 'shop/shop_stopwords', and recreate the index with separate DROP INDEX and CREATE FULLTEXT INDEX statements (a combined ALTER TABLE kept the old list). Listing chapter made it match 0 reviews and the 4: a custom list replaces the default. For Chinese titles such as 数据库索引入门, the default parser sees one token and AGAINST ('数据库') found nothing; WITH PARSER ngram indexes every 2-character run (ngram_token_size) and found both titles containing it.

3.9.9 Spatial Types, SRIDs, and Format Conversion

A geometry carries an SRID naming its spatial reference system: SRID 0 is a flat, unitless plane, and SRID 4326 is WGS 84, the GPS system, in degrees on an ellipsoid (MySQL knows 5,238 systems). A column declared SRID 4326 rejects other SRIDs (error 3643 for an SRID-0 point), and only such a column lets the optimizer use its SPATIAL (R-tree) index, which also needs NOT NULL.

CREATE TABLE stores (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

city VARCHAR(40) NOT NULL, loc POINT NOT NULL SRID 4326, SPATIAL INDEX sp_loc (loc));

INSERT INTO stores (city, loc) VALUES

('Kota Kinabalu', ST_GeomFromText('POINT(5.9804 116.0735)', 4326)),

('Kuala Lumpur', ST_GeomFromText('POINT(3.1390 101.6869)', 4326)),

('Singapore', ST_GeomFromText('POINT(1.3521 103.8198)', 4326)),

('Tokyo', ST_SRID(POINT(139.6503, 35.6762), 4326)),

('Sao Paulo', ST_GeomFromText('POINT(-46.6333 -23.5505)', 4326, 'axis-order=long-lat'));

INSERT INTO stores (city, loc) VALUES ('Oops', ST_GeomFromText('POINT(116.07 5.98)', 4326));

SELECT city, ST_AsText(loc) AS wkt, ST_Longitude(loc) AS lon FROM stores;

Listing 3.84: A stores table in WGS 84, and the axis-order trap

ERROR 3617 (22S03): Latitude 116.070000 is out of range in function st_geomfromtext. It must

be within [-90.000000, 90.000000].

+---------------+--------------------------+----------+

| city | wkt | lon |

+---------------+--------------------------+----------+

| Kota Kinabalu | POINT(5.9804 116.0735) | 116.0735 |

| Kuala Lumpur | POINT(3.139 101.6869) | 101.6869 |

| Singapore | POINT(1.3521 103.8198) | 103.8198 |

| Tokyo | POINT(35.6762 139.6503) | 139.6503 |

| Sao Paulo | POINT(-23.5505 -46.6333) | -46.6333 |

+---------------+--------------------------+----------+

Output of Listing 3.84 EPSG defines 4326 as latitude first, so MySQL's WKT is POINT(lat lon), while GeoJSON and many map libraries put longitude first. Three inputs agree above: lat-long WKT, WKT with axis-order=long-lat, and ST_SRID(POINT(x, y), 4326), where POINT() takes longitude as x. A swap fails loudly only when the would-be latitude exceeds 90 (the Oops row); Sao Paulo's text without the option was accepted as a point in the South Atlantic. HEX(loc) shows the storage format, a 4-byte SRID (E6100000) and then WKB; ST_AsWKB(), ST_SwapXY() and ST_Transform(loc, 3857) (Web Mercator) convert it.

3.9.10 Spatial Relations, Distance, and GeoJSON

ST_Contains(), ST_Within(), ST_Intersects(), ST_Disjoint(), ST_Touches(), ST_Overlaps(), ST_Crosses() and ST_Equals() test exact shapes and return 1 or 0; the MBR... variants test only bounding rectangles. ST_Distance_Sphere() returns meters on a sphere; ST_Distance() on a geographic SRID measures on the ellipsoid. Distance cannot use the R-tree, so filter first with a shape the index can check: a buffer around the customer.

SET @me = ST_SRID(POINT(101.6067, 3.1073), 4326);

SELECT city, ROUND(ST_Distance_Sphere(loc, @me) / 1000, 1) AS sphere_km,

ROUND(ST_Distance(loc, @me, 'kilometre'), 1) AS ellipsoid_km

FROM stores WHERE ST_Within(loc, ST_Buffer(@me, 2000000)) ORDER BY sphere_km;

Listing 3.85: Nearest stores to a customer in Petaling Jaya, within 2,000 km

+---------------+-----------+--------------+

| city | sphere_km | ellipsoid_km |

+---------------+-----------+--------------+

| Kuala Lumpur | 9.6 | 9.6 |

| Singapore | 313.9 | 313.5 |

| Kota Kinabalu | 1634.9 | 1636.3 |

+---------------+-----------+--------------+

Output of Listing 3.85 EXPLAIN FORMAT=JSON showed ["sp_loc", "index_range_scan"] for the filter. The buffer is a 32-segment polygon, so add ST_Distance_Sphere(loc, @me) <= r when the radius must be exact. The cheaper sphere was 1.4 km off over 1,635 km. On SRID 0, ST_Distance() is plain Euclidean geometry.GeoJSON, the format browser maps consume, is always longitude first: ST_AsGeoJSON(loc) for Kota Kinabalu gave {"type": "Point", "coordinates": [116.0735, 5.9804]}, and ST_GeomFromGeoJSON() reads such text back as SRID 4326. Nest ST_AsGeoJSON(loc) in JSON_OBJECT('type', 'Feature', 'geometry', ...), aggregate with JSON_ARRAYAGG() (Section 3.8.2), and one query returns a whole FeatureCollection that PHP can hand to Leaflet or OpenLayers.