This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 0a43c0fc36a [doc](struct) update STRUCT field access for ELEMENT_AT,
remove STRUCT_ELEMENT (#3898)
0a43c0fc36a is described below
commit 0a43c0fc36accfee9ecfec3cf0670d3283a2b7cf
Author: Chenyang Sun <[email protected]>
AuthorDate: Mon Jun 8 10:51:06 2026 +0800
[doc](struct) update STRUCT field access for ELEMENT_AT, remove
STRUCT_ELEMENT (#3898)
Update the STRUCT field-access docs to match apache/doris#64027, which
merges
`struct_element` into `element_at`. `STRUCT_ELEMENT` is removed since
version
4.1.3; struct fields are now accessed via `ELEMENT_AT`, the subscript
operator,
or the dot operator — all equivalent:
- `ELEMENT_AT(s, k)` / `ELEMENT_AT(s, 'field_name')`
- `s[k]` / `s['field_name']`
- `s.field_name` (and nested `s.a.b`, for a STRUCT column)
## Versions
- [x] dev
- [x] 4.x
- [ ] 3.x
- [ ] 2.1 or older (not covered by version/language sync gate)
## Languages
- [x] Chinese
- [x] English
- [ ] Japanese candidate translation needed
## Docs Checklist
- [ ] Checked by AI
- [ ] Test Cases Built
- [ ] Updated required version and language counterparts, or explained
why not
- [ ] If only one language changed, confirmed whether source/translation
counterparts need sync
---------
Co-authored-by: Claude Opus 4.8 (1M context) <[email protected]>
---
.../sql-data-types/semi-structured/STRUCT.md | 76 ++++++++++++-------
.../struct-functions/struct-element.md | 20 ++++-
.../variant-functions/element-at.md | 29 +++++++-
.../sql-data-types/semi-structured/STRUCT.md | 85 ++++++++++++++--------
.../struct-functions/struct-element.md | 20 ++++-
.../variant-functions/element-at.md | 29 +++++++-
.../sql-data-types/semi-structured/STRUCT.md | 85 ++++++++++++++--------
.../struct-functions/struct-element.md | 20 ++++-
.../variant-functions/element-at.md | 29 +++++++-
.../sql-data-types/semi-structured/STRUCT.md | 76 ++++++++++++-------
.../struct-functions/struct-element.md | 20 ++++-
.../variant-functions/element-at.md | 29 +++++++-
12 files changed, 376 insertions(+), 142 deletions(-)
diff --git
a/docs/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
b/docs/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
index 1be44bd51cd..b3f8b335e65 100644
--- a/docs/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
+++ b/docs/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
@@ -100,28 +100,48 @@ The STRUCT type is used to combine multiple fields into a
single structure, wher
## Element Access
-- Use `STRUCT_ELEMENT(struct, k/field_name)` to access a specific subcolumn
inside the `STRUCT`.
+A specific subcolumn inside a `STRUCT` can be accessed in the following
equivalent ways:
- - k represents the position, starting from 1.
+- `ELEMENT_AT(struct, k/field_name)` function.
- - `field_name` is the name of the subcolumn in the `STRUCT`.
+- The subscript operator `struct[k]` / `struct['field_name']`.
+
+- The dot operator `struct_col.field_name` (only for a `STRUCT` column).
+
+Where `k` is the position (a constant starting from 1) and `field_name` is the
subcolumn name (a string constant). Field names are matched
**case-insensitively**. Accessing a non-existent field name or an out-of-bound
position reports an error.
+
+:::caution
+As no other database or query engine provides such a function,
`STRUCT_ELEMENT` has been removed since version 4.1.3. Use `ELEMENT_AT` or the
subscript / dot operators instead.
+:::
+
+- Use `ELEMENT_AT` or the subscript operator to access by position or field
name.
```SQL
- SELECT STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
+ SELECT ELEMENT_AT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
+
+ +------------------------------------------------------------+
+ | ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1) |
+ +------------------------------------------------------------+
+ | Jack |
+ +------------------------------------------------------------+
- +----------------------------------------------------------------+
- | STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1) |
- +----------------------------------------------------------------+
- | Jack |
- +----------------------------------------------------------------+
+ SELECT NAMED_STRUCT("name", "Jack", "id", 1728923)['id'];
- SELECT STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), "id");
+ +--------------------------------------------------+
+ | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
+ +--------------------------------------------------+
+ | 1728923 |
+ +--------------------------------------------------+
+ ```
+
+- For a `STRUCT` column, use the dot operator to access a subcolumn by name,
including nested access such as `s.a.b`.
+
+ ```SQL
+ -- Table with a STRUCT column: s STRUCT<a: INT, b: DOUBLE>
+ SELECT s.a, s.b FROM struct_table;
- +-------------------------------------------------------------------+
- | STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), "id") |
- +-------------------------------------------------------------------+
- | 1728923 |
- +-------------------------------------------------------------------+
+ -- Nested STRUCT column: s STRUCT<s: STRUCT<s: STRUCT<s: INT>>>
+ SELECT s.s.s.s FROM nested_struct_table;
```
## Examples
@@ -157,22 +177,22 @@ The STRUCT type is used to combine multiple fields into a
single structure, wher
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;
+ -- Query (use the dot operator for nested struct field access)
+ SELECT struct_complex.basic_info.name FROM struct_table ORDER BY id;
- +----------------------------------------------------------------------+
- | STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'basic_info'), 'name') |
- +----------------------------------------------------------------------+
- | John |
- +----------------------------------------------------------------------+
+ +--------------------------------+
+ | struct_complex.basic_info.name |
+ +--------------------------------+
+ | John |
+ +--------------------------------+
- SELECT STRUCT_ELEMENT(STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex,
'metadata'), 'stats'), 'views') FROM struct_table ORDER BY id;
+ SELECT struct_complex.metadata.stats.views FROM struct_table ORDER BY id;
-
+----------------------------------------------------------------------------------------------+
- | STRUCT_ELEMENT(STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'metadata'),
'stats'), 'views') |
-
+----------------------------------------------------------------------------------------------+
- |
100 |
-
+----------------------------------------------------------------------------------------------+
+ +-------------------------------------+
+ | struct_complex.metadata.stats.views |
+ +-------------------------------------+
+ | 100 |
+ +-------------------------------------+
```
- Modifying Type
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
b/docs/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
index ffaf65c9da2..72e8e718466 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
@@ -6,6 +6,10 @@
}
---
+:::caution
+As no other database or query engine provides such a function,
`STRUCT_ELEMENT` has been removed since version 4.1.3. Please use the
[`ELEMENT_AT`](../variant-functions/element-at.md) function instead (or the
equivalent subscript `s[k]` / `s['field_name']` and dot `s.field_name` syntax).
+:::
+
## Description
Returns a specific field within a struct data column. The function supports
accessing fields in a struct through field position (index) or field name.
@@ -33,9 +37,10 @@ Return value meaning:
## Usage
- Supports accessing by field position (index), index starts from 1
-- Supports accessing by field name, field name must match exactly
+- Supports accessing by field name; the field name is matched
**case-insensitively**
- The second parameter must be a constant (cannot be a column)
- The function is marked as AlwaysNullable, return value may be null
+- Since version 4.1.3, `STRUCT_ELEMENT` is removed. Use `ELEMENT_AT(<struct>,
...)`, the subscript operators `<struct>[<index>]` /
`<struct>['<field_name>']`, or the dot operator `<struct_col>.<field_name>`
instead — these are all equivalent ways to access a struct field.
## Examples
@@ -61,6 +66,17 @@ select struct_element(named_struct('name', 'Alice', 'age',
25, 'city', 'Beijing'
+------------------------------------------------------------------------------------+
```
+Access using the subscript operator (equivalent to the calls above):
+```sql
+select named_struct('name', 'Alice', 'age', 25, 'city', 'Beijing')[1] as
by_index,
+ named_struct('name', 'Alice', 'age', 25, 'city', 'Beijing')['age'] as
by_name;
++----------+---------+
+| by_index | by_name |
++----------+---------+
+| Alice | 25 |
++----------+---------+
+```
+
Accessing struct containing complex types:
```sql
select struct_element(named_struct('array', [1,2,3], 'map', {'key':'value'}),
'array');
@@ -98,7 +114,7 @@ ERROR 1105 (HY000): errCode = 2, detailMessage = the
specified field index out o
Second parameter is not a constant:
```sql
select struct_element(named_struct('name', 'Alice', 'age', 25), inv) from
var_with_index where k = 4;
-ERROR 1105 (HY000): errCode = 2, detailMessage = struct_element only allows
constant int or string second parameter: struct_element(named_struct('name',
'Alice', 'age', 25), inv)
+ERROR 1105 (HY000): errCode = 2, detailMessage = element_at over a struct only
allows a constant int or string second parameter:
element_at(named_struct('name', 'Alice', 'age', 25), inv)
```
Input struct is NULL, will report error:
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
index a64898c64c8..4a9b8594781 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
@@ -8,10 +8,11 @@
## Function
-The `ELEMENT_AT` function is used to extract the element value from an array
or map based on the specified index or key.
+The `ELEMENT_AT` function is used to extract the element value from an array,
map, struct, or variant based on the specified index or key.
- When applied to an **ARRAY**, it returns the element at the specified
position.
- When applied to a **MAP**, it returns the value corresponding to the
specified key.
+- When applied to a **STRUCT**, it returns the subfield at the specified
position (starting from 1) or with the specified field name, equivalent to
`STRUCT_ELEMENT`.
- When applied to a **VARIANT**, it returns the value of the specified
subfield.
## Syntax
@@ -22,18 +23,20 @@ ELEMENT_AT(container, key_or_index)
## Parameters
-- `container`: Can be `ARRAY`, `MAP`, or `VARIANT`.
+- `container`: Can be `ARRAY`, `MAP`, `STRUCT`, or `VARIANT`.
- `key_or_index`:
- For `ARRAY`: An integer, with indexing starting from **1**.
- For `MAP`: The key type (`K`) of the `MAP`, which can be any supported
primitive type.
+ - For `STRUCT`: A constant integer field position (starting from **1**) or a
constant string field name (matched **case-insensitively**).
- For `VARIANT`: A string type.
## Return Value
- For `ARRAY`: Returns the element at the specified index (`T` type).
- For `MAP`: Returns the value corresponding to the specified key (`V` type).
+- For `STRUCT`: Returns the specified subfield value.
- For `VARIANT`: Returns a `VARIANT` type value.
-- If the index or key does not exist, returns `NULL`.
+- If the index or key does not exist, returns `NULL` (for `STRUCT`, an
out-of-bound position or a non-existent field name reports an error).
- If the parameter is `NULL`, returns `NULL`.
## Notes
@@ -91,7 +94,25 @@ ELEMENT_AT(container, key_or_index)
+-----------------------------------+
```
-4. When accessing a subfield of a `VARIANT`, if the `VARIANT` value is not an
OBJECT, an empty value is returned.
+4. Accessing a `STRUCT` subfield by position or by field name (equivalent to
`STRUCT_ELEMENT`).
+
+ ```SQL
+ SELECT ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1);
+ +------------------------------------------------------------+
+ | ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1) |
+ +------------------------------------------------------------+
+ | Jack |
+ +------------------------------------------------------------+
+
+ SELECT NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'];
+ +--------------------------------------------------+
+ | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
+ +--------------------------------------------------+
+ | 1728923 |
+ +--------------------------------------------------+
+ ```
+
+5. When accessing a subfield of a `VARIANT`, if the `VARIANT` value is not an
OBJECT, an empty value is returned.
```SQL
SELECT ELEMENT_AT(CAST('{"a": 1, "b": 2}' AS VARIANT), "a");
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
index e235d7956bf..230efac64af 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
@@ -86,27 +86,50 @@ STRUCT 类型用于将多个字段组合成一个结构体，每个字段可以
## 元素访问
-- 使用`STRUCT_ELEMENT(struct, k/field_name)`访问`STRUCT`内部的某一个子列。
- - k表征位置，从1开始。
- - `filed_name` 是`STRUCT`的子列的名字。
+可以通过以下等价方式访问 `STRUCT` 内部的某一个子列：
+
+- `ELEMENT_AT(struct, k/field_name)` 函数。
+
+- 下标运算符 `struct[k]` / `struct['field_name']`。
+
+- 点运算符 `struct_col.field_name`（仅适用于 `STRUCT` 列）。
+
+其中 k 表征位置（常量，从 1 开始），`field_name`
是子列的名字（字符串常量）。字段名按**大小写不敏感**匹配。访问不存在的字段名或越界的位置会报错。
+
+:::caution
+因业界其他数据库和查询引擎均无此函数，`STRUCT_ELEMENT` 自 4.1.3 版本起已移除，请改用 `ELEMENT_AT` 或下标 / 点运算符。
+:::
+
+- 使用 `ELEMENT_AT` 或下标运算符按位置或字段名访问。
+
```SQL
- SELECT STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
+ SELECT ELEMENT_AT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
- +----------------------------------------------------------------+
- | STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1) |
- +----------------------------------------------------------------+
- | Jack |
- +----------------------------------------------------------------+
-
+ +------------------------------------------------------------+
+ | ELEMENT_AT(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 |
- +-------------------------------------------------------------------+
+ SELECT NAMED_STRUCT("name", "Jack", "id", 1728923)['id'];
+
+ +--------------------------------------------------+
+ | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
+ +--------------------------------------------------+
+ | 1728923 |
+ +--------------------------------------------------+
+ ```
+
+- 对于 `STRUCT` 列，使用点运算符按名字访问子列，并支持嵌套访问，如 `s.a.b`。
+
+ ```SQL
+ -- 表中有一个 STRUCT 列：s STRUCT<a: INT, b: DOUBLE>
+ SELECT s.a, s.b FROM struct_table;
+
+ -- 嵌套 STRUCT 列：s STRUCT<s: STRUCT<s: STRUCT<s: INT>>>
+ SELECT s.s.s.s FROM nested_struct_table;
```
+
## 示例
- 嵌套复杂类型
@@ -140,20 +163,20 @@ STRUCT 类型用于将多个字段组合成一个结构体，每个字段可以
STRUCT('2021-01-01 00:00:00', '2021-01-02 00:00:00', STRUCT(100, 50))
));
- -- 查询
- 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 |
-
+----------------------------------------------------------------------------------------------+
+ -- 查询（嵌套 struct 字段访问使用点运算符）
+ SELECT struct_complex.basic_info.name FROM struct_table order by id;
+ +--------------------------------+
+ | struct_complex.basic_info.name |
+ +--------------------------------+
+ | John |
+ +--------------------------------+
+
+ SELECT struct_complex.metadata.stats.views FROM struct_table order by id;
+ +-------------------------------------+
+ | struct_complex.metadata.stats.views |
+ +-------------------------------------+
+ | 100 |
+ +-------------------------------------+
```
- 修改类型
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
index e37b10c0a1d..b2aeed5f1a9 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
@@ -6,6 +6,10 @@
}
---
+:::caution
+因业界其他数据库和查询引擎均无此函数，`STRUCT_ELEMENT` 自 4.1.3 版本起已移除，请改用
[`ELEMENT_AT`](../variant-functions/element-at.md) 函数（或等价的下标 `s[k]` /
`s['field_name']`、点运算符 `s.field_name` 语法）。
+:::
+
## 描述
返回 struct 数据列内的某一字段。函数支持通过字段位置（索引）或字段名来访问结构体中的字段。
@@ -33,9 +37,10 @@ STRUCT_ELEMENT( <struct>, <field_location_or_name> )
## 使用说明
- 支持通过字段位置（索引）访问，索引从1开始
-- 支持通过字段名访问，字段名必须完全匹配
+- 支持通过字段名访问，字段名按**大小写不敏感**匹配
- 第二个参数必须是常量（不能是列）
- 函数标记为 AlwaysNullable，返回值可能为 null
+- 自 4.1.3 版本起 `STRUCT_ELEMENT` 已被删除，请改用 `ELEMENT_AT(<struct>, ...)`、下标运算符
`<struct>[<index>]` / `<struct>['<field_name>']` 或点运算符
`<struct_col>.<field_name>` —— 它们都是访问 struct 字段的等价写法。
## 举例
@@ -61,6 +66,17 @@ select struct_element(named_struct('name', 'Alice', 'age',
25, 'city', 'Beijing'
+------------------------------------------------------------------------------------+
```
+使用下标运算符访问（等价于上述调用）：
+```sql
+select named_struct('name', 'Alice', 'age', 25, 'city', 'Beijing')[1] as
by_index,
+ named_struct('name', 'Alice', 'age', 25, 'city', 'Beijing')['age'] as
by_name;
++----------+---------+
+| by_index | by_name |
++----------+---------+
+| Alice | 25 |
++----------+---------+
+```
+
访问包含有复杂类型的struct：
```sql
select struct_element(named_struct('array', [1,2,3], 'map', {'key':'value'}),
'array');
@@ -97,7 +113,7 @@ ERROR 1105 (HY000): errCode = 2, detailMessage = the
specified field index out o
访问的第二个参数不是常量：
```sql
select struct_element(named_struct('name', 'Alice', 'age', 25), inv) from
var_with_index where k = 4;
-ERROR 1105 (HY000): errCode = 2, detailMessage = struct_element only allows
constant int or string second parameter: struct_element(named_struct('name',
'Alice', 'age', 25), inv)
+ERROR 1105 (HY000): errCode = 2, detailMessage = element_at over a struct only
allows a constant int or string second parameter:
element_at(named_struct('name', 'Alice', 'age', 25), inv)
```
输入的struct 为NULL，会报错：
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
index 64f2d2ef790..f9bc791d6ca 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
@@ -8,10 +8,11 @@
## 功能
-`ELEMENT_AT` 函数用于从数组或 map 中按指定的索引或键提取对应的元素值。
+`ELEMENT_AT` 函数用于从数组、map、struct 或 variant 中按指定的索引或键提取对应的元素值。
- 当作用于 **数组（ARRAY）** 时，返回指定位置的元素；
- 当作用于 **MAP** 时，返回指定键对应的值。
+- 当作用于 **STRUCT** 时，返回指定位置（从 1 开始）或指定字段名对应的子列，等价于 `STRUCT_ELEMENT`。
- 当作用于 **VARIANT** 时，返回指定子列对应的值。
## 语法
@@ -22,18 +23,20 @@ ELEMENT_AT(container, key_or_index)
## 参数
-- `container`：可以是`ARRAY`, `MAP`, `VARIANT`。
+- `container`：可以是`ARRAY`, `MAP`, `STRUCT`, `VARIANT`。
- `key_or_index`：
- 对于 `ARRAY`：为整数类型，索引从 **1** 开始；
- 对于 `MAP`：为 `MAP` 中的键类型（`K`），可为任意支持的基础类型。
+ - 对于 `STRUCT`：为常量整数（字段位置，从 **1** 开始）或常量字符串（字段名，按**大小写不敏感**匹配）。
- 对于 `VARIANT`： 为字符串类型
## 返回值
- 若为 `ARRAY`，返回数组中对应索引的元素（`T` 类型）；
- 若为 `MAP`，返回对应键的值（`V` 类型）；
+- 若为 `STRUCT`，返回指定的子列值；
- 若为 `VARIANT`， 返回 `VARIANT` 类型；
-- 如果索引或键不存在，返回 `NULL`；
+- 如果索引或键不存在，返回 `NULL`（对于 `STRUCT`，位置越界或字段名不存在会报错）；
- 如果参数为 `NULL`，返回 `NULL`。
## 使用说明
@@ -91,7 +94,25 @@ ELEMENT_AT(container, key_or_index)
+-----------------------------------+
```
-4. 访问 `VARIANT` 的某个子列，如果 `VARIANT` 的值不是 OBJECT，返回空
+4. 按位置或字段名访问 `STRUCT` 的子列（等价于 `STRUCT_ELEMENT`）。
+
+ ```SQL
+ SELECT ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1);
+ +------------------------------------------------------------+
+ | ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1) |
+ +------------------------------------------------------------+
+ | Jack |
+ +------------------------------------------------------------+
+
+ SELECT NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'];
+ +--------------------------------------------------+
+ | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
+ +--------------------------------------------------+
+ | 1728923 |
+ +--------------------------------------------------+
+ ```
+
+5. 访问 `VARIANT` 的某个子列，如果 `VARIANT` 的值不是 OBJECT，返回空
```SQL
SELECT ELEMENT_AT(CAST('{"a": 1, "b": 2}' AS VARIANT), "a");
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
index e235d7956bf..230efac64af 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
@@ -86,27 +86,50 @@ STRUCT 类型用于将多个字段组合成一个结构体，每个字段可以
## 元素访问
-- 使用`STRUCT_ELEMENT(struct, k/field_name)`访问`STRUCT`内部的某一个子列。
- - k表征位置，从1开始。
- - `filed_name` 是`STRUCT`的子列的名字。
+可以通过以下等价方式访问 `STRUCT` 内部的某一个子列：
+
+- `ELEMENT_AT(struct, k/field_name)` 函数。
+
+- 下标运算符 `struct[k]` / `struct['field_name']`。
+
+- 点运算符 `struct_col.field_name`（仅适用于 `STRUCT` 列）。
+
+其中 k 表征位置（常量，从 1 开始），`field_name`
是子列的名字（字符串常量）。字段名按**大小写不敏感**匹配。访问不存在的字段名或越界的位置会报错。
+
+:::caution
+因业界其他数据库和查询引擎均无此函数，`STRUCT_ELEMENT` 自 4.1.3 版本起已移除，请改用 `ELEMENT_AT` 或下标 / 点运算符。
+:::
+
+- 使用 `ELEMENT_AT` 或下标运算符按位置或字段名访问。
+
```SQL
- SELECT STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
+ SELECT ELEMENT_AT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
- +----------------------------------------------------------------+
- | STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1) |
- +----------------------------------------------------------------+
- | Jack |
- +----------------------------------------------------------------+
-
+ +------------------------------------------------------------+
+ | ELEMENT_AT(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 |
- +-------------------------------------------------------------------+
+ SELECT NAMED_STRUCT("name", "Jack", "id", 1728923)['id'];
+
+ +--------------------------------------------------+
+ | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
+ +--------------------------------------------------+
+ | 1728923 |
+ +--------------------------------------------------+
+ ```
+
+- 对于 `STRUCT` 列，使用点运算符按名字访问子列，并支持嵌套访问，如 `s.a.b`。
+
+ ```SQL
+ -- 表中有一个 STRUCT 列：s STRUCT<a: INT, b: DOUBLE>
+ SELECT s.a, s.b FROM struct_table;
+
+ -- 嵌套 STRUCT 列：s STRUCT<s: STRUCT<s: STRUCT<s: INT>>>
+ SELECT s.s.s.s FROM nested_struct_table;
```
+
## 示例
- 嵌套复杂类型
@@ -140,20 +163,20 @@ STRUCT 类型用于将多个字段组合成一个结构体，每个字段可以
STRUCT('2021-01-01 00:00:00', '2021-01-02 00:00:00', STRUCT(100, 50))
));
- -- 查询
- 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 |
-
+----------------------------------------------------------------------------------------------+
+ -- 查询（嵌套 struct 字段访问使用点运算符）
+ SELECT struct_complex.basic_info.name FROM struct_table order by id;
+ +--------------------------------+
+ | struct_complex.basic_info.name |
+ +--------------------------------+
+ | John |
+ +--------------------------------+
+
+ SELECT struct_complex.metadata.stats.views FROM struct_table order by id;
+ +-------------------------------------+
+ | struct_complex.metadata.stats.views |
+ +-------------------------------------+
+ | 100 |
+ +-------------------------------------+
```
- 修改类型
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
index e37b10c0a1d..b2aeed5f1a9 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
@@ -6,6 +6,10 @@
}
---
+:::caution
+因业界其他数据库和查询引擎均无此函数，`STRUCT_ELEMENT` 自 4.1.3 版本起已移除，请改用
[`ELEMENT_AT`](../variant-functions/element-at.md) 函数（或等价的下标 `s[k]` /
`s['field_name']`、点运算符 `s.field_name` 语法）。
+:::
+
## 描述
返回 struct 数据列内的某一字段。函数支持通过字段位置（索引）或字段名来访问结构体中的字段。
@@ -33,9 +37,10 @@ STRUCT_ELEMENT( <struct>, <field_location_or_name> )
## 使用说明
- 支持通过字段位置（索引）访问，索引从1开始
-- 支持通过字段名访问，字段名必须完全匹配
+- 支持通过字段名访问，字段名按**大小写不敏感**匹配
- 第二个参数必须是常量（不能是列）
- 函数标记为 AlwaysNullable，返回值可能为 null
+- 自 4.1.3 版本起 `STRUCT_ELEMENT` 已被删除，请改用 `ELEMENT_AT(<struct>, ...)`、下标运算符
`<struct>[<index>]` / `<struct>['<field_name>']` 或点运算符
`<struct_col>.<field_name>` —— 它们都是访问 struct 字段的等价写法。
## 举例
@@ -61,6 +66,17 @@ select struct_element(named_struct('name', 'Alice', 'age',
25, 'city', 'Beijing'
+------------------------------------------------------------------------------------+
```
+使用下标运算符访问（等价于上述调用）：
+```sql
+select named_struct('name', 'Alice', 'age', 25, 'city', 'Beijing')[1] as
by_index,
+ named_struct('name', 'Alice', 'age', 25, 'city', 'Beijing')['age'] as
by_name;
++----------+---------+
+| by_index | by_name |
++----------+---------+
+| Alice | 25 |
++----------+---------+
+```
+
访问包含有复杂类型的struct：
```sql
select struct_element(named_struct('array', [1,2,3], 'map', {'key':'value'}),
'array');
@@ -97,7 +113,7 @@ ERROR 1105 (HY000): errCode = 2, detailMessage = the
specified field index out o
访问的第二个参数不是常量：
```sql
select struct_element(named_struct('name', 'Alice', 'age', 25), inv) from
var_with_index where k = 4;
-ERROR 1105 (HY000): errCode = 2, detailMessage = struct_element only allows
constant int or string second parameter: struct_element(named_struct('name',
'Alice', 'age', 25), inv)
+ERROR 1105 (HY000): errCode = 2, detailMessage = element_at over a struct only
allows a constant int or string second parameter:
element_at(named_struct('name', 'Alice', 'age', 25), inv)
```
输入的struct 为NULL，会报错：
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
index 64f2d2ef790..f9bc791d6ca 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
@@ -8,10 +8,11 @@
## 功能
-`ELEMENT_AT` 函数用于从数组或 map 中按指定的索引或键提取对应的元素值。
+`ELEMENT_AT` 函数用于从数组、map、struct 或 variant 中按指定的索引或键提取对应的元素值。
- 当作用于 **数组（ARRAY）** 时，返回指定位置的元素；
- 当作用于 **MAP** 时，返回指定键对应的值。
+- 当作用于 **STRUCT** 时，返回指定位置（从 1 开始）或指定字段名对应的子列，等价于 `STRUCT_ELEMENT`。
- 当作用于 **VARIANT** 时，返回指定子列对应的值。
## 语法
@@ -22,18 +23,20 @@ ELEMENT_AT(container, key_or_index)
## 参数
-- `container`：可以是`ARRAY`, `MAP`, `VARIANT`。
+- `container`：可以是`ARRAY`, `MAP`, `STRUCT`, `VARIANT`。
- `key_or_index`：
- 对于 `ARRAY`：为整数类型，索引从 **1** 开始；
- 对于 `MAP`：为 `MAP` 中的键类型（`K`），可为任意支持的基础类型。
+ - 对于 `STRUCT`：为常量整数（字段位置，从 **1** 开始）或常量字符串（字段名，按**大小写不敏感**匹配）。
- 对于 `VARIANT`： 为字符串类型
## 返回值
- 若为 `ARRAY`，返回数组中对应索引的元素（`T` 类型）；
- 若为 `MAP`，返回对应键的值（`V` 类型）；
+- 若为 `STRUCT`，返回指定的子列值；
- 若为 `VARIANT`， 返回 `VARIANT` 类型；
-- 如果索引或键不存在，返回 `NULL`；
+- 如果索引或键不存在，返回 `NULL`（对于 `STRUCT`，位置越界或字段名不存在会报错）；
- 如果参数为 `NULL`，返回 `NULL`。
## 使用说明
@@ -91,7 +94,25 @@ ELEMENT_AT(container, key_or_index)
+-----------------------------------+
```
-4. 访问 `VARIANT` 的某个子列，如果 `VARIANT` 的值不是 OBJECT，返回空
+4. 按位置或字段名访问 `STRUCT` 的子列（等价于 `STRUCT_ELEMENT`）。
+
+ ```SQL
+ SELECT ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1);
+ +------------------------------------------------------------+
+ | ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1) |
+ +------------------------------------------------------------+
+ | Jack |
+ +------------------------------------------------------------+
+
+ SELECT NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'];
+ +--------------------------------------------------+
+ | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
+ +--------------------------------------------------+
+ | 1728923 |
+ +--------------------------------------------------+
+ ```
+
+5. 访问 `VARIANT` 的某个子列，如果 `VARIANT` 的值不是 OBJECT，返回空
```SQL
SELECT ELEMENT_AT(CAST('{"a": 1, "b": 2}' AS VARIANT), "a");
diff --git
a/versioned_docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
b/versioned_docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
index 1be44bd51cd..b3f8b335e65 100644
---
a/versioned_docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
+++
b/versioned_docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md
@@ -100,28 +100,48 @@ The STRUCT type is used to combine multiple fields into a
single structure, wher
## Element Access
-- Use `STRUCT_ELEMENT(struct, k/field_name)` to access a specific subcolumn
inside the `STRUCT`.
+A specific subcolumn inside a `STRUCT` can be accessed in the following
equivalent ways:
- - k represents the position, starting from 1.
+- `ELEMENT_AT(struct, k/field_name)` function.
- - `field_name` is the name of the subcolumn in the `STRUCT`.
+- The subscript operator `struct[k]` / `struct['field_name']`.
+
+- The dot operator `struct_col.field_name` (only for a `STRUCT` column).
+
+Where `k` is the position (a constant starting from 1) and `field_name` is the
subcolumn name (a string constant). Field names are matched
**case-insensitively**. Accessing a non-existent field name or an out-of-bound
position reports an error.
+
+:::caution
+As no other database or query engine provides such a function,
`STRUCT_ELEMENT` has been removed since version 4.1.3. Use `ELEMENT_AT` or the
subscript / dot operators instead.
+:::
+
+- Use `ELEMENT_AT` or the subscript operator to access by position or field
name.
```SQL
- SELECT STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
+ SELECT ELEMENT_AT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1);
+
+ +------------------------------------------------------------+
+ | ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1) |
+ +------------------------------------------------------------+
+ | Jack |
+ +------------------------------------------------------------+
- +----------------------------------------------------------------+
- | STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), 1) |
- +----------------------------------------------------------------+
- | Jack |
- +----------------------------------------------------------------+
+ SELECT NAMED_STRUCT("name", "Jack", "id", 1728923)['id'];
- SELECT STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), "id");
+ +--------------------------------------------------+
+ | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
+ +--------------------------------------------------+
+ | 1728923 |
+ +--------------------------------------------------+
+ ```
+
+- For a `STRUCT` column, use the dot operator to access a subcolumn by name,
including nested access such as `s.a.b`.
+
+ ```SQL
+ -- Table with a STRUCT column: s STRUCT<a: INT, b: DOUBLE>
+ SELECT s.a, s.b FROM struct_table;
- +-------------------------------------------------------------------+
- | STRUCT_ELEMENT(NAMED_STRUCT("name", "Jack", "id", 1728923), "id") |
- +-------------------------------------------------------------------+
- | 1728923 |
- +-------------------------------------------------------------------+
+ -- Nested STRUCT column: s STRUCT<s: STRUCT<s: STRUCT<s: INT>>>
+ SELECT s.s.s.s FROM nested_struct_table;
```
## Examples
@@ -157,22 +177,22 @@ The STRUCT type is used to combine multiple fields into a
single structure, wher
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;
+ -- Query (use the dot operator for nested struct field access)
+ SELECT struct_complex.basic_info.name FROM struct_table ORDER BY id;
- +----------------------------------------------------------------------+
- | STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'basic_info'), 'name') |
- +----------------------------------------------------------------------+
- | John |
- +----------------------------------------------------------------------+
+ +--------------------------------+
+ | struct_complex.basic_info.name |
+ +--------------------------------+
+ | John |
+ +--------------------------------+
- SELECT STRUCT_ELEMENT(STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex,
'metadata'), 'stats'), 'views') FROM struct_table ORDER BY id;
+ SELECT struct_complex.metadata.stats.views FROM struct_table ORDER BY id;
-
+----------------------------------------------------------------------------------------------+
- | STRUCT_ELEMENT(STRUCT_ELEMENT(STRUCT_ELEMENT(struct_complex, 'metadata'),
'stats'), 'views') |
-
+----------------------------------------------------------------------------------------------+
- |
100 |
-
+----------------------------------------------------------------------------------------------+
+ +-------------------------------------+
+ | struct_complex.metadata.stats.views |
+ +-------------------------------------+
+ | 100 |
+ +-------------------------------------+
```
- Modifying Type
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
index ffaf65c9da2..72e8e718466 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/struct-functions/struct-element.md
@@ -6,6 +6,10 @@
}
---
+:::caution
+As no other database or query engine provides such a function,
`STRUCT_ELEMENT` has been removed since version 4.1.3. Please use the
[`ELEMENT_AT`](../variant-functions/element-at.md) function instead (or the
equivalent subscript `s[k]` / `s['field_name']` and dot `s.field_name` syntax).
+:::
+
## Description
Returns a specific field within a struct data column. The function supports
accessing fields in a struct through field position (index) or field name.
@@ -33,9 +37,10 @@ Return value meaning:
## Usage
- Supports accessing by field position (index), index starts from 1
-- Supports accessing by field name, field name must match exactly
+- Supports accessing by field name; the field name is matched
**case-insensitively**
- The second parameter must be a constant (cannot be a column)
- The function is marked as AlwaysNullable, return value may be null
+- Since version 4.1.3, `STRUCT_ELEMENT` is removed. Use `ELEMENT_AT(<struct>,
...)`, the subscript operators `<struct>[<index>]` /
`<struct>['<field_name>']`, or the dot operator `<struct_col>.<field_name>`
instead — these are all equivalent ways to access a struct field.
## Examples
@@ -61,6 +66,17 @@ select struct_element(named_struct('name', 'Alice', 'age',
25, 'city', 'Beijing'
+------------------------------------------------------------------------------------+
```
+Access using the subscript operator (equivalent to the calls above):
+```sql
+select named_struct('name', 'Alice', 'age', 25, 'city', 'Beijing')[1] as
by_index,
+ named_struct('name', 'Alice', 'age', 25, 'city', 'Beijing')['age'] as
by_name;
++----------+---------+
+| by_index | by_name |
++----------+---------+
+| Alice | 25 |
++----------+---------+
+```
+
Accessing struct containing complex types:
```sql
select struct_element(named_struct('array', [1,2,3], 'map', {'key':'value'}),
'array');
@@ -98,7 +114,7 @@ ERROR 1105 (HY000): errCode = 2, detailMessage = the
specified field index out o
Second parameter is not a constant:
```sql
select struct_element(named_struct('name', 'Alice', 'age', 25), inv) from
var_with_index where k = 4;
-ERROR 1105 (HY000): errCode = 2, detailMessage = struct_element only allows
constant int or string second parameter: struct_element(named_struct('name',
'Alice', 'age', 25), inv)
+ERROR 1105 (HY000): errCode = 2, detailMessage = element_at over a struct only
allows a constant int or string second parameter:
element_at(named_struct('name', 'Alice', 'age', 25), inv)
```
Input struct is NULL, will report error:
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
index a64898c64c8..4a9b8594781 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
@@ -8,10 +8,11 @@
## Function
-The `ELEMENT_AT` function is used to extract the element value from an array
or map based on the specified index or key.
+The `ELEMENT_AT` function is used to extract the element value from an array,
map, struct, or variant based on the specified index or key.
- When applied to an **ARRAY**, it returns the element at the specified
position.
- When applied to a **MAP**, it returns the value corresponding to the
specified key.
+- When applied to a **STRUCT**, it returns the subfield at the specified
position (starting from 1) or with the specified field name, equivalent to
`STRUCT_ELEMENT`.
- When applied to a **VARIANT**, it returns the value of the specified
subfield.
## Syntax
@@ -22,18 +23,20 @@ ELEMENT_AT(container, key_or_index)
## Parameters
-- `container`: Can be `ARRAY`, `MAP`, or `VARIANT`.
+- `container`: Can be `ARRAY`, `MAP`, `STRUCT`, or `VARIANT`.
- `key_or_index`:
- For `ARRAY`: An integer, with indexing starting from **1**.
- For `MAP`: The key type (`K`) of the `MAP`, which can be any supported
primitive type.
+ - For `STRUCT`: A constant integer field position (starting from **1**) or a
constant string field name (matched **case-insensitively**).
- For `VARIANT`: A string type.
## Return Value
- For `ARRAY`: Returns the element at the specified index (`T` type).
- For `MAP`: Returns the value corresponding to the specified key (`V` type).
+- For `STRUCT`: Returns the specified subfield value.
- For `VARIANT`: Returns a `VARIANT` type value.
-- If the index or key does not exist, returns `NULL`.
+- If the index or key does not exist, returns `NULL` (for `STRUCT`, an
out-of-bound position or a non-existent field name reports an error).
- If the parameter is `NULL`, returns `NULL`.
## Notes
@@ -91,7 +94,25 @@ ELEMENT_AT(container, key_or_index)
+-----------------------------------+
```
-4. When accessing a subfield of a `VARIANT`, if the `VARIANT` value is not an
OBJECT, an empty value is returned.
+4. Accessing a `STRUCT` subfield by position or by field name (equivalent to
`STRUCT_ELEMENT`).
+
+ ```SQL
+ SELECT ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1);
+ +------------------------------------------------------------+
+ | ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1) |
+ +------------------------------------------------------------+
+ | Jack |
+ +------------------------------------------------------------+
+
+ SELECT NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'];
+ +--------------------------------------------------+
+ | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
+ +--------------------------------------------------+
+ | 1728923 |
+ +--------------------------------------------------+
+ ```
+
+5. When accessing a subfield of a `VARIANT`, if the `VARIANT` value is not an
OBJECT, an empty value is returned.
```SQL
SELECT ELEMENT_AT(CAST('{"a": 1, "b": 2}' AS VARIANT), "a");
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
