How to create index on json column in MySQL?

Question

How to create index on sub-documents in Json data type in MySQL server?

I know we have to create a generated column from the base table and then need to index that column Virtually or stored.

But I want syntax for creating a generated column for sub-document.

Answer 1

For indexing values stored in JSON, use a stored generated column.

In example, for indexing title and category of

{"title": "Some Title", "category": "Some Category", "url": "...", ...}

use something like:

CREATE TABLE listings (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) AS
    (data->>'$.title') STORED,
  category VARCHAR(255) AS
    (data->>'$.category') STORED,
  data JSON NOT NULL,
  KEY (title),           -- index title
  KEY (category),        -- index category
  KEY (title, category)  -- composite index of title & category
);

Read more about MySQL as smart JSON storage :-)