Tablestore:Operações de índice de pesquisa

Operações suportadas

Busca de texto completo

Corresponda dados em campos do tipo Text usando TEXT_MATCH para correspondência de tokens ou TEXT_MATCH_PHRASE para correspondência de frases.

TEXT_MATCH(fieldName, text [, options])

TEXT_MATCH_PHRASE(fieldName, text)

Exemplos

Consulte dados na coluna content que contenham o token "tablestore":

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore') LIMIT 10;

Consulte dados na coluna content que contenham "sql query" como uma frase consecutiva:

SELECT * FROM search_exampletable WHERE TEXT_MATCH_PHRASE(content, 'sql query') LIMIT 10;

Use o parâmetro options para corresponder a dados que contenham pelo menos 2 tokens:

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore is cool', 'or', '2') LIMIT 10;

Use o operador AND para exigir que todos os tokens estejam presentes:

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore is cool', 'and') LIMIT 10;

Consultas de array

Use a função ARRAY_EXTRACT para consultar dados em colunas do tipo array. Configure a coluna como tipo array no índice de pesquisa ativando a opção de array no console ou definindo IsArray como true no SDK. Ao gravar dados, os valores de array devem estar no formato de array JSON, como ["a","b","c"].

Mapeamento de tipos de dados

ARRAY_EXTRACT(col_name)

A função ARRAY_EXTRACT expande uma coluna de array e combina-se com operadores como condição da cláusula WHERE. Os operadores suportados incluem igualdade (=), intervalo (>, <) e LIKE.

Limitações

• ARRAY_EXTRACT só pode ser usado em tabelas de mapeamento de índice de pesquisa, permitindo apenas um parâmetro de coluna de array por chamada. A função serve exclusivamente como condição da cláusula WHERE, não podendo ser usada como expressão SELECT ou para agregação e ordenação.

• Uma coluna de array sem ARRAY_EXTRACT pode ser usada como nome ou expressão de coluna SELECT, mas não suporta agregação nem ordenação.

• Quando combinado com operadores como condição de consulta, o ARRAY_EXTRACT não suporta conversão de tipo de dados. O valor da consulta deve corresponder ao tipo de dados da coluna de array. Por exemplo, uma coluna de array do tipo Long suporta ARRAY_EXTRACT(col) = 1, mas não suporta ARRAY_EXTRACT(col) = '1'.

• Elementos de array do tipo Text devem ser usados com a função TEXT_MATCH ou TEXT_MATCH_PHRASE, como em TEXT_MATCH(ARRAY_EXTRACT(col_text), 'keyword').

Exemplos

-- Query rows that contain the value 'apple' in the array
SELECT * FROM search_exampletable WHERE ARRAY_EXTRACT(col_array) = 'apple';

-- Query rows that contain elements starting with 'd' in the array
SELECT * FROM search_exampletable WHERE ARRAY_EXTRACT(col_array) LIKE 'd%';

Consultas de tipo aninhado

Colunas de tipo aninhado armazenam arrays JSON nos quais cada elemento contém múltiplas subcolunas. O tipo de dados da coluna na tabela de dados deve ser String. Configure a coluna como tipo Nested e especifique os tipos de dados das subcolunas ao criar o índice de pesquisa.

Ao criar uma tabela de mapeamento, defina colunas de tipo aninhado como MEDIUMTEXT. Subcolunas internas são criadas automaticamente e podem ser visualizadas com DESCRIBE, como col_nested.name e col_nested.age. Nas consultas, os nomes das subcolunas usam o formato nested_column.sub_column, com pontos (.) separando múltiplos níveis de aninhamento, como col1.col2.col3.

Mapeamento de tipos de dados

Métodos de consulta

SELECT * FROM search_exampletable WHERE `col_nested.age` > 30;

NESTED_QUERY(subcol_column_condition)

Diferença entre os dois métodos

Suponha que a coluna aninhada tags contenha os seguintes dados de linha: [{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]:

• tags.tagName — Corresponde, pois o primeiro elemento atende à condição tagName e o segundo elemento atende à condição score.

• NESTED_QUERY( — Não corresponde, pois nenhum elemento individual atende a ambas as condições simultaneamente.

Limitações

• NESTED_QUERY só pode ser usado em tabelas de mapeamento de índice de pesquisa e exclusivamente como cláusula WHERE. Não é permitido seu uso como expressão SELECT ou para agregação, agrupamento ou ordenação.

• Subcolunas aninhadas não podem ser usadas como expressões SELECT ou para agregação, agrupamento ou ordenação.

• ALTER TABLE não pode adicionar ou excluir subcolunas aninhadas diretamente. Você só pode adicionar ou excluir toda a coluna aninhada, e as subcolunas são adicionadas ou excluídas automaticamente junto com ela.

• Subcolunas aninhadas não suportam conversão de tipo de dados ou computações de funções que não possam ser enviadas (pushdown) para o índice de pesquisa. Certifique-se de que os tipos de dados das subcolunas aninhadas estejam corretos.

Exemplos

-- Direct subcolumn query: query rows where age > 30 in the nested column
SELECT * FROM search_exampletable WHERE `col_nested.age` > 30;

-- NESTED_QUERY: query rows where a single element has name starting with 'I' and age < 20
SELECT * FROM search_exampletable WHERE NESTED_QUERY(`col_nested.name` LIKE 'I%' AND `col_nested.age` < 20);

-- Multi-level nesting
SELECT * FROM search_exampletable WHERE NESTED_QUERY(`col1.col2` = 1 AND NESTED_QUERY(`col1.col3.col4` = 2));

Consultas de coluna virtual

As colunas virtuais do índice de pesquisa permitem consultar novos campos e tipos modificando o esquema do índice de pesquisa, sem alterar a estrutura de armazenamento da tabela de dados. As colunas virtuais são definidas na tabela de mapeamento com seus tipos de dados SQL reais.

Mapeamento de tipos de dados

Uso suportado

• Filtre dados em cláusulas WHERE. O tipo de dados da coluna virtual na condição deve corresponder ao tipo do parâmetro de consulta.

• Use em agregação e agrupamento. O tipo de dados de origem da coluna virtual deve ser compatível com a operação. Por exemplo, apenas os tipos Long e Double suportam SUM. Colunas virtuais do tipo Keyword não podem ser somadas, e colunas virtuais do tipo Text não suportam agrupamento.

• Consultas TopN e ordenação são suportadas. A ordenação requer LIMIT.

Limitações

• Colunas virtuais só podem ser usadas em tabelas de mapeamento de índice de pesquisa.

• O uso de colunas virtuais restringe-se a condições de consulta. Elas não podem ser usadas em SELECT para retornar valores de coluna. Para retornar valores, especifique a coluna de origem da coluna virtual. SELECT * não é afetado e exclui automaticamente as colunas virtuais dos resultados.

• Colunas virtuais não podem ser usadas para comparações de colunas, computações ou JOINs.

• Colunas virtuais não suportam conversão de tipo de dados ou computações de funções que não possam ser enviadas (pushdown) para o índice de pesquisa. Atualmente, apenas funções agregadas podem ser enviadas em consultas SQL.

Exemplos

Crie uma tabela de mapeamento de índice de pesquisa que inclua colunas virtuais:

CREATE TABLE search_exampletable(
    col_keyword MEDIUMTEXT,
    col_keyword_virtual_long BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"exampletable_index","table_name":"exampletable"}';

Consulte com colunas virtuais:

SELECT * FROM search_exampletable WHERE col_keyword_virtual_long > 100 LIMIT 10;

Busca vetorial

Use a função VECTOR_QUERY_FLOAT32 para consultas de vizinho mais próximo aproximado (ANN). Campos vetoriais são armazenados como strings na tabela de dados. Configure-os como tipo vetor no índice de pesquisa e especifique as dimensões, o tipo de dados e a métrica de distância. O tipo de dados SQL das colunas vetoriais na tabela de mapeamento é MEDIUMTEXT.

VECTOR_QUERY_FLOAT32

VECTOR_QUERY_FLOAT32(fieldName, float32QueryVector, topK, filter)

Função SCORE()

Use SCORE() com VECTOR_QUERY_FLOAT32 como expressão SELECT para retornar a pontuação de relevância de cada resultado. Uma pontuação mais alta indica maior similaridade.

SCORE()

Limitações

• VECTOR_QUERY_FLOAT32 só pode ser usado em tabelas de mapeamento de índice de pesquisa e deve ser utilizado com LIMIT. Cláusulas HAVING não são suportadas.

• VECTOR_QUERY_FLOAT32 só pode ser usado como cláusula WHERE. Não é permitido seu uso como expressão SELECT ou para agregação, agrupamento ou ordenação.

• SCORE() só pode ser usado com VECTOR_QUERY_FLOAT32 e exclusivamente como expressão SELECT. Não pode ser usado em cláusulas WHERE, agregação ou ordenação.

• Outras condições na cláusula WHERE devem suportar pushdown para o índice de pesquisa. Caso contrário, a consulta falhará. Para operadores de pushdown suportados, consulte Otimização de consulta.

Exemplos

Consulte os 10 resultados mais similares ao vetor especificado na coluna col_vector:

SELECT *, SCORE() FROM exampletable WHERE VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 10) LIMIT 10;

Use filter para reduzir o conjunto de candidatos antes da busca vetorial e obter resultados mais precisos:

SELECT *, SCORE() FROM exampletable WHERE VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 100, col_keyword='cat_a' AND year_num=2024) LIMIT 10;

Use AND na cláusula WHERE para filtragem pós-busca vetorial. Os resultados topK podem não incluir todas as linhas correspondentes:

SELECT *, SCORE() FROM exampletable WHERE col_keyword='cat_a' AND VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 500) LIMIT 10;

Funções JSON

As funções JSON do SQL do Tablestore seguem a sintaxe do MySQL 5.7 e extraem dados de colunas no formato JSON.

->> (Extração de caminho JSON)

Extrai o valor no caminho especificado de uma coluna JSON e remove as aspas para convertê-lo em string. Equivalente a JSON_UNQUOTE(JSON_EXTRACT()).

column->>'$.path'

Exemplo

SELECT col_json->>'$.city' AS city FROM exampletable LIMIT 10;

JSON_UNQUOTE

Remove as aspas externas de um valor JSON e retorna uma string. Retorna NULL se o argumento for NULL.

JSON_UNQUOTE(json_val)

Exemplo

SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.city')) AS city FROM exampletable LIMIT 10;

JSON_EXTRACT

Extrai o subdocumento no caminho especificado de uma coluna JSON. O valor retornado mantém o formato JSON, com valores de string envoltos em aspas. Múltiplos caminhos podem ser especificados, e os resultados são retornados em formato de array.

JSON_EXTRACT(json_doc, path[, path] ...)

Exemplos

Extraia um único caminho:

SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.city')) AS city FROM exampletable WHERE pk = 1;

Extraia múltiplos caminhos. Os resultados são retornados em formato de array:

-- Assume col_json contains {"a": 1, "b": 2, "c": {"d": 4}}
SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.a', '$.b', '$.c.d')) AS subdoc FROM exampletable WHERE pk = 1;
-- Result: [1, 2, 4]

Sintaxe de Caminho JSON

Caminhos devem começar com $, que representa todo o documento JSON. Adicione seletores de caminho após $. Seletores podem ser combinados.

Exemplos de consulta de objeto JSON

Suponha que a coluna JSON contenha {"a": 1, "f": [1, 2, 3], "c": {"d": 4}}:

Exemplos de consulta de array JSON

Suponha que a coluna JSON contenha [3, {"a": [5, 6], "b": 10}, [99, 100]]. Valores de retorno não escalares suportam consultas aninhadas.