CODEWITHSUNDEEP
jsondatabaseindexingmysql-5.7
Aman Aggarwal
Aman Aggarwal

Reputation: 18459

How to create index on json column in MySQL?

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.

Upvotes: 21

Views: 41154

Answers (3)

Tom Raganowicz
Tom Raganowicz

Reputation: 2470

With MySQL 8.0.21 release it is possible to use this syntax:

CREATE TABLE inventory(
    items JSON,
    INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ),
    INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),
    INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) )
);

and query using:

SELECT items->"$.price" FROM inventory
WHERE JSON_VALUE(items, '$.name' RETURNING VARCHAR(50)) = "hat";

SELECT * FROM inventory
WHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= 100.01;

SELECT items->"$.name" AS item, items->"$.price" AS amount
FROM inventory
WHERE JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED) > 500;

source: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html (see JSON Notes section)

Upvotes: 23

Δ O
Δ O

Reputation: 3710

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

For example, to index 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 it in my article MySQL as smart JSON storage :-)

Upvotes: 23

Bipil Raut
Bipil Raut

Reputation: 262

JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column. See Section “Secondary Indexes and Generated Virtual Columns”, for a detailed example.

Upvotes: 10

Related Questions