Cast to STRUCT Types

STRUCT type is used to store and process structured data, which can contain fields of different types, each with a name and corresponding value. STRUCT can nest other complex types such as ARRAY, MAP, or other STRUCTs.

Cast to STRUCT

FROM String

Strict Mode

BNF Definition

<struct>          ::= "{" <struct-content>? "}" | <empty-struct> 

<empty-struct> ::= "{}"

<struct-content>  ::=  <struct-field-value-content> | <struct-only-value-content>

<struct-field-value-content> ::=  <field-token> <map_key_delimiter> <value-token>
                     (<collection-delim> <field-token> <map_key_delimiter> <value-token>)*
                         
<struct-only-value-content> ::=  <value-token>(<collection-delim> <value-token>)*

<value-token>    ::= <whitespace>* "\"" <inner-sequence> "\"" <whitespace>*
                   | <whitespace>* "'" <inner-sequence> "'" <whitespace>*
                   | <whitespace>* <inner-sequence> <whitespace>*

<inner-sequence>    ::= .*
<collection-delim>  ::= "," 
<map_key_delimiter> ::= ":"

Rule Description

The textual representation of a STRUCT must begin with a left brace { and end with a right brace }.
Empty STRUCTs are directly represented as {}.
Field-value pairs within the STRUCT are separated by commas ,.
Each field-value pair consists of an optional field name, a colon :, and a value, in the order "fieldname:value" or simply "value".
Field-value pairs must either all use the "fieldname:value" format or all use the "value" format.
Field names and values can optionally be enclosed in matching single quotes (') or double quotes ("). The content inside the quotes is treated as a single entity.
Whitespace is allowed before and after elements within the STRUCT.
During parsing, parts that match <value-token> continue to apply the parsing rules of the value type. How <field-token> parts are handled depends on whether field names are provided:
- When field names are provided: fields are matched by name, case-insensitively. The order of the input fields does not need to match the order defined in the STRUCT. Fields defined in the STRUCT but missing from the input are filled with NULL. In strict mode, an input field name that does not exist in the STRUCT causes an error.
- When no field names are provided: fields are matched by position, and the number of values must be exactly equal to the number of fields defined in the STRUCT.
Elements can use "null" to represent a null value.

If the STRUCT format does not meet the requirements, an error is reported, for example:

When no field names are provided, the number of values is not equal to the number of fields defined in the STRUCT.
When field names are provided, the input contains a field name that does not exist in the STRUCT.
Some field-value pairs have field names while others do not (they must either all have field names or none have field names).

If a value in the STRUCT does not meet the requirements of the corresponding type, an error is reported.

Examples

Input String	Conversion Result	Comment
"{}"	{}	Valid empty STRUCT
" {}"	Error	Does not start with a brace, parsing fails
'{"a":1,"b":1}'	Cast to STRUCT<a:int, b:int>: {"a":1, "b":1}	Valid STRUCT with field names
'{a:1,"b":3.14}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":3.14}	Field names can be quoted or unquoted
'{1,3.14}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":3.14}	No field names provided, matched by position
'{b:3.14,a:1}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":3.14}	When field names are provided, fields are matched by name; the input order may differ from the schema
'{A:1,B:3.14}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":3.14}	Field names are matched case-insensitively
'{a:1}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":null}	Field b is missing from the input and is filled with NULL
'{a:1,c:100}'	Cast to STRUCT<a:int, b:double>: Error	Field name c does not exist in the STRUCT; strict mode reports an error
'{a:1,3.1,c:100}'	Error	Mixed format with some having field names and others not
'{1,3.14,100}'	Cast to STRUCT<a:int, b:double>: Error	When no field names are provided, the value count does not match the number of fields defined
'{"a":"abc","b":1}'	Cast to STRUCT<a:int, b:int>: Error	"abc" cannot be converted to int type
'{null,1}'	Cast to STRUCT<a:int, b:int>: {"a":null, "b":1}	Valid STRUCT with null value
'{"name":"John","age":25}'	Cast to STRUCT<name:string, age:int>: {"name":"John", "age":25}	STRUCT with string values
'{{"x":1,"y":2},3}'	Cast to STRUCT<point:struct<x:int,y:int>, z:int>: {"point":{"x":1,"y":2}, "z":3}	Nested STRUCT structure

Non-Strict Mode

BNF Definition

<struct>          ::= "{" <struct-content>? "}" | <empty-struct> 

<empty-struct> ::= "{}"

<struct-content>  ::=  <struct-field-value-content> | <struct-only-value-content>

<struct-field-value-content> ::=  <field-token> <map_key_delimiter> <value-token>
                     (<collection-delim> <field-token> <map_key_delimiter> <value-token>)*
                         
<struct-only-value-content> ::=  <value-token>(<collection-delim> <value-token>)*

<value-token>    ::= <whitespace>* "\"" <inner-sequence> "\"" <whitespace>*
                   | <whitespace>* "'" <inner-sequence> "'" <whitespace>*
                   | <whitespace>* <inner-sequence> <whitespace>*

<inner-sequence>    ::= .*
<collection-delim>  ::= "," 
<map_key_delimiter> ::= ":"

Rule Description

The textual representation of a STRUCT must begin with a left brace { and end with a right brace }.
Empty STRUCTs are directly represented as {}.
Field-value pairs within the STRUCT are separated by commas ,.
Each field-value pair consists of an optional field name, a colon :, and a value, in the order "fieldname:value" or simply "value".
Field-value pairs must either all use the "fieldname:value" format or all use the "value" format.
Field names and values can optionally be enclosed in matching single quotes (') or double quotes ("). The content inside the quotes is treated as a single entity.
Whitespace is allowed before and after elements within the STRUCT.
During parsing, parts that match <value-token> continue to apply the parsing rules of the value type. How <field-token> parts are handled depends on whether field names are provided:
- When field names are provided: fields are matched by name, case-insensitively. The order of the input fields does not need to match the order defined in the STRUCT. Fields defined in the STRUCT but missing from the input are filled with NULL. An input field name that does not exist in the STRUCT is ignored.
- When no field names are provided: fields are matched by position, and the number of values must be exactly equal to the number of fields defined in the STRUCT.
Elements can use "null" to represent a null value.

If the STRUCT format does not meet the requirements, NULL is returned, for example:

When no field names are provided, the number of values is not equal to the number of fields defined in the STRUCT.
Some field-value pairs have field names while others do not (they must either all have field names or none have field names).

If a value in the STRUCT does not meet the requirements of the corresponding type, the corresponding position is set to null.

Examples

Input String	Conversion Result	Comment
"{}"	{}	Valid empty STRUCT
" {}"	NULL	Does not start with a brace, parsing fails
'{"a":1,"b":1}'	Cast to STRUCT<a:int, b:int>: {"a":1, "b":1}	Valid STRUCT with field names
'{a:1,"b":3.14}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":3.14}	Field names can be quoted or unquoted
'{1,3.14}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":3.14}	No field names provided, matched by position
'{b:3.14,a:1}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":3.14}	When field names are provided, fields are matched by name; the input order may differ from the schema
'{A:1,B:3.14}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":3.14}	Field names are matched case-insensitively
'{a:1}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":null}	Field b is missing from the input and is filled with NULL
'{a:1,c:100}'	Cast to STRUCT<a:int, b:double>: {"a":1, "b":null}	Unknown field c is ignored; missing field b is filled with NULL
'{a:1,3.1,c:100}'	NULL	Mixed format with some having field names and others not
'{1,3.14,100}'	Cast to STRUCT<a:int, b:double>: NULL	When no field names are provided, the value count does not match the number of fields defined
'{"a":"abc","b":1}'	Cast to STRUCT<a:int, b:int>: {"a":null, "b":1}	"abc" cannot be converted to int type, position set to null
'{null,1}'	Cast to STRUCT<a:int, b:int>: {"a":null, "b":1}	Valid STRUCT with null value
'{"name":"John","age":"twenty-five"}'	Cast to STRUCT<name:string, age:int>: {"name":"John", "age":null}	"twenty-five" cannot be converted to int type, position set to null
'{{"x":"one","y":2},3}'	Cast to STRUCT<point:struct<x:int,y:int>, z:int>: {"point":{"x":null,"y":2}, "z":3}	In nested STRUCT, failed conversion results in null

FROM STRUCT<Other Type>

When the source data is of STRUCT type and the target is also of STRUCT type, the following conditions must be met:

The source STRUCT and target STRUCT must have the same number of elements (fields)
Each element in the source STRUCT will be converted to the corresponding element type in the target STRUCT in sequence

If the above conditions are not met, such as when the number of elements doesn't match, the conversion will not be possible.

Strict Mode

Rule Description

For each element in the STRUCT, a Cast from Other Type To Type is performed. The Cast is also in strict mode.

Examples

-- Create a simple STRUCT type variable
mysql> SELECT named_struct('a', 123, 'b', 'abc') AS original_struct;
+----------------------+
| original_struct      |
+----------------------+
| {"a":123, "b":"abc"} |
+----------------------+
-- Result: {"a":123,"b":"abc"} Type: struct<a:tinyint,b:varchar(3)>

-- Normal CAST
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<c:bigint, d:string>) AS renamed_struct;
+----------------------+
| renamed_struct       |
+----------------------+
| {"c":123, "d":"abc"} |
+----------------------+

-- Fields count doesn't match
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<c:bigint, d:string,e:char>) AS renamed_struct;
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from ...

mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<c:bigint>) AS renamed_struct;
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from ...

-- Element in STRUCT doesn't have a corresponding CAST
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<b:Array<int>, a:int>) AS renamed_struct;
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from ...

-- CAST is based on the defined order, not field names
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<b:string, a:int>) AS renamed_struct;
+------------------------+
| renamed_struct         |
+------------------------+
| {"b":"123", "a":"abc"} |
+------------------------+

-- Element CAST fails, the whole CAST reports an error
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<b:string, a:int>) AS renamed_struct;
ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]parse number fail, string: 'abc'

Non-Strict Mode

Rule Description

For each element in the STRUCT, a Cast from Other Type To Type is performed. The Cast is also in non-strict mode.

Examples

-- Create a simple STRUCT type variable
mysql> SELECT named_struct('a', 123, 'b', 'abc') AS original_struct;
+----------------------+
| original_struct      |
+----------------------+
| {"a":123, "b":"abc"} |
+----------------------+
-- Result: {"a":123,"b":"abc"} Type: struct<a:tinyint,b:varchar(3)>

-- Normal CAST
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<c:bigint, d:string>) AS renamed_struct;
+----------------------+
| renamed_struct       |
+----------------------+
| {"c":123, "d":"abc"} |
+----------------------+

-- Fields count doesn't match
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<c:bigint, d:string,e:char>) AS renamed_struct;
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from ...

mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<c:bigint>) AS renamed_struct;
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from ...

-- Element in STRUCT doesn't have a corresponding CAST
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<b:Array<int>, a:int>) AS renamed_struct;
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from ...

-- CAST is based on the defined order, not field names
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<b:string, a:int>) AS renamed_struct;
+------------------------+
| renamed_struct         |
+------------------------+
| {"b":"123", "a":"abc"} |
+------------------------+

-- Element CAST fails, the corresponding element is set to null
mysql> SELECT CAST(named_struct('a', 123, 'b', 'abc') AS STRUCT<b:string, a:int>) AS renamed_struct;
+-----------------------+
| renamed_struct        |
+-----------------------+
| {"b":"123", "a":null} |
+-----------------------+

Cast to STRUCT​

FROM String​

Strict Mode​

BNF Definition​

Rule Description​

Examples​

Non-Strict Mode​

BNF Definition​

Rule Description​

Examples​

FROM STRUCT<Other Type>​

Strict Mode​

Rule Description​

Examples​

Non-Strict Mode​

Rule Description​

Examples​

Cast to STRUCT

FROM String

Strict Mode

BNF Definition

Rule Description

Examples

Non-Strict Mode

BNF Definition

Rule Description

Examples

FROM STRUCT<Other Type>

Strict Mode

Rule Description

Examples

Non-Strict Mode

Rule Description

Examples