多元索引嵌套类型

注意事项

要使用嵌套类型，在数据表中的列数据类型必须为字符串。创建多元索引时，该列的数据类型需要设置为嵌套类型，并且正确填写其子列的数据与数据类型。

数据类型映射

创建方法

如果要在SQL查询时使用嵌套类型，则必须创建多元索引映射关系。关于创建多元索引映射关系的具体操作，请参见创建多元索引的映射关系。

在CREATE TABLE语句中嵌套类型的列需要正确设置嵌套列名和对应的SQL数据类型即可，嵌套类型列的内部子列会自动创建。在多元索引映射关系时，推荐定义嵌套类型的列为MEDIUMTEXT类型。

创建包含嵌套类型列的多元索引映射关系，SQL示例如下：

CREATE TABLE `test_table__test_table_index`(
    `col_nested` MEDIUMTEXT
) 
ENGINE='searchindex'
ENGINE_ATTRIBUTE='{"index_name":"test_table_index", "table_name":"test_table"}';

通过SQL查询数据

通过SQL查询嵌套类型时，嵌套类型列的子列支持直接与运算符组合使用（例如`col_nested.col_long` = 1）或者使用NESTED_QUERY(subcol_column_condition)函数实现，请实际需要查询需求选择。其中subcol_column_condition为同一嵌套层级下的子列查询条件。

当查询条件中使用嵌套类型的列时，嵌套类型列下的子列列名格式为`嵌套列名.子列名`。如果嵌套类型为多层嵌套，则继续向后添加子列名即可，使用半角句号（.）进行连接。例如嵌套类型列名为col_nested，子列名为col_long，则该子列作为查询条件时设置为`col_nested.col_long`；假如嵌套类型列名为col1，该列有一个嵌套类型子列col2，col2列有一个子列col3，则col3子列作为查询条件时设置为`col1.col2.col3`。

当使用NESTED_QUERY(subcol_column_condition)函数作为查询条件时，该行同一个嵌套的JSON元素必须同时满足全部查询条件。

假设嵌套类型的列名为tags，其元素只有一行包括两个JSON元素，具体值为[{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]，使用不同方式查询嵌套类型的列数据时的区别如下：

• 嵌套类型的子列支持直接与运算符组合使用

  SQL示例如下：

  SELECT tags FROM `test_table__test_table_index` WHERE `tags.tagName` = 'tag1' AND `tags.score` = 0.2;

  该行的第一个JSON元素满足`tags.tagName` = 'tag1'，第二个JSON元素满足`tags.score` = 0.2，因此返回结果如下：

  [{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]

• 使用NESTED_QUERY(subcol_column_condition)函数实现

  SQL示例如下：

  SELECT tags FROM `test_table__test_table_index` WHERE NESTED_QUERY(`tags.tagName` = 'tag1' AND `tags.score` = 0.2);

  由于没有任何一个JSON元素同时满足`tags.tagName` = 'tag1'和`tags.score` = 0.2，因此返回结果为空。

使用限制

• NESTED_QUERY(subcol_column_condition)用于同一层级下的嵌套查询，并且需要该行同一个嵌套的JSON元素同时满足全部查询条件。SQL示例如下：

  SELECT * FROM `test_table__test_table_index` WHERE NESTED_QUERY(`col1.col2` = 1 AND NESTED_QUERY(`col1.col3.col4` = 2));

• 嵌套类型子列直接与运算符组合使用或者使用NESTED_QUERY(subcol_column_condition)函数作为嵌套查询条件时只能在多元索引映射关系上使用。

  NESTED_QUERY(subcol_column_condition)函数只能设置一个表达式类型的参数，多个表达式之间需要使用逻辑运算符（AND或OR）进行连接。该函数只能作为SELECT语句的WHERE子句，不能作为SELECT语句的列表达式，不能用于聚合函数计算，不能进行分组和排序。

• 嵌套类型列下子列不能作为SELECT语句的列名或者列表达式，且不能用于聚合函数计算，不能进行分组和排序。

• 使用ALTER TABLE语句添加或删除列时，不能直接添加或删除嵌套类型列下子列，只能添加或删除存在于数据表中的嵌套类型列，子列会被自动添加或删除。

• 嵌套子列不能进行数据类型转换后的计算，也不能对无法下推到多元索引的函数进行计算。使用时请确保嵌套子列对应的数据类型正确。

使用示例

假设数据表名称为test_table，该表中有col_nested（字符串类型）列。

如果要使用SQL语句查询嵌套类型的数据，则需要为数据表创建多元索引并创建多元索引的映射关系，然后使用SQL语句查询嵌套类型列的数据。具体步骤如下：

1. 创建一个多元索引。具体操作，请参见使用控制台创建多元索引或使用SDK创建多元索引。

   多元索引名称为test_table_index，该多元索引包括col_nested（嵌套类型）列。

   使用控制台创建多元索引的配置如下图所示。

   

2. 创建多元索引映射关系。更多信息，请参见创建多元索引的映射关系。

   多元索引映射关系名称为test_table__test_table_index，该映射关系中col_nested列对应的SQL数据类型为MEDIUMTEXT。

   SQL示例如下：

   CREATE TABLE `test_table__test_table_index`(
       `col_nested` MEDIUMTEXT
   ) 
   ENGINE='searchindex'
   ENGINE_ATTRIBUTE='{"index_name":"test_table_index", "table_name":"test_table"}';

   创建多元索引映射关系后，请根据需要执行如下操作：

   • 查询表的描述信息。更多信息，请参见查询表的描述信息。

     执行如下语句查询test_table__test_table_index的描述信息。

     DESCRIBE `test_table__test_table_index`;

     返回结果如下图所示。嵌套列col_nested下子列name和age均已自动创建，列名分别为col_nested.name和col_nested.age。

     

   • 获取表中数据。

     执行如下语句获取表中数据。

     SELECT * FROM `test_table__test_table_index`;

     返回结果如下图所示。假设多元索引映射关系test_table__test_table_index有5条数据。

     

3. 使用SELECT语句查询数据。

   • 示例一：查询col_long_array嵌套类型列下子列age大于30的行。

     SELECT * FROM `test_table__test_table_index` WHERE `col_nest.age` >30;

     返回结果如下图所示。

     

   • 示例二：查询col_long_array嵌套类型列下的JSON元素满足name以字母I开头，并且子列age小于20的行。

     SELECT * FROM `test_table__test_table_index` WHERE `col_nested.name` like 'I%' AND `col_nested.age` < 20;

     返回结果如下图所示。

     

   • 示例三：查询col_long_array嵌套类型列的某个JSON元素同时满足name以字母I开头，并且age小于20。

     此功能需要使用NESTED_QUERY(subcol_column_condition)函数来实现。

     SELECT * FROM `test_table__test_table_index` WHERE NESTED_QUERY(`col_nested.name` like 'I%' AND `col_nested.age` < 20);

     返回结果如下图所示。