AnalyticDB:JSON indexes

Version requirements

To view or update your cluster's minor version, go to the AnalyticDB for MySQL console, open the Cluster Information page, and find the Configuration Information section.

Choose the right index type

Each JSON index or JSON array index covers a single JSON column. To index multiple columns, create a separate index for each.

Usage notes

• JSON indexes and JSON array indexes can only be created on columns of the JSON type.

• JSON array indexes support only numeric and string elements. Nested arrays and objects cannot be indexed.

• For clusters running V3.1.5.10 or later, JSON indexes are not created automatically when you create a table. Create them manually using the syntax below.

• For clusters running versions earlier than V3.1.5.10, JSON indexes are created automatically for JSON columns after table creation.

Create a JSON index

All examples in this section use a devices table that stores device telemetry as JSON:

CREATE TABLE devices(
  id int,
  info json,
  ...
)
DISTRIBUTED BY HASH(id);

A typical info value looks like:

{"device_id": "d-001", "status": "online", "tags": ["prod", "us-west"]}

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

CREATE TABLE devices(
  id int,
  info json,
  index idx_info(info)
)
DISTRIBUTED BY HASH(id);

CREATE TABLE devices(
  id int,
  info json,
  index idx_info_status(info->'$.status')
)
DISTRIBUTED BY HASH(id);

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

ALTER TABLE devices ADD KEY idx_info(info);

ALTER TABLE devices ADD KEY idx_info_status(info->'$.status');

Create a JSON array index

JSON array indexes accelerate membership queries on JSON arrays using JSON_CONTAINS and JSON_OVERLAPS. This index type requires V3.1.10.6 or later.

The $[*] JSONPath expression matches all elements in a JSON array.

JSON array indexes support only numeric and string array elements. Nested arrays and objects cannot be indexed.

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

CREATE TABLE devices(
  id int,
  info json,
  index idx_info_tags(info->'$[*]')
)
DISTRIBUTED BY HASH(id);

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

ALTER TABLE devices ADD KEY idx_info_tags(info->'$[*]');

Delete an index

ALTER TABLE db_name.table_name DROP KEY index_name

SHOW INDEX FROM db_name.table_name;

ALTER TABLE mydb.devices DROP KEY idx_info;

ALTER TABLE mydb.devices DROP KEY idx_info_tags;

What's next

• JSON: JSON data type reference for AnalyticDB for MySQL.

• JSON functions: JSON functions supported by AnalyticDB for MySQL, including JSON_CONTAINS and JSON_OVERLAPS.