How to query data of the NESTED type supported in search indexes by executing SQL statements - Tablestore

Search indexes support the NESTED field type in addition to basic field types such as LONG, DOUBLE, BOOLEAN, KEYWORD, and TEXT. NESTED indicates nested documents. Nested documents are used when a row of data (document) contains multiple child rows (child documents). Multiple child rows are stored in a NESTED field. This topic describes how to query data of NESTED fields by using the SQL query feature.

Note

For more information about the NESTED type, see ARRAY and Nested field types.

Usage notes

If you want to query the data of a NESTED field, the data of the NESTED field in the relevant table must be of the STRING type. When you create a search index for a field, you must set the data type of the index field to NESTED and correctly specify the data and data types for the child fields of the index field.

Data type mappings

Data type in tables	Data type in search indexes	Data type in SQL
STRING	NESTED. The data type of a child field is the same as the type of the data that is written to the child field.	VARCHAR (primary key) MEDIUMTEXT (predefined column)

Create a mapping table for a search index

If you want to query the data of a NESTED field by executing SQL statements, you must create a mapping table for the search index. For more information about how to create a mapping table for a search index, see Create mapping tables for search indexes.

In the CREATE TABLE statement, you must specify a valid name and the mapped data type in SQL for the NESTED field. Child fields of the NESTED field are automatically created. We recommend that you specify MEDIUMTEXT as the data type of the NESTED field in SQL when you create a mapping table for the search index.

Note

When you execute anALTER TABLE statement to create or delete a NESTED field, the child fields of the NESTED field are automatically created or deleted.

The following sample SQL statement provides an example on how to create a mapping table for a search index that contains a NESTED field.

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

Query data by executing SQL statements

When you execute SQL statements to query the data of a NESTED field, you can directly use the child fields of the NESTED field together with operators to specify query conditions, such as col_nested.col_long= 1. You can also use the NESTED_QUERY(subcol_column_condition) function to specify query conditions. In actual scenarios, select one of the preceding methods based on your query requirements. subcol_column_condition in the function indicates the condition for querying child fields at the same level.

When you use a NESTED field to specify query conditions, specify the names of child fields of the NESTED field in the format of 'NESTED field name.Child field name'. If the NESTED field contains multi-level fields, you can add the name of a child field after its previous child field name for data query. Separate child field names with periods (.). For example, if a NESTED field is named col_nested and a child field of the NESTED field is named col_long, the child field is named 'col_nested.col_long' in the query condition. If a NESTED field is named col1, the col1 NESTED field has a child field named col2, and the col2 child field has a child field named col3, the col3 child field is named 'col1.col2.col3' in the query condition.

If you use the NESTED_QUERY(subcol_column_condition) function to specify a query condition, the JSON elements of a child field in a row must meet all the query conditions.

In this example, a NESTED field is named tags and only one row of the NESTED field contains two JSON elements: [{"tagName":"tag1", "score":0.8} and {"tagName":"tag2", "score":0.2}]. The following sample statements describe the differences between the two methods used to query the data of the NESTED field.

Use the child fields of the NESTED field together with operators
Sample SQL statement:
```
SELECT tags FROM `test_table__test_table_index` WHERE `tags.tagName` = 'tag1' AND `tags.score` = 0.2;
```
The first JSON element of the row meets the 'tags.tagName' = 'tag1' query condition and the second JSON element of the row meets the 'tags.score' = 0.2 query condition. Therefore, the following result is returned:
```
[{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]
```
Use the NESTED_QUERY(subcol_column_condition) function
Sample SQL statement:
```
SELECT tags FROM `test_table__test_table_index` WHERE NESTED_QUERY(`tags.tagName` = 'tag1' AND `tags.score` = 0.2);
```
No JSON elements meet both the 'tags.tagName' = 'tag1' and the 'tags.score' = 0.2 query conditions. Therefore, no result is returned.

Limits

You can use the NESTED_QUERY(subcol_column_condition) function to query child fields of a NESTED field at the same level. The JSON elements to be queried in the row of the NESTED field must meet all query conditions. Sample SQL statement:
```
SELECT * FROM `test_table__test_table_index` WHERE NESTED_QUERY(`col1.col2` = 1 AND NESTED_QUERY(`col1.col3.col4` = 2));
```
You can use the child fields of a NESTED field together with operators or the NESTED_QUERY(subcol_column_condition) function only in the mapping table of a search index.
You can specify only one expression by using the NESTED_QUERY(subcol_column_condition) function. You must use logical operators such as AND or OR to connect multiple expressions. This function can be used only as the WHERE clause of a SELECT statement, but cannot be used as a column expression of a SELECT statement, as an aggregate function, or for sorting.
A NESTED field cannot be used as the field name or column expression in a SELECT statement or an aggregate function. A NESTED field cannot be used for grouping or sorting.
When you use an ALTER TABLE statement to create or delete a NESTED field, you cannot directly create or delete the child fields of the NESTED field. You can only create or delete the NESTED fields that exist in a table. The child fields of the NESTED fields are automatically created or deleted.
The data of the child fields of a NESTED field cannot be calculated after the data type is converted, or pushed down to the functions in the search index for calculation. Make sure that the data type of the child fields of a NESTED field is valid when you query data of the child fields of the NESTED field.

Example

In the following example, the table whose data is queried is named test_table and the table contains the col_nested field of the STRING type.

If you want to execute SQL statements to query data of the NESTED field in the table, you must create a search index for the table and create a mapping table for the search index. To do so, perform the following steps:

Create a search index. For more information, see Use the Tablestore console or Use Tablestore SDKs.
The search index is named test_table_index. The search index contains the col_nested field of the NESTED type.
The following figure shows how to create a search index by using the Tablestore console.
Create a mapping table for the search index. For more information, see Create mapping tables for search indexes.
The mapping table of the search index is named test_table__test_table_index, and the SQL data type of the col_nested field in the mapping table is MEDIUMTEXT.
Sample SQL statement:
```
CREATE TABLE `test_table__test_table_index`(
    `col_nested` MEDIUMTEXT
) 
ENGINE='searchindex'
ENGINE_ATTRIBUTE='{"index_name":"test_table_index", "table_name":"test_table"}';
```
After you create a mapping table for test_table_index, perform the following operations based on your business requirements:
- Query the description of the table. For more information, see Query the information about a table.
  Execute the following statement to query the description of test_table__test_table_index:
```
DESCRIBE `test_table__test_table_index`;
```
  The following figure shows the query result. The child fields name and age of the NESTED field col_nested are automatically created. The name child field is named col_nested.name and the age child field is named col_nested.age.
- Query data from the table.
  Execute the following statement to query data from test_table__test_table_index:
```
SELECT * FROM `test_table__test_table_index`;
```
  The following figure shows the query result. In this example, five data records are contained in the test_table__test_table_index mapping table.
Execute the SELECT statement to query data.
- Example 1: From col_long_array, query the rows with the array element that contains age whose value is greater than 30.
```
SELECT * FROM `test_table__test_table_index` WHERE `col_nest.age` >30;
```
  The following figure shows the query result.
- Example 2: From col_long_array, query the rows in which one or more JSON elements contain name whose value start with I and age whose value is less than 20.
```
SELECT * FROM `test_table__test_table_index` WHERE `col_nested.name` like 'I%' AND `col_nested.age` < 20;
```
  The following figure shows the query result.
- Example 3: From col_long_array, query the JSON elements that contain name whose value start with I and age whose value is less than 20.
  In this example, the NESTED_QUERY(subcol_column_condition) function is required.
```
SELECT * FROM `test_table__test_table_index` WHERE NESTED_QUERY(`col_nested.name` like 'I%' AND `col_nested.age` < 20);
```
  The following figure shows the query result.