JSON, Full-Text Search & Generated Columns

JSON columns

CREATE TABLE products (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100),
  attrs JSON
);

INSERT INTO products (name, attrs) VALUES
('Laptop', '{"brand":"Dell","ram_gb":16,"tags":["work","portable"]}');

Reading JSON

SELECT attrs->'$.brand'   AS quoted;    -- "Dell"
SELECT attrs->>'$.brand'  AS unquoted;  -- Dell
SELECT JSON_EXTRACT(attrs, '$.ram_gb');
SELECT JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.tags[0]'));

SELECT * FROM products WHERE attrs->>'$.brand' = 'Dell';
SELECT * FROM products WHERE JSON_CONTAINS(attrs->'$.tags', '"work"');
SELECT * FROM products WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.warranty');

Modifying JSON

UPDATE products SET attrs = JSON_SET(attrs, '$.ram_gb', 32) WHERE id = 1;
UPDATE products SET attrs = JSON_INSERT(attrs, '$.color', 'black') WHERE id = 1;
UPDATE products SET attrs = JSON_REMOVE(attrs, '$.color') WHERE id = 1;
UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.tags', 'sale') WHERE id = 1;

SELECT JSON_KEYS(attrs), JSON_LENGTH(attrs), JSON_TYPE(attrs->'$.tags');
SELECT JSON_PRETTY(attrs) FROM products;

JSON_SET updates or inserts, JSON_INSERT only adds new keys, JSON_REPLACE only changes existing ones.

Expanding JSON arrays into rows

SELECT p.name, t.tag
FROM products p,
JSON_TABLE(p.attrs, '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')) AS t;

Generated columns and indexing JSON

JSON paths cannot be indexed directly, so extract the value into a generated column and index that.

ALTER TABLE products
  ADD COLUMN brand VARCHAR(50)
    AS (attrs->>'$.brand') STORED,
  ADD INDEX idx_brand (brand);

SELECT * FROM products WHERE brand = 'Dell';   -- now uses the index

• VIRTUAL (default) — computed on read, no storage.
• STORED — computed on write, uses disk, can be indexed in more situations.

MySQL 8.0.17+ also supports multi-valued indexes over JSON arrays:

ALTER TABLE products ADD INDEX idx_tags ((CAST(attrs->'$.tags' AS CHAR(50) ARRAY)));

Use JSON for genuinely variable attributes. Anything you filter, join or sort on frequently belongs in a real column — a relational database is still better at relations than a document store bolted inside one.

Full-text search

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body  TEXT,
  FULLTEXT KEY ft_article (title, body)
) ENGINE=InnoDB;

-- natural language mode (default)
SELECT id, title, MATCH(title, body) AGAINST ('database indexing') AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('database indexing')
ORDER BY score DESC;

-- boolean mode: +required -excluded "exact phrase" wildcard*
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('+mysql -oracle "query optimization"' IN BOOLEAN MODE);

Notes: the default minimum word length for InnoDB is 3 characters (innodb_ft_min_token_size), common stopwords are ignored, and natural-language mode drops words present in more than 50% of rows. For Bengali or other non-space-delimited needs, look at the ngram parser:

FULLTEXT KEY ft_bn (title_bn) WITH PARSER ngram

For large-scale search with typo tolerance and faceting, a dedicated engine such as Elasticsearch or Meilisearch is a better fit than MySQL full-text.