Query semi-structured JSON data - AnalyticDB for MySQL - Alibaba Cloud Documentation Center

AnalyticDB for MySQL provides JSON indexes as a semi-structured data query feature to simplify semi-structured data processing and improve data query efficiency. This topic describes how to create a JSON index.

Overview

AnalyticDB for MySQL supports JSON indexes. You can create a JSON index for a column of the JSON type. This way, when you query data, you do not need to scan the entire table or parse the entire JSON document. This improves data query efficiency. JSON indexes are suitable for the scenarios where complex semi-structured data, such as log information, configuration files, and device information, is stored and queried.

Usage notes

You can create JSON indexes only for columns of the JSON type.
Each JSON index involves only one JSON column. To involve multiple columns in JSON indexes, you can create multiple JSON indexes.

Create a JSON index

When you create a JSON index, take note of the following items:

For AnalyticDB for MySQL clusters of V3.1.5.10 or later, no JSON index is automatically created after you create a table. You must manually create JSON indexes.
For AnalyticDB for MySQL clusters earlier than V3.1.5.10, JSON indexes are automatically created for JSON columns after you create a table.

Note

For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.

Create a JSON index when you create a table

Usage notes

If you specify one or more columns to create an index when you create a table, AnalyticDB for MySQL does not automatically create indexes for other columns in the table.

Syntax

CREATE TABLE table_name(
  column_name column_type,
  index index_name(column_name|column_name->'$.json_path')
)
DISTRIBUTED BY HASH(column_name);

Parameters

Parameter	Description
index_name	The name of the JSON index. Important The index name must be unique.
column_name\|column_name->'$.json_path'	column_name: the name of the column for which you want to create the JSON index. column_name->'$.json_path': the JSON column and its property key. Each JSON index involves only one property key of a JSON column. Important Only AnalyticDB for MySQL clusters of V3.1.6.8 or later support the `column_name->'$.json_path` parameter. If a JSON column already has an index, you must delete the index for the JSON column before you can create an index for a property key of the JSON column.

For information about other parameters of the table creation statement, see CREATE TABLE.

Examples

Create a JSON index for the vj column of the JSON type in the json_test table.

CREATE TABLE json_test(
  id int,
  vj json,
  index idx_vj(vj)
)
DISTRIBUTED BY HASH(id);

Create a JSON index for the name property key of the vj JSON column in the json_test table.

CREATE TABLE json_test(
  id int,
  vj json COMMENT 
  index idx_vj_path(vj->'$.name')
)
DISTRIBUTED BY HASH(id);

Create a JSON index for an existing table

Syntax

ALTER TABLE db_name.table_name ADD KEY index_name(column_name|column_name->'$.json_path');

Parameters

Parameter	Description
db_name	The name of the database.
table_name	The name of the table.
index_name	The name of the JSON index. Important The index name must be unique.
column_name\|column_name->'$.json_path'	column_name: the name of the column for which you want to create the JSON index. column_name->'$.json_path': the JSON column and its property key. Each JSON index involves only one property key of a JSON column. Important Only AnalyticDB for MySQL clusters of V3.1.6.8 or later support the `column_name->'$.json_path` parameter. If a JSON column already has an index, you must delete the index for the JSON column before you can create an index for a property key of the JSON column.

Examples

Create a JSON index for the vj column of the JSON type in the json_test table.
```
ALTER TABLE json_test ADD KEY index_vj(vj);
```
Create a JSON index for the name property key of the vj JSON column in the json_test table.
```
ALTER TABLE json_test ADD KEY index_vj_key(vj->'$.name');
```

References

For information about how to query JSON data, see JSON functions.