Consulta de dados JSON semiestruturados - AnalyticDB: Central de Documentação do Alibaba Cloud

Requisitos de versão

Recurso	Versão mínima
Índice JSON (criação manual obrigatória)	V3.1.5.10
Índice JSON em chave de propriedade (`column->'$.path'`)	V3.1.6.8
Índice de array JSON	V3.1.10.6

Para visualizar ou atualizar a versão secundária do cluster, acesse o console do AnalyticDB for MySQL, abra a página Cluster Information e localize a seção Configuration Information.

Escolha o tipo de índice adequado

Tipo de índice	Indicado para	Funções compatíveis
Índice JSON	Buscas exatas de chave/valor em propriedades de objetos	—
Índice de array JSON	Consultas de pertinência em arrays JSON	`JSON_CONTAINS`, `JSON_OVERLAPS`

Cada índice JSON ou de array JSON abrange apenas uma coluna JSON. Para indexar várias colunas, crie um índice separado para cada uma.

Observações de uso

Índices JSON e de array JSON só podem ser criados em colunas do tipo JSON.
Índices de array JSON aceitam apenas elementos numéricos e de string. Arrays aninhados e objetos não são indexáveis.
Em clusters com versão V3.1.5.10 ou posterior, os índices JSON não são criados automaticamente durante a criação da tabela. Crie-os manualmente conforme a sintaxe abaixo.
Em clusters com versões anteriores à V3.1.5.10, os índices JSON são gerados automaticamente para colunas JSON após a criação da tabela.

Ativação do índice após a criação (tabelas existentes)

Ao adicionar um índice JSON ou de array JSON a uma tabela existente, o comportamento de ativação varia conforme o mecanismo de armazenamento:

Tipo de tabela	Ativação
Tabelas XUANWU_V2 particionadas e não particionadas	Entra em vigor imediatamente. Não é necessário executar um job BUILD.
Tabelas XUANWU não particionadas	Entra em vigor após a conclusão de um job BUILD.
Tabelas XUANWU particionadas	Entra em vigor após a conclusão de um job BUILD executado em toda a tabela.

Criar um índice JSON

Todos os exemplos nesta seção usam uma tabela devices que armazena telemetria de dispositivos como JSON:

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

Um valor típico de info tem a seguinte estrutura:

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

Criar um índice JSON ao criar uma tabela

Se você especificar uma ou mais colunas para indexação durante a criação da tabela, o AnalyticDB for MySQL não criará automaticamente índices para as demais colunas.

Sintaxe

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

A sintaxe column_name->'$.json_path' usa a notação JSONPath: $ refere-se à raiz do documento JSON e .key acessa uma propriedade do objeto. Esse parâmetro exige a versão V3.1.6.8 ou posterior.

Parâmetros

Parâmetro	Descrição
`index_name`	Nome do índice. Deve ser único na tabela.
`column_name`	Nome da coluna JSON a ser indexada. Cria um índice em todo o documento JSON.
`column_name->'$.json_path'`	Coluna JSON e uma chave de propriedade específica. Cada índice abrange uma chave de propriedade.

Para outros parâmetros de CREATE TABLE, consulte CREATE TABLE.

Importante

Se uma coluna JSON já tiver um índice, exclua-o antes de criar um índice de chave de propriedade na mesma coluna.

Exemplos

Indexe toda a coluna JSON info:

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

Indexe apenas a chave de propriedade status (requer V3.1.6.8 ou posterior):

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

Criar um índice JSON em uma tabela existente

Sintaxe

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

Parâmetros

Parâmetro	Descrição
`db_name`	Nome do banco de dados.
`table_name`	Nome da tabela.
`index_name`	Nome do índice. Deve ser único na tabela.
`column_name`	Nome da coluna JSON a ser indexada.
`column_name->'$.json_path'`	Coluna JSON e uma chave de propriedade específica. Requer V3.1.6.8 ou posterior.

Importante

Se uma coluna JSON já tiver um índice, exclua-o antes de criar um índice de chave de propriedade na mesma coluna.

Exemplos

Indexe toda a coluna JSON info:

ALTER TABLE devices ADD KEY idx_info(info);

Indexe apenas a chave de propriedade status:

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

Criar um índice de array JSON

Os índices de array JSON aceleram consultas de pertinência em arrays JSON com JSON_CONTAINS e JSON_OVERLAPS. Esse tipo de índice requer a versão V3.1.10.6 ou posterior.

A expressão JSONPath $[*] corresponde a todos os elementos de um array JSON.

Índices de array JSON aceitam apenas elementos numéricos e strings. Arrays aninhados e objetos não são indexáveis.

Criar um índice de array JSON ao criar uma tabela

Sintaxe

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

Parâmetros

Parâmetro	Descrição
`index_name`	Nome do índice. Deve ser único na tabela.
`column_name->'$[*]'`	Coluna JSON a ser indexada. `$[]` seleciona todos os elementos do array. Por exemplo, `info->'$[]'` cria um índice de array na coluna `info`.

Exemplo

Indexe a coluna de array info na tabela devices:

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

Criar um índice de array JSON em uma tabela existente

Sintaxe

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

Parâmetros

Parâmetro	Descrição
`db_name`	Nome do banco de dados.
`table_name`	Nome da tabela.
`index_name`	Nome do índice. Deve ser único na tabela.
`column_name->'$[*]'`	Coluna JSON a ser indexada. `$[*]` seleciona todos os elementos do array.

Exemplo

Adicione um índice de array JSON na coluna info da tabela devices:

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

Excluir um índice

Sintaxe

ALTER TABLE db_name.table_name DROP KEY index_name

Para obter o nome do índice, execute:

SHOW INDEX FROM db_name.table_name;

Exemplos

Exclua o índice idx_info da tabela devices:

ALTER TABLE mydb.devices DROP KEY idx_info;

Exclua o índice de array JSON idx_info_tags da tabela devices:

ALTER TABLE mydb.devices DROP KEY idx_info_tags;

Próximos passos

JSON: Referência do tipo de dado JSON para o AnalyticDB for MySQL.
Funções JSON: Funções JSON compatíveis com o AnalyticDB for MySQL, incluindo JSON_CONTAINS e JSON_OVERLAPS.