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

Lindorm permite crear índices de búsqueda en columnas JSON de tablas anchas. Las funciones de consulta disponibles dependen de la estructura de sus datos JSON. Esta guía abarca los tres patrones JSON compatibles: arreglos de elementos básicos, objetos JSON y arreglos de objetos, e indica qué tipo de índice y qué funciones de consulta corresponden a cada uno.

Requisitos previos

Antes de empezar, asegúrese de que:

El motor LindormTable tenga la versión 2.8.5 o posterior
El motor Lindorm LTS tenga la versión 3.8.13.3 o posterior

Para comprobar o actualizar la versión de su motor, consulte Minor version update.

Notas de uso

Al utilizar la inference automática de tipos JSON, si el tipo de datos de un campo JSON varía entre escrituras (por ejemplo, el campo a se escribe primero como cadena y después como número), se utiliza el tipo inferido en la primera escritura. Los valores posteriores que no coincidan con dicho tipo se omiten.
Si un documento JSON contiene muchos campos internos, la inference automática puede alcanzar el límite de columnas del índice de búsqueda y provocar fallos en las tareas de sincronización del índice. Se recomienda indexar únicamente los campos JSON que realmente necesite consultar (consulte el siguiente ejemplo para más detalles). El límite predeterminado de columnas es 1000. En LindormTable 2.8.6.1 y versiones posteriores, puede ajustar este límite con la siguiente sentencia SQL:
```
ALTER INDEX idx ON search_table SET SEARCH_INDEX_MAX_COLUMN_COUNT='2000';
```

Elegir el tipo de índice adecuado

Antes de crear un índice de búsqueda en una columna JSON, identifique cuál de los tres patrones siguen sus datos. Si se mezclan patrones en una sola columna, los datos no válidos se omiten silenciosamente durante la indexación.

Patrón JSON	Ejemplo	Tipo de índice	Funciones de consulta compatibles
Arreglo de elementos básicos	`[1, 2, 3]` o `["a", "b"]`	`mapping` con tipo escalar (`keyword`, `integer`, etc.)	`JSON_CONTAINS`, `JSON_CONTAINS_ANY`
Objeto JSON	`{"name": "Alice", "age": 13}`	`type=jsonobject` o `mapping` con `"type": "object"`	`JSON_EXTRACT`, `JSON_EXTRACT_STRING`, `JSON_CONTAINS`, `JSON_CONTAINS_ANY` (en arreglos anidados), `MATCH`...`AGAINST`
Arreglo de objetos	`[{"name": "Alice"}, {"name": "Bob"}]`	`type=jsonarray` o `mapping` con `"type": "nested"`	Solo `SEARCH_QUERY` con DSL de Elasticsearch

Importante

No mezcle los tres patrones en una sola columna. Los datos no válidos pueden omitirse y no indexarse.

Arreglo de elementos básicos

Un arreglo de elementos básicos almacena una lista plana de valores escalares en el nivel superior, como ["101", "102", "109"] (arreglo de cadenas) o [1, 2, 3] (arreglo de enteros).

Crear una tabla ancha

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

Insertar datos

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

Crear un índice de búsqueda

Especifique el tipo de elemento en el parámetro mapping. Utilice keyword para arreglos de cadenas y integer para arreglos de enteros.

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

Consultar

Hay dos funciones disponibles:

JSON_CONTAINS: devuelve las filas donde todos los valores especificados están presentes en el arreglo
JSON_CONTAINS_ANY: devuelve las filas donde está presente como mínimo uno de los valores especificados

JSON_CONTAINS — filas que contienen todos los valores especificados

-- 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 — filas que contienen al menos uno de los valores especificados

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

Objeto JSON

Un objeto JSON almacena un documento estructurado en el nivel superior, como {"name": "Alice", "age": 13, "hobbies": ["read", "badminton"]}. Los campos pueden ser valores escalares, arreglos u objetos anidados.

Crear una tabla ancha

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

Insertar datos

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"]}');

Crear un índice de búsqueda

Existen dos enfoques. Utilice la estructura predefinida siempre que sea posible.

Inference automática de tipos

Especifique type=jsonobject para permitir que el sistema infiera los tipos de campo a partir del primer valor escrito. Por ejemplo, user_info.name se infiere como cadena y user_info.age como numérico. Los datos escritos posteriormente que no coincidan con los tipos inferidos se omiten para preservar la consistencia del índice.

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

Predefinir la estructura de campos (recomendado)

La inference automática de tipos presenta dos limitaciones:

Para ejecutar consultas tokenizadas (texto completo) en un campo, el tipo de campo debe establecerse explícitamente como text. El tipo inferido predeterminado es keyword, que no admite tokenización.
Si un objeto JSON contiene un arreglo anidado de objetos, el tipo de campo debe establecerse explícitamente como nested.

Utilice mapping para definir la estructura interna de los campos. Los tipos de campo siguen la sintaxis de 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"
      }
    }
  }')
);

Para indexar solo un subconjunto de campos e ignorar el resto, agregue "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"
      }
    }
  }')
);

Consultar

Se admiten tres métodos de consulta para objetos JSON:

JSON_EXTRACT / JSON_EXTRACT_STRING — coinciden con un solo campo escalar mediante su ruta
JSON_CONTAINS / JSON_CONTAINS_ANY — coinciden con elementos en un campo de arreglo dentro del objeto
MATCH...AGAINST combinado con JSON_EXTRACT — búsqueda de texto completo (tokenizada) en un campo text

No se admite la coincidencia con un subobjeto completo. Por ejemplo, WHERE JSON_EXTRACT(json_col, '$.user') = '{"name": "Alice", "age": 12}' no es válido. JSON_EXTRACT en un índice de búsqueda solo coincide con elementos escalares individuales.

JSON_EXTRACT — buscar filas por el valor de un campo escalar

-- 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 con JSON_EXTRACT — búsqueda de texto completo en un campo de texto

El campo address debe predefinirse como type: text. El tipo inferido predeterminado (keyword) no admite consultas tokenizadas.

-- 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 — buscar filas donde un campo de arreglo contenga valores específicos

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

Arreglo de objetos

Un arreglo de objetos almacena un arreglo de nivel superior donde cada elemento es un objeto JSON, como [{"name": "Alice", "age": 12}, {"name": "Bob", "age": 20}].

Por qué un arreglo de objetos requiere un tipo de índice diferente

Sin un tipo de índice especializado, una base de datos aplana un arreglo de objetos en campos independientes de múltiples valores, lo que provoca la pérdida de las asociaciones entre campos que pertenecen al mismo objeto. Por ejemplo, dadas las siguientes dos filas:

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

Un índice simple (no anidado) almacenaría los valores de name y age como listas planas: name = [Alice, Bob] y age = [9, 20]. Una consulta de "Alice con edad >= 10" coincidiría incorrectamente con esta fila porque Alice y age=20 coexisten en los campos aplanados, aunque la edad real de Alice sea 9.

Lindorm utiliza internamente type: nested para los índices de arreglos de objetos. Cada objeto del arreglo se indexa como una unidad aislada, por lo que se preservan las asociaciones de campos dentro del mismo objeto. En consecuencia, solo se admite SEARCH_QUERY con DSL de Elasticsearch integrado para consultar este patrón, ya que la consulta nested del DSL expresa correctamente estas restricciones intraobjeto.

Crear una tabla ancha

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

Insertar datos

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}]');

Crear un índice de búsqueda

Existen dos enfoques. Utilice la estructura predefinida siempre que sea posible.

Inference automática de tipos

Especifique type=jsonarray para permitir que el sistema infiera los tipos de campo a partir del primer valor escrito para cada objeto del arreglo. Los datos escritos posteriormente que no coincidan con los tipos inferidos se omiten.

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

Predefinir la estructura de campos (recomendado)

La inference automática de tipos tiene las mismas limitaciones que para los objetos JSON: las consultas tokenizadas requieren el tipo text explícito, y los arreglos anidados de objetos dentro de un campo requieren el tipo nested explícito.

Utilice mapping para definir la estructura interna de los campos. El tipo de nivel superior debe ser nested. Los tipos de campo siguen la sintaxis de Elasticsearch.

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

Para indexar solo un subconjunto de campos e ignorar el resto, agregue "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"
      }
    }
  }')
);

Consultar

Consulte arreglos de objetos mediante la función SEARCH_QUERY con DSL de Elasticsearch integrado. Utilice una consulta nested con el parámetro path establecido en el nombre de la columna.

-- 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 se excluye porque su entrada "Alice" tiene age=9 , que es menor que 10. El índice anidado preserva la asociación entre campos dentro del mismo objeto, por lo que Alice y age=9 se evalúan correctamente juntos en lugar de mezclarse con la edad de Bob, que es 20.