STRUCT Data Type Overview for Nested & Complex Fields - MaxCompute - Alibaba Cloud - MaxCompute

MaxCompute supports the STRUCT data type, which allows you to combine fields of different types into a logical unit. This topic describes how to use the STRUCT data type.

Construction methods

Differences of construction methods

MaxCompute supports three different methods to construct the STRUCT data type: STRUCT EXPRESSION, STRUCT function, and NAMED_STRUCT function.

Construction method	Example	Data type
STRUCT EXPRESSION Supports using a value list and a named expression list.	`STRUCT(1L AS a,'abc' AS b,null as c)`	`STRUCT<a:bigint,b:string,c:string>`
	`STRUCT(str_col AS abc) from t`	`STRUCT<abc:string>`
	`STRUCT() from t` Supports using an asterisk () to construct a STRUCT from all columns of a table.	`STRUCT<str_col:string,...>`
STRUCT function Supports using a value list.	`STRUCT(1L,2L,3L)`	`STRUCT<col1:bigint,col2:bigint,col3:bigint>`
	`STRUCT('abc')`	`STRUCT<col1:string>`
	`STRUCT(1L,t.str_col) from t`	`STRUCT<col1:int,col2:string>`
NAMED_STRUCT function Supports using a name/value list.	`NAMED_STRUCT('a',1L,'b','b_','c',3.50)`	`STRUCT<a:bigint,b:string,c:double>`
NAMED_STRUCT function Supports using a name/value list.	`NAMED_STRUCT('a', str_col...) from t`	`STRUCT<a:string,...>`

The difference between STRUCT EXPRESSION and the STRUCT function: The STRUCT function does not support specifying an alias for each parameter. When constructing a STRUCT, the system automatically adds field names in the order of parameters as col1, col2, and so on.

Usage note

Different construction syntaxes support nesting and can be combined with construction functions of other complex types such as ARRAY and MAP.
The STRUCT data type does not support NULL as a field value by default. If you need to use NULL values, you must enable the Bigquery compatibility mode by using SET odps.sql.bigquery.compatible=true;, and you can only use the STRUCT EXPRESSION method for construction.

STRUCT EXPRESSION

STRUCT EXPRESSION provides a method to construct the complex STRUCT type. It supports using a value list and a named expression list when you construct a STRUCT.

Syntax

STRUCT(expression1 [[AS] field_name1], expression2 [[AS] field_name2], ...)

Parameters

Parameter	Required	Description
expression	Yes	This parameter can be any expression and data type. Column names, constants, and expressions are supported.
as	No	The prefix keyword for field names in the STRUCT expression.
field_name	No	The field name in the generated STRUCT. Field names are case-insensitive. Field names within the same STRUCT expression cannot be duplicated.

Return value: Returns a STRUCT type, STRUCT <field_name1:T1, field_name2:T2, ...>, where the field names are field_name1, field_name2, and so on.

Examples

Use non-NULL values as field values.

SELECT * FROM VALUES (STRUCT (1 AS a, 2 AS b, 3 AS c, 4 AS d)) AS tbl1(struct1);

-- Result
{a:1, b:2, c:3, d:4}

SELECT struct1.b FROM VALUES (STRUCT (1 AS a, 2 AS b, 3 AS c, 4 AS d)) AS tbl1(struct1);

-- Result
+------------+
| b          |
+------------+
| 2          |
+------------+

Use NULL values as field values. You need to run the SET odps.sql.bigquery.compatible=true; command to enable the Bigquery compatibility mode.
```
SET odps.sql.bigquery.compatible=true;
SELECT STRUCT (NULL AS Col_1, 2 AS Col_2);

-- Result
 {col_1:NULL, col_2:2} 
```

STRUCT function

MaxCompute supports using the STRUCT function to create a STRUCT data type from a given value list.

Syntax

STRUCT STRUCT(value1,value2, ...)

Parameters: The value can be of any type, but NULL is not supported.
Return value: Returns a STRUCT type, STRUCT<col1:T1, col2:T2,...>, where the field names are col1, col2, and so on.

Examples

SELECT STRUCT('a',123,'true',56.90);

-- Result
{col1:a, col2:123, col3:true, col4:56.9}

NAMED_STRUCT function

MaxCompute supports using the NAMED_STRUCT function to construct a STRUCT data type from a given name/value list.

Syntax

STRUCT NAMED_STRUCT(string_name1, T1 value1, string_name2, T2 value2,...)

Parameters:
- string_name: The specified field name of the STRING type, which must be a constant. Field names are case-insensitive.
- value: The value can be of any type, but NULL is not supported.
Return value: Returns a STRUCT type, STRUCT<string_name1:T1, string_name2:T2, ...>, where the field names are string_name1, string_name2, and so on.

Examples

SELECT NAMED_STRUCT('user_id',10001,'user_name','LiLei','married','F','weight',63.50);

-- Result
{user_id:10001, user_name:LiLei, married:F, weight:63.5}