Tablestore:Search index operations

Operaciones compatibles

Búsqueda de texto completo

Busque datos en campos de tipo Text mediante TEXT_MATCH para coincidencia de tokens o TEXT_MATCH_PHRASE para coincidencia de frases.

TEXT_MATCH(fieldName, text [, options])

TEXT_MATCH_PHRASE(fieldName, text)

Ejemplos

Consulte datos en la columna content que contengan el token "tablestore":

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

Consulte datos en la columna content que contengan "sql query" como frase consecutiva:

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

Utilice el parámetro options para buscar datos que contengan como mínimo 2 tokens:

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

Use el operador AND para exigir que todos los tokens estén presentes:

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

Consultas de matriz

Utilice la función ARRAY_EXTRACT para consultar datos en columnas de tipo matriz. Configure la columna como tipo matriz en el índice de búsqueda; para ello, habilite la opción de matriz en la consola o establezca IsArray en true mediante el SDK. Al escribir datos, los valores de la matriz deben tener formato de matriz JSON, por ejemplo ["a","b","c"].

Asignación de tipos de datos

ARRAY_EXTRACT(col_name)

ARRAY_EXTRACT expande una columna de matriz y se combina con operadores como condición de la cláusula WHERE. Los operadores compatibles incluyen igualdad (=), rango (>, <) y LIKE.

Límites

• ARRAY_EXTRACT solo se puede utilizar en tablas de asignación del índice de búsqueda, y únicamente se permite un parámetro de columna de matriz por llamada. La función solo sirve como condición de la cláusula WHERE. No se admite su uso como expresión SELECT ni para agregaciones u ordenamientos.

• Una columna de matriz sin ARRAY_EXTRACT puede usarse como nombre de columna o expresión en SELECT, pero no es válida para agregaciones u ordenamientos.

• Cuando ARRAY_EXTRACT se combina con operadores como condición de consulta, no se admite la conversión de tipos de datos. El valor de consulta debe coincidir con el tipo de datos de la columna de matriz. Por ejemplo, una columna de matriz de tipo Long admite ARRAY_EXTRACT(col) = 1, pero no admite ARRAY_EXTRACT(col) = '1'.

• Los elementos de matriz de tipo Text deben utilizarse junto con la función TEXT_MATCH o TEXT_MATCH_PHRASE, por ejemplo TEXT_MATCH(ARRAY_EXTRACT(col_text), 'keyword').

Ejemplos

-- 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 anidado

Las columnas de tipo anidado almacenan matrices JSON donde cada elemento contiene varias subcolumnas. El tipo de datos de la columna en la tabla de datos debe ser String. Configure la columna como tipo Nested y especifique los tipos de datos de las subcolumnas al crear el índice de búsqueda.

Al crear una tabla de asignación, defina las columnas de tipo anidado como MEDIUMTEXT. Las subcolumnas internas se crean automáticamente y pueden consultarse con DESCRIBE, por ejemplo col_nested.name y col_nested.age. En las consultas, los nombres de las subcolumnas siguen el formato nested_column.sub_column, donde los puntos (.) separan múltiples niveles de anidamiento, como en col1.col2.col3.

Asignación de tipos de datos

Métodos de consulta

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

NESTED_QUERY(subcol_column_condition)

Diferencia entre ambos métodos

Suponga que la columna anidada tags contiene los siguientes datos de fila: [{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]:

• tags.tagName — Coincide, porque el primer elemento cumple la condición de tagName y el segundo elemento cumple la condición de score.

• NESTED_QUERY( — No coincide, porque ningún elemento individual cumple ambas condiciones simultáneamente.

Límites

• NESTED_QUERY solo se puede utilizar en tablas de asignación del índice de búsqueda y exclusivamente como cláusula WHERE. No se admite su uso como expresión SELECT ni para agregaciones, agrupaciones u ordenamientos.

• Las subcolumnas anidadas no pueden usarse como expresiones SELECT ni para agregaciones, agrupaciones u ordenamientos.

• ALTER TABLE no puede agregar ni eliminar subcolumnas anidadas directamente. Solo es posible agregar o eliminar la columna anidada completa, y las subcolumnas se agregan o eliminan automáticamente con ella.

• Las subcolumnas anidadas no admiten conversión de tipos de datos ni cálculos de funciones que no puedan delegarse al índice de búsqueda. Asegúrese de que los tipos de datos de las subcolumnas anidadas sean correctos.

Ejemplos

-- 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 columnas virtuales

Las columnas virtuales del índice de búsqueda permiten consultar nuevos campos y tipos al modificar el esquema del índice, sin alterar la estructura de almacenamiento de la tabla de datos. Las columnas virtuales se definen en la tabla de asignación con sus tipos de datos SQL reales.

Asignación de tipos de datos

Usos compatibles

• Filtrar datos en cláusulas WHERE. El tipo de datos de la columna virtual en la condición debe coincidir con el tipo del parámetro de consulta.

• Utilizar en agregaciones y agrupaciones. El tipo de datos de origen de la columna virtual debe ser compatible con la operación. Por ejemplo, solo los tipos Long y Double admiten SUM. Las columnas virtuales de tipo Keyword no pueden sumarse, y las columnas virtuales de tipo Text no admiten agrupaciones.

• Se admiten consultas TopN y ordenamientos. El ordenamiento requiere LIMIT.

Límites

• Las columnas virtuales solo pueden utilizarse en tablas de asignación del índice de búsqueda.

• Las columnas virtuales solo pueden emplearse en condiciones de consulta. No es posible usarlas en SELECT para devolver valores de columna. Para devolver valores, especifique la columna de origen de la columna virtual. SELECT * no se ve afectado y excluye automáticamente las columnas virtuales de los resultados.

• Las columnas virtuales no pueden utilizarse para comparaciones de columnas, cálculos ni JOIN.

• Las columnas virtuales no admiten conversión de tipos de datos ni cálculos de funciones que no puedan delegarse al índice de búsqueda. Actualmente, solo las funciones de agregación pueden delegarse en consultas SQL.

Ejemplos

Cree una tabla de asignación del índice de búsqueda que incluya columnas virtuales:

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

Realice consultas con columnas virtuales:

SELECT * FROM search_exampletable WHERE col_keyword_virtual_long > 100 LIMIT 10;

Búsqueda vectorial

Utilice la función VECTOR_QUERY_FLOAT32 para consultas de vecinos más cercanos aproximados (ANN). Los campos vectoriales se almacenan como cadenas en la tabla de datos. Configúrelos como tipo vector en el índice de búsqueda, y especifique las dimensiones, el tipo de datos y la métrica de distancia. El tipo de datos SQL de las columnas vectoriales en la tabla de asignación es MEDIUMTEXT.

VECTOR_QUERY_FLOAT32

VECTOR_QUERY_FLOAT32(fieldName, float32QueryVector, topK, filter)

Función SCORE()

Utilice SCORE() junto con VECTOR_QUERY_FLOAT32 como expresión SELECT para devolver la puntuación de relevancia de cada resultado. Una puntuación más alta indica mayor similitud.

SCORE()

Límites

• VECTOR_QUERY_FLOAT32 solo se puede utilizar en tablas de asignación del índice de búsqueda y debe usarse con LIMIT. No se admiten cláusulas HAVING.

• VECTOR_QUERY_FLOAT32 solo puede emplearse como cláusula WHERE. No se admite su uso como expresión SELECT ni para agregaciones, agrupaciones u ordenamientos.

• SCORE() solo puede utilizarse con VECTOR_QUERY_FLOAT32 y exclusivamente como expresión SELECT. No es válido en cláusulas WHERE, agregaciones u ordenamientos.

• Las demás condiciones en la cláusula WHERE deben admitir la delegación al índice de búsqueda. De lo contrario, la consulta fallará. Para consultar los operadores que admiten delegación, véase Query optimization.

Ejemplos

Consulte los 10 resultados más similares al vector especificado en la columna col_vector:

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

Utilice filter para reducir el conjunto de candidatos antes de la búsqueda vectorial y obtener resultados más 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;

Utilice AND en la cláusula WHERE para filtrar después de la búsqueda vectorial. Es posible que los resultados topK no incluyan todas las filas coincidentes:

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;

Funciones JSON

Las funciones JSON de SQL en Tablestore siguen la sintaxis de MySQL 5.7 y extraen datos de columnas con formato JSON.

->> (extracción de ruta JSON)

Extrae el valor en la ruta especificada de una columna JSON y le quita las comillas para convertirlo en cadena. Equivale a JSON_UNQUOTE(JSON_EXTRACT()).

column->>'$.path'

Ejemplo

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

JSON_UNQUOTE

Elimina las comillas externas de un valor JSON y devuelve una cadena. Devuelve NULL si el argumento es NULL.

JSON_UNQUOTE(json_val)

Ejemplo

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

JSON_EXTRACT

Extrae el subdocumento en la ruta especificada desde una columna JSON. El valor devuelto conserva el formato JSON, con los valores de cadena entre comillas. Es posible especificar múltiples rutas, y los resultados se devuelven en formato de matriz.

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

Ejemplos

Extraiga una sola ruta:

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

Extraiga múltiples rutas. Los resultados se devuelven en formato de matriz:

-- 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]

Sintaxis de rutas JSON

Las rutas deben comenzar con $, que representa todo el documento JSON. Agregue selectores de ruta después de $. Los selectores pueden combinarse.

Ejemplos de consulta de objetos JSON

Suponga que la columna JSON contiene {"a": 1, "f": [1, 2, 3], "c": {"d": 4}}:

Ejemplos de consulta de matrices JSON

Suponga que la columna JSON contiene [3, {"a": [5, 6], "b": 10}, [99, 100]]. Los valores devueltos no escalares admiten consultas anidadas.