Query semi-structured JSON data - - Centro de documentación de Alibaba Cloud

Los índices JSON permiten consultar campos específicos dentro de columnas JSON sin necesidad de examinar toda la tabla ni analizar sintácticamente cada documento JSON. AnalyticDB for MySQL admite dos tipos de índice para columnas JSON: índices JSON para búsquedas de clave/valor e índices de matriz JSON para consultas de pertenencia a matrices mediante JSON_CONTAINS y JSON_OVERLAPS.

Requisitos de versión

Función	Versión mínima
Índice JSON (creación manual obligatoria)	V3.1.5.10
Índice JSON en una clave de propiedad (`column->'$.path'`)	V3.1.6.8
Índice de matriz JSON	V3.1.10.6

Para consultar o actualizar la versión menor de su clúster, vaya a AnalyticDB for MySQL console, abra la página Cluster Information y localice la sección Configuration Information.

Elección del tipo de índice adecuado

Tipo de índice	Recomendado para	Funciones compatibles
Índice JSON	Búsquedas exactas de clave/valor en propiedades de objetos	—
Índice de matriz JSON	Consultas de pertenencia en matrices JSON	`JSON_CONTAINS`, `JSON_OVERLAPS`

Cada índice JSON o índice de matriz JSON abarca una sola columna JSON. Para indexar varias columnas, cree un índice independiente para cada una.

Notas de uso

Los índices JSON y los índices de matriz JSON solo se pueden crear en columnas de tipo JSON.
Los índices de matriz JSON solo admiten elementos numéricos y de cadena. No es posible indexar matrices anidadas ni objetos.
En clústeres con la versión V3.1.5.10 o posterior, los índices JSON no se crean automáticamente al crear una tabla. Debe crearlos manualmente con la sintaxis que se muestra a continuación.
En clústeres con versiones anteriores a la V3.1.5.10, los índices JSON se crean automáticamente para las columnas JSON después de la creación de la tabla.

Activación del índice tras la creación (tablas existentes)

Al agregar un índice JSON o un índice de matriz JSON a una tabla existente, el comportamiento de activación depende del motor de almacenamiento:

Tipo de tabla	Activación
Tablas XUANWU_V2 particionadas y no particionadas	Surte efecto de inmediato. No se requiere ningún trabajo de BUILD.
Tablas XUANWU no particionadas	Surte efecto una vez completado un trabajo de BUILD.
Tablas XUANWU particionadas	Surte efecto al finalizar un proceso de BUILD job run on the entire table.

Creación de un índice JSON

Todos los ejemplos de esta sección utilizan una tabla devices que almacena telemetría de dispositivos en formato JSON:

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

Un valor típico de info tiene el siguiente aspecto:

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

Crear un índice JSON al crear una tabla

Si especifica una o más columnas para crear un índice durante la creación de la tabla, AnalyticDB for MySQL no crea índices automáticamente para las demás columnas de la tabla.

Sintaxis

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

La sintaxis column_name->'$.json_path' utiliza la notación JSONPath: $ hace referencia a la raíz del documento JSON y .key accede a una propiedad del objeto. Este parámetro requiere la versión V3.1.6.8 o posterior.

Parámetros

Parámetro	Descripción
`index_name`	Nombre del índice. Debe ser único dentro de la tabla.
`column_name`	Nombre de la columna JSON que se va a indexar. Crea un índice sobre todo el documento JSON.
`column_name->'$.json_path'`	Columna JSON y una clave de propiedad específica. Cada índice abarca una clave de propiedad.

Para otros parámetros de CREATE TABLE, consulte CREATE TABLE.

Importante

Si una columna JSON ya tiene un índice, elimínelo antes de crear un índice de clave de propiedad en la misma columna.

Ejemplos

Indexar toda la columna JSON info:

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

Indexar únicamente la clave de propiedad status (requiere la versión V3.1.6.8 o posterior):

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

Crear un índice JSON en una tabla existente

Sintaxis

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

Parámetros

Parámetro	Descripción
`db_name`	Nombre de la base de datos.
`table_name`	Nombre de la tabla.
`index_name`	Nombre del índice. Debe ser único dentro de la tabla.
`column_name`	Nombre de la columna JSON que se va a indexar.
`column_name->'$.json_path'`	Columna JSON y una clave de propiedad específica. Requiere la versión V3.1.6.8 o posterior.

Importante

Si una columna JSON ya tiene un índice, elimínelo antes de crear un índice de clave de propiedad en la misma columna.

Ejemplos

Indexar toda la columna JSON info:

ALTER TABLE devices ADD KEY idx_info(info);

Indexar únicamente la clave de propiedad status:

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

Creación de un índice de matriz JSON

Los índices de matriz JSON aceleran las consultas de pertenencia en matrices JSON mediante JSON_CONTAINS y JSON_OVERLAPS. Este tipo de índice requiere la versión V3.1.10.6 o posterior.

La expresión JSONPath $[*] coincide con todos los elementos de una matriz JSON.

Los índices de matriz JSON solo admiten elementos de matriz numéricos y de cadena. No es posible indexar matrices anidadas ni objetos.

Crear un índice de matriz JSON al crear una tabla

Sintaxis

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

Parámetros

Parámetro	Descripción
`index_name`	Nombre del índice. Debe ser único dentro de la tabla.
`column_name->'$[*]'`	Columna JSON que se va a indexar. `$[]` selecciona todos los elementos de la matriz. Por ejemplo, `info->'$[]'` crea un índice de matriz en la columna `info`.

Ejemplo

Indexar la columna de matriz info en la tabla devices:

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

Crear un índice de matriz JSON en una tabla existente

Sintaxis

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

Parámetros

Parámetro	Descripción
`db_name`	Nombre de la base de datos.
`table_name`	Nombre de la tabla.
`index_name`	Nombre del índice. Debe ser único dentro de la tabla.
`column_name->'$[*]'`	Columna JSON que se va a indexar. `$[*]` selecciona todos los elementos de la matriz.

Ejemplo

Agregar un índice de matriz JSON en la columna info de la tabla devices:

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

Eliminación de un índice

Sintaxis

ALTER TABLE db_name.table_name DROP KEY index_name

Para obtener el nombre del índice, ejecute:

SHOW INDEX FROM db_name.table_name;

Ejemplos

Eliminar el índice idx_info de la tabla devices:

ALTER TABLE mydb.devices DROP KEY idx_info;

Eliminar el índice de matriz JSON idx_info_tags de la tabla devices:

ALTER TABLE mydb.devices DROP KEY idx_info_tags;

Pasos siguientes

JSON: Referencia del tipo de datos JSON para AnalyticDB for MySQL.
JSON functions: Funciones JSON compatibles con AnalyticDB for MySQL, incluidas JSON_CONTAINS y JSON_OVERLAPS.