STRUCT
Type Descriptionβ
The STRUCT type is used to combine multiple fields into a single structure, where each field can have its own name and type, suitable for representing nested or complex business data structures.
-
STRUCT<field_name:field_type [COMMENT 'comment_string'], ... >
-
field_name
represents the name, cannot be empty, cannot be duplicated, and is case-insensitive. -
field_type
represents the type, which is nullable and cannot be specified as NOT NULL. Supported types include:BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, CHAR, VARCHAR, STRING, IPV4, IPV6, ARRAY, MAP, STRUCT
. -
[COMMENT 'comment-string']
represents an optional comment.
-
Type Constraintsβ
-
The maximum nesting depth supported by the
STRUCT
type is 9. -
Conversion between
STRUCT
types depends on whether the internal types can be converted (names do not affect conversion).STRUCT
types cannot be converted to other types.- String types can be converted to
STRUCT
types (through parsing, returning NULL if parsing fails).
- String types can be converted to
-
In the
AGGREGATE
table model,STRUCT
types only supportREPLACE
andREPLACE_IF_NOT_NULL
. In any table model, they cannot be used as KEY columns, nor as partition or bucket columns. -
Columns of
STRUCT
type do not support comparison or arithmetic operations, do not supportORDER BY
andGROUP BY
operations, cannot be used asJOIN KEY
, and cannot be used inDELETE
statements. -
Columns of
STRUCT
type do not support creating any indexes.
Type Constructionβ
-
Use
STRUCT()
to construct a value of typeSTRUCT
, where the internal names start from col1.SELECT STRUCT(1, 'a', "abc");
+--------------------------------------+
| STRUCT(1, 'a', "abc") |
+--------------------------------------+
| {"col1":1, "col2":"a", "col3":"abc"} |
+--------------------------------------+ -
Use
NAMED_STRUCT()
to construct a specificSTRUCT
type value.SELECT NAMED_STRUCT("name", "Jack", "id", 1728923);
+---------------------------------------------+
| NAMED_STRUCT("name", "Jack", "id", 1728923) |
+---------------------------------------------+
| {"name":"Jack", "id":1728923} |
+---------------------------------------------+
Modifying Typeβ
-
Modification is allowed only when the subcolumn type of
STRUCT
isVARCHAR
.- Only allows changing the parameter of
VARCHAR
from smaller to larger, not the other way around.
CREATE TABLE struct_table (
`k` INT NOT NULL,
`struct_varchar` STRUCT<name: VARCHAR(10), age: INT>
) ENGINE=OLAP
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES (
"replication_num" = "1"
);
ALTER TABLE struct_table MODIFY COLUMN struct_varchar STRUCT<name: VARCHAR(20), age: INT>; - Only allows changing the parameter of
-
Subcolumns inside
STRUCT
type do not support deletion, but new subcolumns can be added at the end.
CREATE TABLE struct_table (
`k` INT NOT NULL,
`struct_varchar` STRUCT<name: VARCHAR(10), age: INT>
) ENGINE=OLAP
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES (
"replication_num" = "1"
);
-- Add a subcolumn at the end
ALTER TABLE struct_table MODIFY COLUMN struct_varchar STRUCT<name: VARCHAR(10), age: INT, id: INT>;
Element Accessβ
-
Use
STRUCT_ELEMENT(struct, k/field_name)
to access a specific subcolumn inside theSTRUCT
.-
k represents the position, starting from 1.
-
field_name
is the name of the subcolumn in theSTRUCT
.
SELECT STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
+----------------------------------------------------------------+
| STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1) |
+----------------------------------------------------------------+
| Jack |
+----------------------------------------------------------------+
SELECT STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), "id");
+-------------------------------------------------------------------+
| STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), "id") |
+-------------------------------------------------------------------+
| 1728923 |
+-------------------------------------------------------------------+ -
Examplesβ
-
Nested Complex Types
-- Create table
CREATE TABLE IF NOT EXISTS struct_table (
id INT,
struct_complex STRUCT<
basic_info: STRUCT<name: STRING, age: INT>,
contact: STRUCT<email: STRING, phone: STRING>,
preferences: STRUCT<tags: ARRAY<STRING>, settings: MAP<STRING, INT>>,
metadata: STRUCT<
created_at: DATETIME,
updated_at: DATETIME,
stats: STRUCT<views: INT, clicks: INT>
>
>
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
-- Insert
INSERT INTO struct_table VALUES (1, STRUCT(
STRUCT('John', 25),
STRUCT('john@example.com', '1234567890'),
STRUCT(['tag1', 'tag2'], MAP('setting1', 1, 'setting2', 2)),
STRUCT('2021-01-01 00:00:00', '2021-01-02 00:00:00', STRUCT(100, 50))
));
-- Query
SELECT STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'basic_info'), 'name') FROM struct_table ORDER BY id;
+----------------------------------------------------------------------+
| STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'basic_info'), 'name') |
+----------------------------------------------------------------------+
| John |
+----------------------------------------------------------------------+
SELECT STRUCT_ELEMENT(STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'metadata'), 'stats'), 'views') FROM struct_table ORDER BY id;
+----------------------------------------------------------------------------------------------+
| STRUCT_ELEMENT(STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'metadata'), 'stats'), 'views') |
+----------------------------------------------------------------------------------------------+
| 100 |
+----------------------------------------------------------------------------------------------+ -
Modifying Type
-- Create table
CREATE TABLE struct_table (
`k` INT NOT NULL,
`struct_varchar` STRUCT<name: VARCHAR(10), age: INT>
) ENGINE=OLAP
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES (
"replication_num" = "1"
);
-- Modify the type of the 'name' subcolumn
ALTER TABLE struct_table MODIFY COLUMN struct_varchar STRUCT<name: VARCHAR(20), age: INT>;
-- Check column types
DESC struct_table;
+----------------+----------------------------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------------------------------+------+-------+---------+-------+
| k | int | No | true | NULL | |
| struct_varchar | struct<name:varchar(20),age:int> | Yes | false | NULL | NONE |
+----------------+----------------------------------+------+-------+---------+-------+
-- Create table
CREATE TABLE struct_table (
`k` INT NOT NULL,
`struct_varchar` STRUCT<name: VARCHAR(10), age: INT>
) ENGINE=OLAP
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES (
"replication_num" = "1"
);
-- Add a subcolumn at the end
ALTER TABLE struct_table MODIFY COLUMN struct_varchar STRUCT<name: VARCHAR(10), age: INT, id: INT>;
-- Check column types
DESC struct_table;
+----------------+-----------------------------------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------------------------+------+-------+---------+-------+
| k | int | No | true | NULL | |
| struct_varchar | struct<name:varchar(10),age:int,id:int> | Yes | false | NULL | NONE |
+----------------+-----------------------------------------+------+-------+---------+-------+