Queries on a JSON column that must parse the entire JSON object can be slow. To accelerate these queries, the In-Memory Column Index (IMCI) provides a JSON index feature. This feature uses a JSON tokenizer to break down JSON data and build an inverted index. This process avoids parsing the full JSON object, significantly improving the performance of expressions such as json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys, and json_depth.
The JSON index for a columnstore index is currently in beta. If you want to use this feature, submit a ticket to request access.
Create a JSON index
A JSON index uses a JSON tokenizer to break down a JSON object into array items or key-value pairs and adds them to an inverted index. During a query, the system uses the index to find matching rows directly, avoiding the need to load and parse the entire JSON object. You can create a JSON index only on columns of the JSON data type. Three types of JSON indexes are available:
JSON array index (
mode=0ormode=1): For JSON columns that contain arrays. It supports only arrays with scalar elements (such as integers, strings, or time values), not object arrays or nested arrays. We recommend that you usemode=1. This index type supportsjson_overlapsandjson_contains.JSON key-value index (
mode=2): For JSON columns that contain objects where the values in key-value pairs are scalars (such as integers, strings, or time values) or scalar arrays. Use thejson_pathsparameter to specify the keys or arrays to index, such as{$.k1}or{$[*].k1,$[*].k2}. This index type supportsjson_extract,json_overlaps, andjson_contains.JSON hybrid index (
mode=3): For JSON columns of any type, where the values in key-value pairs are scalars, objects, scalar arrays, or object arrays. Use thejson_pathsparameter to specify the keys or arrays to index. You can append@[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value]to a path to declare which expressions that path supports. The default is@[extract|contains|overlaps], which supports scalar and scalar array expressions such asjson_overlaps,json_contains, andjson_extract.@[length]supportsjson_length.@[contains|overlaps|structure_path]supportsjson_overlapsandjson_containson object arrays.@[extract|structure_hash]or@[extract|structure_value]supportsjson_extracton objects.structure_hashstores the hash value of the object in the index, which lets you filter at the lowest cost.structure_valuestores the object value in the index, which gives precise filtering at a higher storage cost. This index type supportsjson_overlaps,json_contains,json_extract,json_unquote,json_type,json_length,json_keys, andjson_depth.
Creating a JSON index builds an inverted index, and its usage is similar to that of a full-text index. For more information, see IMCI full-text index.
Procedure
Enable the JSON index feature: To use a JSON index, you must first enable the global parameter
imci_enable_fts_json.Parameter
Level
Description
imci_enable_fts_jsonGlobal
Controls whether to enable the JSON index feature.
ON (default): Enables the feature.
OFF: Disables the feature.
Syntax:
CREATE TABLE table_name ( column_name JSON COMMENT "imci_fts(type=4 mode=MODE json_paths={$.PATH1,$.PATH2,...})" ) COMMENT 'columnar=1';Parameters:
type=4: Specifies the use of the JSON tokenizer.mode: Specifies the JSON index type. Set to0or1for a JSON array index,2for a JSON key-value index, or3for a JSON hybrid index.json_paths: Specifies the keys or arrays to index. This parameter applies whenmode=2or whenmode=3. For example, for the JSON object'{"k1":"v1","k2":2}', you can specifyjson_paths={$.k1},json_paths={$.k2}, orjson_paths={$.k1,$.k2}. Whenmode=3, you can append@[...]to each path to declare which expressions that path supports, using the values described for the JSON hybrid index.
JSON array index
A JSON array index accelerates queries on JSON columns that contain arrays. It supports the json_overlaps and json_contains expressions.
Syntax
ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=1)';Accelerate json_overlaps queries
Parameter | Level | Description |
| Global/Session | Controls whether to enable JSON index acceleration for
|
When this parameter is enabled, json_overlaps expressions are accelerated by using an FtsTableScan. The JSON tokenizer breaks down the target values, searches for each one in the inverted index, and the query returns the union of the results. This means a row is matched if any of the target elements are found.
mysql> EXPLAIN SELECT * FROM t1 WHERE json_overlaps(title, '[300, "301"]');
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 1, max_query_mem = 1073741824) |
| 2 | └─Compute Scalar | | |
| 3 | └─FtsTableScan | t1 | Term: ("300(json_type(2))", "301(json_type(5))") Fallback: (JSON_OVERLAPS(t1.title, "[300, "301"](json)") <> 0) |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------------+Accelerate json_contains queries
Parameter | Level | Description |
| Global/Session | Controls whether to enable JSON index acceleration for
|
When this parameter is enabled, json_contains expressions are accelerated by using an FtsTableScan. The JSON tokenizer breaks down the target values, searches for each one in the inverted index, and the query returns the intersection of the results. This means a row is matched only if all of the target elements are found.
mysql> EXPLAIN SELECT * FROM t1 WHERE json_contains(title, '[300, "301"]');
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 1, max_query_mem = 1073741824) |
| 2 | └─Compute Scalar | | |
| 3 | └─FtsTableScan | t1 | Term: ("300(json_type(2))", "301(json_type(5))") Fallback: (JSON_CONTAINS(t1.title, "[300, "301"]") <> 0) |
+----+----------------------+------+-----------------------------------------------------------------------------------------------------------+JSON key-value index
A JSON key-value index accelerates equality queries on specific keys within JSON objects. It supports the json_extract expression. When you create the index, you use json_paths to specify which keys to index. During a query, the system can use the inverted index to quickly locate matching rows based on the value of the key-value pair.
Syntax
ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=2 json_paths={$.k1,$.k2})';Accelerate json_extract queries
Parameter | Level | Description |
| Global/Session | Controls whether to enable JSON index acceleration for
|
When this parameter is enabled, equality expressions using json_extract are accelerated by an FtsTableScan. The following example shows a query that finds rows where the value of $.k1 is '1':
mysql> EXPLAIN SELECT * FROM t1 WHERE json_extract(title, "$.k1") = '1';
+----+----------------------+------+------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+----------------------+------+------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 1, max_query_mem = 1073741824) |
| 2 | └─Compute Scalar | | |
| 3 | └─FtsTableScan | t1 | Term: ("1(json_index(0),json_type(5))") Fallback: (JSON_EXTRACT(t1.title, "$.k1") = "1(json)") |
+----+----------------------+------+------------------------------------------------------------------------------------------------+You can accelerate multiple key-value conditions in the same query:
mysql> EXPLAIN SELECT * FROM t1 WHERE json_extract(title, "$.k1") = '1' AND json_extract(title, "$.k2") = 2;
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 1, max_query_mem = 1073741824) |
| 2 | └─Compute Scalar | | |
| 3 | └─FtsTableScan | t1 | Term: ("1(json_index(0),json_type(5))") Term: ("2(json_index(1),json_type(2))") Fallback: ((JSON_EXTRACT(t1.title, "$.k1") = "1(json)") AND (JSON_EXTRACT(t1.title, "$.k2") = "2(json)")) |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+JSON nested index
A JSON nested index accelerates array queries on specific keys in JSON columns that contain object arrays. It supports nested queries that use expressions such as json_overlaps, json_contains, and json_extract. When you create the index, use json_paths to specify the keys in the object arrays. During a query, the system locates matching rows in the inverted index based on the values of the key-value pairs.
Syntax
ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=2 json_paths={$[*].k1,$[*].k2})';
ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=3 json_paths={$[*].k1@[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value] ,$[*].k2@[contains|overlaps]})';Accelerate JSON nested queries
Nested queries that use json_overlaps, json_contains, and json_extract can be accelerated by an FtsTableScan. For example, assume that a JSON column contains '[{"id": 1, "name": "Zhang San"}, {"id": 2, "name": "Li Si"}, {"id": 3, "name": "Chen Yi"}]'. You can specify json_paths={$[*].id, $[*].name} to parse the key-value pairs in the object array and add them to the index to accelerate queries.
mysql> EXPLAIN SELECT * FROM t1 WHERE json_overlaps(title->'$[*].id', '[1, 2]');
+----+----------------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+----------------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 32, max_query_mem = 137438953472) |
| 2 | └─Compute Scalar | | |
| 3 | └─FtsTableScan | t1 | Term: (OR(1(json_index(0),json_type(2)), 2(json_index(0),json_type(2)))) Fallback: (JSON_OVERLAPS(JSON_EXTRACT(t1.title, "$[*].id"), "[1, 2](json)") <> 0) |
+----+----------------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+mysql> EXPLAIN SELECT * FROM t1 WHERE json_contains(title->'$[*].name', '["Zhang San", "Chen Yi"]');
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 32, max_query_mem = 137438953472) |
| 2 | └─Compute Scalar | | |
| 3 | └─FtsTableScan | t1 | Term: (AND(Zhang San(json_index(1),json_type(5)), Chen Yi(json_index(1),json_type(5)))) Fallback: (JSON_CONTAINS(JSON_EXTRACT(t1.title, "$[*].name"), "["Zhang San", "Chen Yi"](json)") <> 0) |
+----+----------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+JSON hybrid index
A JSON hybrid index accelerates queries on specific keys in JSON columns of any type, including scalar values, objects, scalar arrays, and object arrays. It supports json_overlaps, json_contains, json_extract, json_unquote, json_type, json_length, json_keys, and json_depth.
Syntax
ALTER TABLE table_name MODIFY COLUMN column_name JSON DEFAULT NULL COMMENT 'imci_fts(type=4 mode=3 json_paths={$[*].k1@[extract|contains|overlaps|unquote|type|length|keys|depth|structure_path|structure_hash|structure_value] ,$[*].k2@[contains|overlaps]})';Accelerate various JSON queries
Parameter | Level | Description |
| Global/Session | Controls whether to enable JSON index acceleration for
|
| Global/Session | Controls whether to enable JSON index acceleration for
|
| Global/Session | Controls whether to enable JSON index acceleration for
|
| Global/Session | Controls whether to enable JSON index acceleration for
|
| Global/Session | Controls whether to enable JSON index acceleration for
|
| Global/Session | Controls whether the optimizer uses the hash value of the object or the object value when both |
After you enable imci_convert_json_unquote_to_match, json_unquote expressions are accelerated by an FtsTableScan:
mysql> explain select id, title from t1 where json_unquote(title->'$.id') = '21';
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
| 2 | └─Compute Scalar | | |
| 3 | └─FILTER | | Cond: ((TRUE PRED) AND (JSON_UNQUOTE(JSON_EXTRACT(t1.title, "$.id")) = "21")) |
| 4 | └─FtsTableScan | t1 | Term: (AND("21"(json:index(0),type(STRING),src(UnquotedValue)))) Fallback: (JSON_UNQUOTE(JSON_EXTRACT(t1.title, "$.id")) = "21") |
+----+------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+After you enable imci_convert_json_length_to_match, json_length expressions are accelerated by an FtsTableScan:
mysql> explain select id, json_length(title), title from t1 where json_length(title) = 1;
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
| 2 | └─Compute Scalar | | |
| 3 | └─FtsTableScan | t1 | Term: (AND(1(json:index(0),type(UNSIGNED_INTEGER),src(LengthValue)))) Fallback: (JSON_LENGTH(t1.title) = 1) |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------+When a path enables both structure_hash and structure_value, set imci_fts_json_structure_preference to "VALUE" to have the optimizer use the object value for an object equality query:
mysql> set imci_fts_json_structure_preference = "VALUE";
mysql> explain select id, title from t1 where json_extract(title, '$.info') = cast('{"id": 10, "name": "name_10"}' as json);
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | Extra Info |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
| 2 | └─Compute Scalar | | |
| 3 | └─FtsTableScan | t1 | Term: (AND(json_value({"id":10,"name":"name_10"})(json:index(0),type(OBJECT),src(JsonValue)))) Fallback: (JSON_EXTRACT(t1.title, "$.info") = "{"id": 10, "name": "name_10"}(json)") |
+----+----------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+