全部产品
Search
文档中心

表格存储:多元索引嵌套类型

更新时间:Jun 20, 2023

多元索引除了提供Long、Double、Boolean、Keyword、Text等基本类型外,还提供了嵌套类型(Nested)。嵌套类型代表嵌套文档类型。嵌套文档是指对于一行数据(文档)可以包含多个子行(子文档),多个子行保存在一个嵌套类型字段中。本文介绍如何通过SQL查询功能查询嵌套类型的列数据。

说明

关于嵌套类型的更多信息,请参见嵌套类型

注意事项

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

数据类型映射

数据表中数据类型

多元索引中数据类型

SQL数据类型

字符串

嵌套类型,子列数据类型和实际写入的数据类型一致。

  • VARCHAR(主键)

  • MEDIUMTEXT(预定义列)

创建方法

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

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

说明

使用ALTER TABLE语句添加或删除嵌套类型列时,嵌套类型列的内部子列也会被自动添加或删除。

创建包含嵌套类型列的多元索引映射关系,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,该列有一个嵌套类型子列col2col2列有一个子列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(嵌套类型)列。

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

    image..png
  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下子列nameage均已自动创建,列名分别为col_nested.namecol_nested.age

      image..png
    • 获取表中数据。

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

      SELECT * FROM `test_table__test_table_index`;

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

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

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

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

      返回结果如下图所示。

      image..png
    • 示例二:查询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;

      返回结果如下图所示。

      image..png
    • 示例三:查询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);

      返回结果如下图所示。

      image..png