MySQL JSON Index

Summary: in this tutorial, you will learn how to create an index for JSON data in MySQL using functional indexes.

Setting up a sample table

We’ll use the products table from the sample database as a data source for creating JSON data:

First, create a new table called product_info:

CREATE TABLE product_results(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);Code language: SQL (Structured Query Language) (sql)

The product_results table has two columns:

id: This is an auto-increment primary key column.
data: This column has the data type of JSON that stores the JSON data.

Next, create JSON data from the columns of the products table and insert it into the product_results table:

INSERT INTO product_results(data) 
SELECT 
  JSON_OBJECT(
    'buyPrice', buyPrice, 'MSRP', msrp, 
    'quantityInStock', quantityInStock, 
    'productName', productName
  ) 
FROM
  products;Code language: SQL (Structured Query Language) (sql)

The statement inserts 110 rows into the product_results table.

Then, query data from the product_results table:

SELECT * FROM product_results;Code language: SQL (Structured Query Language) (sql)

Output:

+-----+--------------------------------------------------------------------------------------------------------------------------+
| id  | data                                                                                                                     |
+-----+--------------------------------------------------------------------------------------------------------------------------+
|   1 | {"MSRP": 95.70, "buyPrice": 48.81, "productName": "1969 Harley Davidson Ultimate Chopper", "quantityInStock": 7933}      |
|   2 | {"MSRP": 214.30, "buyPrice": 98.58, "productName": "1952 Alpine Renault 1300", "quantityInStock": 7305}                  |
|   3 | {"MSRP": 118.94, "buyPrice": 68.99, "productName": "1996 Moto Guzzi 1100i", "quantityInStock": 6625}                     |
...Code language: SQL (Structured Query Language) (sql)

After that, find the product with the name 'P-51-D Mustang':

SELECT 
  data ->> '$.productName',
  data ->> '$.buyPrice'
FROM 
  product_results 
WHERE 
  data ->> '$.productName' = 'P-51-D Mustang';Code language: SQL (Structured Query Language) (sql)

It returns the following product:

+--------------------------+-----------------------+
| data ->> '$.productName' | data ->> '$.buyPrice' |
+--------------------------+-----------------------+
| P-51-D Mustang           | 49.00                 |
+--------------------------+-----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The query works as expected but it has to scan all the rows in the product_results table to find the product.

Finally, show how the query optimizer executes the query by using the EXPLAIN statement:

EXPLAIN SELECT 
  data ->> '$.productName',
  data ->> '$.buyPrice'
FROM 
  product_results 
WHERE 
  data ->> '$.productName' = 'P-51-D Mustang' \G;Code language: SQL (Structured Query Language) (sql)

Output:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product_results
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 110
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)Code language: SQL (Structured Query Language) (sql)

To speed up the query, you need to index the JSON in the data column. However, MySQL does not support indexing JSON data directly.

Fortunately, you can use functional indexes, which have been available since MySQL 8.0.13, to index JSON data.

Indexing JSON data using functional indexes

A functional index is an index based on an expression rather than a column. The following statement adds an index to the product_results table:

ALTER TABLE 
  product_results 
ADD 
  INDEX idx_product_name(
    (data ->> '$.productName')
  );Code language: SQL (Structured Query Language) (sql)

But it returns an error:

ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.Code language: SQL (Structured Query Language) (sql)

The reason is that the ->> operator returns a value of the TEXT type that is not indexable.

The error message also shows that we need to use the CAST operator to cast the value to an indexable type e.g., VARCHAR in this case:

ALTER TABLE 
  product_results 
ADD 
  INDEX idx_product_name (
    (
      CAST(
        data ->> '$.productName' AS CHAR(255)
      ) COLLATE utf8mb4_bin
    )
  );Code language: SQL (Structured Query Language) (sql)

The index is created successfully.

Note that casting using CHAR instructs MySQL to infer a VARCHAR type.

Now, you can query the products by the buy price from the JSON data:

SELECT 
  data ->> '$.productName',
  data ->> '$.buyPrice'
FROM 
  product_results 
WHERE 
  data ->> '$.productName' = 'P-51-D Mustang';Code language: SQL (Structured Query Language) (sql)

It returns the same result set as before. But this time, it uses the functional index idx_product_name:

EXPLAIN SELECT 
  data ->> '$.productName',
  data ->> '$.buyPrice'
FROM 
  product_results 
WHERE 
  data ->> '$.productName' = 'P-51-D Mustang' \G;Code language: SQL (Structured Query Language) (sql)

Output:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product_results
   partitions: NULL
         type: ref
possible_keys: idx_product_name
          key: idx_product_name
      key_len: 1023
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use functional indexes to index JSON data to improve the query speed.

Was this tutorial helpful?