User guide for search indexes on the JSON data type in Lindorm - Lindorm

Lindorm permet de créer des index de recherche sur les colonnes JSON des tables larges. Les fonctions de requête disponibles dépendent de la structure de vos données JSON. Ce guide présente les trois modèles JSON pris en charge — tableau d'éléments simples, objet JSON et tableau d'objets — et indique quel type d'index et quelles fonctions de requête s'appliquent à chacun.

Prérequis

Avant de commencer, assurez-vous que :

Le moteur LindormTable est en version 2.8.5 ou ultérieure
Le moteur Lindorm LTS est en version 3.8.13.3 ou ultérieure

Pour vérifier ou mettre à niveau la version de votre moteur, consultez la rubrique Mise à jour des versions mineures.

Remarques sur l'utilisation

Si vous utilisez l'inférence automatique des types JSON et que le type de données d'un champ varie d'une écriture à l'autre (par exemple, le champ a est d'abord écrit sous forme de chaîne, puis sous forme de nombre), le système conserve le type déduit lors de la première écriture. Il ignore les valeurs suivantes qui ne correspondent pas à ce type.
Si un document JSON contient de nombreux champs internes, l'inférence automatique peut atteindre la limite de colonnes de l'index de recherche et provoquer l'échec des tâches de synchronisation. Nous vous recommandons d'indexer uniquement les champs JSON que vous interrogez réellement (voir l'exemple ci-dessous). La limite de colonnes par défaut est de 1000. Sur LindormTable 2.8.6.1 et versions ultérieures, vous pouvez ajuster cette limite avec la commande SQL suivante :
```
ALTER INDEX idx ON search_table SET SEARCH_INDEX_MAX_COLUMN_COUNT='2000';
```

Choisir le bon type d'index

Avant de créer un index de recherche sur une colonne JSON, identifiez le modèle auquel vos données correspondent. Le mélange de modèles au sein d'une même colonne entraîne l'ignorance silencieuse des données invalides lors de l'indexation.

Modèle JSON	Exemple	Type d'index	Fonctions de requête prises en charge
Tableau d'éléments simples	`[1, 2, 3]` ou `["a", "b"]`	`mapping` avec un type scalaire (`keyword`, `integer`, etc.)	`JSON_CONTAINS`, `JSON_CONTAINS_ANY`
Objet JSON	`{"name": "Alice", "age": 13}`	`type=jsonobject` ou `mapping` avec `"type": "object"`	`JSON_EXTRACT`, `JSON_EXTRACT_STRING`, `JSON_CONTAINS`, `JSON_CONTAINS_ANY` (sur les tableaux imbriqués), `MATCH`...`AGAINST`
Tableau d'objets	`[{"name": "Alice"}, {"name": "Bob"}]`	`type=jsonarray` ou `mapping` avec `"type": "nested"`	`SEARCH_QUERY` avec DSL Elasticsearch uniquement

Important

Ne mélangez pas les trois modèles au sein d'une même colonne. Les données invalides risquent d'être ignorées et non indexées.

Tableau d'éléments simples

Un tableau d'éléments simples stocke une liste plate de valeurs scalaires au niveau supérieur, comme ["101", "102", "109"] (tableau de chaînes) ou [1, 2, 3] (tableau d'entiers).

Créer une table large

CREATE TABLE test_json_array(id VARCHAR, c1 JSON, c2 JSON, PRIMARY KEY (id));

Insérer des données

UPSERT INTO test_json_array(id, c1, c2) VALUES ('1001', '["101", "102", "109"]', '[1, 2, 3]');
UPSERT INTO test_json_array(id, c1, c2) VALUES ('1002', '["999", "888", "777"]', '[1, 2, 3, 4, 5]');

Créer un index de recherche

Spécifiez le type d'élément dans le paramètre mapping. Utilisez keyword pour les tableaux de chaînes et integer pour les tableaux d'entiers.

CREATE INDEX idx USING SEARCH ON test_json_array(
  c1(mapping='{
    "type": "keyword"
  }'),
  c2(mapping='{
    "type": "integer"
  }')
);

Interroger

Deux fonctions sont disponibles :

JSON_CONTAINS : renvoie les lignes où toutes les valeurs spécifiées sont présentes dans le tableau
JSON_CONTAINS_ANY : renvoie les lignes où au moins une des valeurs spécifiées est présente

JSON_CONTAINS — lignes contenant toutes les valeurs spécifiées

-- No row contains both "101" and "999", so the result is empty
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS(c1, '["101", "999"]');
Empty set (0.11 sec)

-- id=1001 contains both "101" and "102"
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS(c1, '["101", "102"]');
+------+-----------------------+-----------+
| id   | c1                    | c2        |
+------+-----------------------+-----------+
| 1001 | ["101", "102", "109"] | [1, 2, 3] |
+------+-----------------------+-----------+
1 row in set (0.01 sec)

JSON_CONTAINS_ANY — lignes contenant au moins une des valeurs spécifiées

-- id=1001 has "101" and id=1002 has "999", so both rows are returned
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS_ANY(c1, '["101", "999"]');
+------+-----------------------+---------------------+
| id   | c1                    | c2                  |
+------+-----------------------+---------------------+
| 1002 | ["999", "888", "777"] | [1, 2, 3, 5, 9, 10] |
| 1001 | ["101", "102", "109"] | [1, 2, 3]           |
+------+-----------------------+---------------------+
2 rows in set (0.01 sec)

Objet JSON

Un objet JSON stocke un document structuré au niveau supérieur, comme {"name": "Alice", "age": 13, "hobbies": ["read", "badminton"]}. Les champs peuvent être des valeurs scalaires, des tableaux ou des objets imbriqués.

Créer une table large

CREATE TABLE test_json_object(id VARCHAR, user_info JSON, PRIMARY KEY (id));

Insérer des données

UPSERT INTO test_json_object(id, user_info) VALUES ('1001', '{"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang Province", "hobbies": ["play games", "read", "badminton"]}');
UPSERT INTO test_json_object(id, user_info) VALUES ('1002', '{"name": "Bob", "age": 9, "address": "Ningbo, Zhejiang Province", "hobbies": ["play games"]}');
UPSERT INTO test_json_object(id, user_info) VALUES ('1003', '{"name": "John", "age": 21, "address": "Shenzhen, Guangdong Province", "hobbies": ["read", "badminton", "food"]}');

Créer un index de recherche

Deux approches sont possibles. Privilégiez autant que possible la structure prédéfinie.

Inférence automatique du type

Spécifiez type=jsonobject pour permettre au système de déduire les types de champs à partir de la première valeur écrite. Par exemple, user_info.name est déduit comme une chaîne et user_info.age comme un numérique. Le système ignore les données écrites ultérieurement qui ne correspondent pas aux types déduits afin de préserver la cohérence de l'index.

CREATE INDEX idx USING SEARCH ON test_json_object(user_info(type=jsonobject));

Prédéfinir la structure des champs (recommandé)

L'inférence automatique du type présente deux limitations :

Pour exécuter des requêtes tokenisées (texte intégral) sur un champ, vous devez définir explicitement son type sur text. Le type déduit par défaut est keyword, qui ne prend pas en charge la tokenisation.
Si un objet JSON contient un tableau imbriqué d'objets, vous devez définir explicitement le type de champ sur nested.

Utilisez mapping pour définir la structure des champs internes. Les types de champs suivent la syntaxe Elasticsearch.

-- Explicitly define field types to enable tokenized queries and control indexing behavior
CREATE INDEX idx USING SEARCH ON test_json_object(
  user_info(mapping='{
    "type": "object",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      },
      "address": {
        "type": "text",
        "analyzer": "ik_max_word"
      },
      "hobbies": {
        "type": "keyword"
      }
    }
  }')
);

Pour indexer uniquement un sous-ensemble de champs et ignorer les autres, ajoutez "dynamic": "false" :

CREATE INDEX idx USING SEARCH ON test_json_object(
  user_info(mapping='{
    "type": "object",
    "dynamic": "false",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      },
      "address": {
        "type": "text",
        "analyzer": "ik_max_word"
      },
      "hobbies": {
        "type": "keyword"
      }
    }
  }')
);

Interroger

Trois méthodes de requête sont prises en charge pour les objets JSON :

JSON_EXTRACT / JSON_EXTRACT_STRING : correspondance d'un seul champ scalaire par chemin
JSON_CONTAINS / JSON_CONTAINS_ANY : correspondance d'éléments dans un champ tableau au sein de l'objet
MATCH...AGAINST combiné avec JSON_EXTRACT : recherche en texte intégral (tokenisée) sur un champ text

La correspondance d'un sous-objet complet n'est pas prise en charge. Par exemple, WHERE JSON_EXTRACT(json_col, '$.user') = '{"name": "Alice", "age": 12}' n'est pas valide. JSON_EXTRACT dans un index de recherche ne correspond qu'à des éléments scalaires uniques.

JSON_EXTRACT — trouver des lignes par la valeur d'un champ scalaire

-- Find users whose name is Alice
Lindorm> SELECT * FROM test_json_object WHERE JSON_EXTRACT_STRING(user_info, '$.name')='Alice';
+------+------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                      |
+------+------------------------------------------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang Province", "hobbies": ["play games", "read", "badminton"]} |
+------+------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Find users whose age is 21
Lindorm> SELECT * FROM test_json_object WHERE JSON_EXTRACT(user_info, '$.age')=21;
+------+--------------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                              |
+------+--------------------------------------------------------------------------------------------------------+
| 1003 | {"name": "John", "age": 21, "address": "Shenzhen, Guangdong Province", "hobbies": ["read", "badminton", "food"]}       |
+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MATCH...AGAINST avec JSON_EXTRACT — recherche en texte intégral sur un champ textuel

Le champ address doit être prédéfini comme type: text. Le type déduit par défaut (keyword) ne prend pas en charge les requêtes tokenisées.

-- Find users whose address contains "Zhejiang" (tokenized match across multiple words)
Lindorm> SELECT * FROM test_json_object WHERE MATCH(JSON_EXTRACT(user_info, '$.address')) AGAINST ('Zhejiang');
+------+---------------------------------------------------------------+
| id   | user_info                                                     |
+------+---------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang", "hobbies": ["play games", "read", "badminton"]}    |
| 1002 | {"name": "Bob", "age": 9, "address": "Ningbo, Zhejiang", "hobbies": ["play games"]}                              |
+------+---------------------------------------------------------------+
2 rows in set (0.03 sec)

-- Find users whose address contains "Hangzhou"
Lindorm> SELECT * FROM test_json_object WHERE MATCH(JSON_EXTRACT(user_info, '$.address')) AGAINST ('Hangzhou');
+------+---------------------------------------------------------------+
| id   | user_info                                                     |
+------+---------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang", "hobbies": ["play games", "read", "badminton"]}    |
+------+---------------------------------------------------------------+
1 row in set (0.01 sec)

JSON_CONTAINS — trouver des lignes où un champ tableau contient des valeurs spécifiques

-- Find users who have "read" in their hobbies list
Lindorm> SELECT * FROM test_json_object WHERE JSON_CONTAINS(user_info, '["read"]', '$.hobbies');
+------+---------------------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                                     |
+------+---------------------------------------------------------------------------------------------------------------+
| 1003 | {"name": "John", "age": 21, "address": "Shenzhen, Guangdong Province", "hobbies": ["read", "badminton", "food"]}              |
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang Province", "hobbies": ["play games", "read", "badminton"]}       |
+------+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

Tableau d'objets

Un tableau d'objets stocke un tableau au niveau supérieur où chaque élément est un objet JSON, comme [{"name": "Alice", "age": 12}, {"name": "Bob", "age": 20}].

Pourquoi un tableau d'objets nécessite un type d'index différent

Sans un type d'index spécialisé, une base de données aplatit un tableau d'objets en champs multivalues indépendants, perdant ainsi les associations entre les champs appartenant au même objet. Prenons l'exemple des deux lignes suivantes :

id	user
1002	`[{"name": "Alice", "age": 9}, {"name": "Bob", "age": 20}]`

Un index naïf (non imbriqué) stockerait les valeurs name et age sous forme de listes plates : name = [Alice, Bob] et age = [9, 20]. Une requête recherchant « Alice avec âge >= 10 » correspondrait incorrectement à cette ligne, car Alice et âge=20 coexistent dans les champs aplatis, même si l'âge réel d'Alice est 9.

Lindorm utilise type: nested en interne pour les index de tableaux d'objets. Chaque objet du tableau est indexé comme une unité isolée, ce qui préserve les associations de champs au sein du même objet. Par conséquent, seule la fonction SEARCH_QUERY avec DSL Elasticsearch intégré est prise en charge pour interroger ce modèle. La requête nested du DSL exprime correctement ces contraintes intra-objets.

Créer une table large

CREATE TABLE test_json_object_array(id VARCHAR, user JSON, primary key(id));

Insérer des données

UPSERT INTO test_json_object_array(id, user) VALUES ('1001', '[{"name": "Alice", "age": 12}]');
UPSERT INTO test_json_object_array(id, user) VALUES ('1002', '[{"name": "Alice", "age": 9},{"name": "Bob", "age": 20}]');
UPSERT INTO test_json_object_array(id, user) VALUES ('1003', '[{"name": "Alice", "age": 12},{"name": "Bob", "age": 20}]');

Créer un index de recherche

Deux approches sont possibles. Privilégiez autant que possible la structure prédéfinie.

Inférence automatique du type

Spécifiez type=jsonarray pour permettre au système de déduire les types de champs à partir de la première valeur écrite pour chaque objet du tableau. Le système ignore les données écrites ultérieurement qui ne correspondent pas aux types déduits.

CREATE INDEX idx USING SEARCH ON test_json_object_array(user(type=jsonarray));

Prédéfinir la structure des champs (recommandé)

L'inférence automatique du type présente les mêmes limitations que pour les objets JSON : les requêtes tokenisées nécessitent un type text explicite, et les tableaux imbriqués d'objets au sein d'un champ nécessitent un type nested explicite.

Utilisez mapping pour définir la structure des champs internes. Le type de niveau supérieur doit être nested. Les types de champs suivent la syntaxe Elasticsearch.

CREATE INDEX idx USING SEARCH ON test_json_object_array(
  user(mapping='{
    "type": "nested",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      }
    }
  }')
);

Pour indexer uniquement un sous-ensemble de champs et ignorer les autres, ajoutez "dynamic": "false" :

CREATE INDEX idx USING SEARCH ON test_json_object_array(
  user(mapping='{
    "type": "nested",
    "dynamic": "false",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      }
    }
  }')
);

Interroger

Interrogez les tableaux d'objets à l'aide de la fonction SEARCH_QUERY avec DSL Elasticsearch intégré. Utilisez une requête nested avec le paramètre path défini sur le nom de la colonne.

-- Find rows where a single user object has name "Alice" AND age >= 10
-- (preserves field associations within each object)
Lindorm> SELECT * FROM test_json_object_array WHERE SEARCH_QUERY('
  {
    "nested": {
      "path": "user",
      "query": {
        "bool": {
          "must": [
            { "match": { "user.name": "Alice" } },
            { "range": { "user.age": {"gte": 10} } }
          ]
        }
      }
    }
  }
');
+------+-----------------------------------------------------------+
| id   | user                                                      |
+------+-----------------------------------------------------------+
| 1003 | [{"name": "Alice", "age": 12},{"name": "Bob", "age": 20}] |
| 1001 | [{"name": "Alice", "age": 12}]                            |
+------+-----------------------------------------------------------+

id=1002 est exclu car son entrée « Alice » a un age=9 , inférieur à 10. L'index imbriqué préserve l'association entre les champs au sein du même objet, de sorte qu'Alice et age=9 sont correctement évalués ensemble plutôt que mélangés avec l'âge de Bob (20).