Query semi-structured JSON data - AnalyticDB - 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 and a JSON array index.

Overview

AnalyticDB for MySQL supports JSON indexes and JSON array indexes. You can create a JSON index or a JSON array 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 and JSON array indexes only for columns of the JSON type.
Each JSON index or JSON array index involves only one JSON column. To involve multiple columns in JSON indexes or JSON array indexes, you can create multiple JSON indexes or JSON array 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 view the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a 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|KEY} [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. For information about how to view the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a cluster? To update the minor version of a cluster, contact technical support. 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 {INDEX|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. For information about how to view the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a cluster? To update the minor version of a cluster, contact technical support. 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');
```

Create a JSON array index

Only AnalyticDB for MySQL clusters of V 3.1.10.6 or later support JSON array indexes. After you create JSON array indexes, you can use the JSON_CONTAINS and JSON_OVERLAPS functions to query data and improve query efficiency.

Usage notes

AnalyticDB for MySQL can create indexes for only numeric and string elements in a JSON array but not other types of elements (such as nested arrays or objects) in the array.

Create a JSON array index when you create a table

Syntax

CREATE TABLE table_name(
  column_name column_type,
  {INDEX|KEY} [index_name](column_name->'$[*]')
)
DISTRIBUTED BY HASH(column_name);

Parameters

Parameter	Description
index_name	The name of the JSON array index. Important The index name must be unique.
column_name->'$[*]'	`column_name` specifies the name of the column for which you want to create the JSON array index. For example, `vj->'$[*]'` specifies to create a JSON array index for the vj column.

Example

Create a JSON array 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_array(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);

Create a JSON array index for an existing table

Usage notes

The created JSON array index takes effect only after the BUILD job is complete. For information about automatic BUILD jobs, manual BUILD jobs, and the status of BUILD jobs, see BUILD.

Syntax

ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name->'$[*]')

Parameters

Parameter	Description
db_name	The name of the database.
table_name	The name of the table.
index_name	The name of the JSON array index. Important The index name must be unique.
column_name->'$[*]'	`column_name` specifies the name of the column for which you want to create the JSON array index. For example, `vj->'$[*]'` specifies to create a JSON array index for the vj column.

Example

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

ALTER TABLE json_test ADD KEY index_vj(vj->'$[*]');

Delete an index

Syntax

ALTER TABLE db_name.table_name DROP KEY index_name

Parameter description

index_name: the name of the index to be deleted. You can execute the SHOW INDEX FROM db_name.table_name; statement to query the value of index_name.

Example

Delete the index of the age column in the customer table.

ALTER TABLE adb_demo.customer DROP KEY age_idx;

References

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