STRUCT data type-MaxCompute(MaxCompute)-阿里云帮助中心

The STRUCT data type lets you group fields of different types into a single value. This topic covers the three construction methods and how to access fields after construction.

Choose a construction method

MaxCompute supports three ways to construct a STRUCT. Each differs in how field names are assigned:

Method	How field names are set	NULL support
STRUCT EXPRESSION	Specified by you (optional `AS field_name`)	Supported when BigQuery compatibility mode is enabled
STRUCT function	Always auto-generated as `col1`, `col2`, ...	Not supported
NAMED_STRUCT function	Specified by you as string constants	Not supported

When to use which:

Use STRUCT EXPRESSION when you need named fields, want to construct a STRUCT from all table columns (STRUCT(*)), or need NULL field values.
Use STRUCT function when field names do not matter and all values are non-NULL.
Use NAMED_STRUCT function when field names must be explicit string constants and all values are non-NULL.

Usage notes

All three methods support nesting. A STRUCT field can itself be an ARRAY or MAP.
By default, STRUCT fields do not accept NULL values. To use NULL as a field value, enable BigQuery compatibility mode and use the STRUCT EXPRESSION method:
```
SET odps.sql.bigquery.compatible=true;
```
This setting applies only to the STRUCT EXPRESSION method. The STRUCT function and NAMED_STRUCT function do not support NULL values regardless of this setting.

STRUCT EXPRESSION

STRUCT EXPRESSION constructs a STRUCT from an expression list. Field names are optional — assign them with AS field_name.

A unique capability of STRUCT EXPRESSION: pass STRUCT(*) to wrap all columns of a table into a single STRUCT value.

Syntax

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

Parameters:

Parameter	Required	Description
`expression`	Yes	Any valid expression: a column reference, constant, or computed expression.
`AS`	No	Keyword prefix before an optional field name.
`field_name`	No	The field name in the resulting STRUCT. Field names are case-insensitive and must be unique within the same expression.

Return value: STRUCT<field_name1:T1, field_name2:T2, ...>

Examples

Construct a STRUCT with named fields:

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}

Access a specific field:

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

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

Construct a STRUCT with NULL field values (requires 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

The STRUCT function constructs a STRUCT from a value list. MaxCompute auto-generates field names as col1, col2, and so on — you cannot assign custom names with this method. NULL values are not supported.

Syntax

STRUCT(value1, value2, ...)

Parameters: Each value can be of any type except NULL.

Return value: STRUCT<col1:T1, col2:T2, ...> — field names are always 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

The NAMED_STRUCT function constructs a STRUCT from alternating name/value pairs. All field names must be string constants. NULL values are not supported.

Syntax

NAMED_STRUCT(string_name1, value1, string_name2, value2, ...)

Parameters:

Parameter	Description
`string_name`	A string constant specifying the field name. Field names are case-insensitive.
`value`	The field value. Any type is supported except NULL.

Return value: STRUCT<string_name1:T1, string_name2:T2, ...>

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}

Access fields

Access a STRUCT field by appending .field_name to the STRUCT value or column reference.

struct_value.field_name

Example:

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

-- Result
2

Field names in access expressions are case-insensitive.