Tablestore:Search index operations

Opérations prises en charge

Recherche en texte intégral

Faites correspondre les données des champs de type Text à l'aide de TEXT_MATCH pour une correspondance par jetons ou de TEXT_MATCH_PHRASE pour une correspondance par expression.

TEXT_MATCH(fieldName, text [, options])

TEXT_MATCH_PHRASE(fieldName, text)

Exemples

Interrogez les données de la colonne content contenant le jeton « tablestore » :

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

Interrogez les données de la colonne content contenant l'expression consécutive « sql query » :

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

Utilisez le paramètre options pour faire correspondre les données contenant au moins 2 jetons :

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

Utilisez l'opérateur AND pour exiger la présence de tous les jetons :

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

Requêtes sur tableau

Utilisez la fonction ARRAY_EXTRACT pour interroger les données dans les colonnes de type tableau. Configurez la colonne comme étant de type tableau dans l'index de recherche en activant l'option array dans la console ou en définissant IsArray sur true dans le SDK. Lors de l'écriture des données, les valeurs de tableau doivent être au format de tableau JSON, par exemple ["a","b","c"].

Mappage des types de données

ARRAY_EXTRACT(col_name)

ARRAY_EXTRACT développe une colonne de type tableau et se combine avec des opérateurs en tant que condition de clause WHERE. Les opérateurs pris en charge incluent l'égalité (=), les plages (>, <) et LIKE.

Limitations

• ARRAY_EXTRACT s'utilise uniquement sur les tables de mappage d'index de recherche, et un seul paramètre de colonne de tableau est autorisé par appel. La fonction s'utilise uniquement comme condition de clause WHERE. Elle ne peut pas servir d'expression SELECT ni pour l'agrégation et le tri.

• Une colonne de type tableau sans ARRAY_EXTRACT peut servir de nom de colonne SELECT ou d'expression, mais ne peut pas être utilisée pour l'agrégation et le tri.

• Lorsque ARRAY_EXTRACT est combiné avec des opérateurs comme condition de requête, la conversion de type de données n'est pas prise en charge. La valeur de requête doit correspondre au type de données de la colonne de tableau. Par exemple, une colonne de tableau de type Long prend en charge ARRAY_EXTRACT(col) = 1 mais ne prend pas en charge ARRAY_EXTRACT(col) = '1'.

• Les éléments de tableau de type Text doivent être utilisés avec la fonction TEXT_MATCH ou TEXT_MATCH_PHRASE, par exemple TEXT_MATCH(ARRAY_EXTRACT(col_text), 'keyword').

Exemples

-- 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%';

Requêtes de type imbriqué

Les colonnes de type imbriqué stockent des tableaux JSON dans lesquels chaque élément contient plusieurs sous-colonnes. Le type de données de la colonne dans la table de données doit être String. Configurez la colonne comme étant de type Nested et spécifiez les types de données des sous-colonnes lors de la création de l'index de recherche.

Lors de la création d'une table de mappage, définissez les colonnes de type imbriqué comme MEDIUMTEXT. Les sous-colonnes internes sont créées automatiquement et peuvent être affichées avec DESCRIBE, par exemple col_nested.name et col_nested.age. Dans les requêtes, les noms de sous-colonnes utilisent le format nested_column.sub_column, avec des points (.) séparant les niveaux d'imbrication multiples, par exemple col1.col2.col3.

Mappage des types de données

Méthodes de requête

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

NESTED_QUERY(subcol_column_condition)

Différence entre les deux méthodes

Supposons que la colonne imbriquée tags contienne les données de ligne suivantes : [{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}] :

• tags.tagName — Correspondance, car le premier élément satisfait la condition tagName et le second élément satisfait la condition score.

• NESTED_QUERY( — Pas de correspondance, car aucun élément unique ne satisfait les deux conditions.

Limitations

• NESTED_QUERY s'utilise uniquement sur les tables de mappage d'index de recherche et uniquement comme clause WHERE. Elle ne peut pas servir d'expression SELECT ni pour l'agrégation, le regroupement ou le tri.

• Les sous-colonnes imbriquées ne peuvent pas servir d'expressions SELECT ni pour l'agrégation, le regroupement ou le tri.

• ALTER TABLE ne peut pas ajouter ou supprimer directement des sous-colonnes imbriquées. Vous pouvez uniquement ajouter ou supprimer la colonne imbriquée entière, et les sous-colonnes sont automatiquement ajoutées ou supprimées avec elle.

• Les sous-colonnes imbriquées ne prennent pas en charge la conversion de type de données ni les calculs de fonctions qui ne peuvent pas être poussés vers l'index de recherche. Assurez-vous que les types de données des sous-colonnes imbriquées sont corrects.

Exemples

-- 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));

Requêtes sur colonne virtuelle

Les colonnes virtuelles d'index de recherche vous permettent d'interroger de nouveaux champs et types en modifiant le schéma de l'index de recherche, sans changer la structure de stockage de la table de données. Les colonnes virtuelles sont définies dans la table de mappage avec leurs types de données SQL réels.

Mappage des types de données

Utilisation prise en charge

• Filtrez les données dans les clauses WHERE. Le type de données de la colonne virtuelle dans la condition doit correspondre au type de paramètre de requête.

• Utilisation dans l'agrégation et le regroupement. Le type de données source de la colonne virtuelle doit être compatible avec l'opération. Par exemple, seuls les types Long et Double prennent en charge SUM. Les colonnes virtuelles de type Keyword ne peuvent pas être sommées, et les colonnes virtuelles de type Text ne prennent pas en charge le regroupement.

• Les requêtes TopN et le tri sont pris en charge. Le tri nécessite LIMIT.

Limitations

• Les colonnes virtuelles s'utilisent uniquement dans les tables de mappage d'index de recherche.

• Les colonnes virtuelles s'utilisent uniquement dans les conditions de requête. Elles ne peuvent pas être utilisées dans SELECT pour renvoyer des valeurs de colonne. Pour renvoyer des valeurs, spécifiez la colonne source de la colonne virtuelle. SELECT * n'est pas affecté et exclut automatiquement les colonnes virtuelles des résultats.

• Les colonnes virtuelles ne peuvent pas être utilisées pour les comparaisons de colonnes, les calculs ou les JOINs.

• Les colonnes virtuelles ne prennent pas en charge la conversion de type de données ni les calculs de fonctions qui ne peuvent pas être poussés vers l'index de recherche. Actuellement, seules les fonctions d'agrégation peuvent être poussées dans les requêtes SQL.

Exemples

Créez une table de mappage d'index de recherche incluant des colonnes virtuelles :

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

Requête avec des colonnes virtuelles :

SELECT * FROM search_exampletable WHERE col_keyword_virtual_long > 100 LIMIT 10;

Recherche vectorielle

Utilisez la fonction VECTOR_QUERY_FLOAT32 pour les requêtes de plus proches voisins approximatifs (ANN). Les champs vectoriels sont stockés sous forme de chaînes dans la table de données. Configurez-les comme étant de type vector dans l'index de recherche, et spécifiez les dimensions, le type de données et la métrique de distance. Le type de données SQL des colonnes vectorielles dans la table de mappage est MEDIUMTEXT.

VECTOR_QUERY_FLOAT32

VECTOR_QUERY_FLOAT32(fieldName, float32QueryVector, topK, filter)

Fonction SCORE()

Utilisez SCORE() avec VECTOR_QUERY_FLOAT32 comme expression SELECT pour renvoyer le score de pertinence de chaque résultat. Un score plus élevé indique une similarité plus grande.

SCORE()

Limitations

• VECTOR_QUERY_FLOAT32 s'utilise uniquement sur les tables de mappage d'index de recherche et doit être utilisé avec LIMIT. Les clauses HAVING ne sont pas prises en charge.

• VECTOR_QUERY_FLOAT32 s'utilise uniquement comme clause WHERE. Elle ne peut pas servir d'expression SELECT ni pour l'agrégation, le regroupement ou le tri.

• SCORE() s'utilise uniquement avec VECTOR_QUERY_FLOAT32 et uniquement comme expression SELECT. Elle ne peut pas être utilisée dans les clauses WHERE, l'agrégation ou le tri.

• Les autres conditions de la clause WHERE doivent prendre en charge le pushdown vers l'index de recherche. Sinon, la requête échoue. Pour les opérateurs de pushdown pris en charge, consultez la rubrique Optimisation des requêtes.

Exemples

Interrogez les 10 résultats les plus similaires au vecteur spécifié dans la colonne col_vector :

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

Utilisez filter pour réduire l'ensemble de candidats avant la recherche vectorielle afin d'obtenir des résultats plus précis :

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;

Utilisez AND dans la clause WHERE pour le filtrage post-recherche vectorielle. Les résultats topK peuvent ne pas inclure toutes les lignes correspondantes :

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;

Fonctions JSON

Les fonctions JSON SQL de Tablestore suivent la syntaxe MySQL 5.7 et extraient les données des colonnes au format JSON.

->> (extraction de chemin JSON)

Extrait la valeur au chemin spécifié d'une colonne JSON et la convertit en chaîne sans guillemets. Équivalent à JSON_UNQUOTE(JSON_EXTRACT()).

column->>'$.path'

Exemple

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

JSON_UNQUOTE

Supprime les guillemets extérieurs d'une valeur JSON et renvoie une chaîne. Renvoie NULL si l'argument est NULL.

JSON_UNQUOTE(json_val)

Exemple

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

JSON_EXTRACT

Extrait le sous-document au chemin spécifié d'une colonne JSON. La valeur de retour conserve le format JSON, avec les valeurs de chaîne entourées de guillemets. Plusieurs chemins peuvent être spécifiés, et les résultats sont renvoyés au format tableau.

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

Exemples

Extraire un chemin unique :

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

Extraire plusieurs chemins. Les résultats sont renvoyés au format tableau :

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

Syntaxe de chemin JSON

Les chemins doivent commencer par $, qui représente l'intégralité du document JSON. Ajoutez des sélecteurs de chemin après $. Les sélecteurs peuvent être combinés.

Exemples de requête d'objet JSON

Supposons que la colonne JSON contienne {"a": 1, "f": [1, 2, 3], "c": {"d": 4}} :

Exemples de requête de tableau JSON

Supposons que la colonne JSON contienne [3, {"a": [5, 6], "b": 10}, [99, 100]]. Les valeurs de retour non scalaires prennent en charge les requêtes imbriquées.