跳转到主要内容

半结构

半结构化数据类型​

名称别名从值创建Description
VARIANTJSON[1,{"a":1,"b":{"c":2}}]Collection of elements of different data types, including NULL, BOOLEAN, NUMBER, STRING, ARRAY, and OBJECT.

VARIANT 数据类型​

A VARIANT can store a value of any other type, including NULL, BOOLEAN, NUMBER, STRING, ARRAY, and OBJECT, and the internal value can be any level of nested structure, which is very flexible to store various data. VARIANT can also be called JSON, for more information, please refer to JSON website

Example​

创建表：

CREATE TABLE variant_table(var VARIANT NULL);

在表中插入不同类型的值：

INSERT INTO variant_table VALUES(1),(1.34),(true),(parse_json('[1,2,3,["a","b","c"]]')),(parse_json('{"a":1,"b":{"c":2}}'));

查询结果：

SELECT * FROM variant_table;
+-----------------------+
| var                   |
+-----------------------+
| 1                     |
| 1.34                  |
| true                  |
| [1,2,3,["a","b","c"]] |
| {"a":1,"b":{"c":2}}   |
+-----------------------+

Get by index​

Variant contains ARRAY is a zero based array like many other programming languages, each element is also a Variant type. Element can be accessed by its index.

Example​

CREATE TABLE array_table(arr VARIANT NULL);

Desc the array_table:

DESC array_table;
+-------+---------+------+---------+
| Field | Type    | Null | Default |
+-------+---------+------+---------+
| arr   | Variant | YES  | NULL    |
+-------+---------+------+---------+

将 [1,2,3,["a","b","c"]] 这个值插入表中。

INSERT INTO array_table VALUES(parse_json('[1,2,3,["a","b","c"]]'));

获取数组中下标为 0 的元素：

SELECT arr[0] FROM array_table;
+--------+
| arr[0] |
+--------+
| 1      |
+--------+

获取数组中下标为 3 的元素：

SELECT arr[3] FROM array_table;
+---------------+
| arr[3]        |
+---------------+
| ["a","b","c"] |
+---------------+

arr[3] 也是 ARRAY 类型，我们还可以获取它的子元素：

SELECT arr[3][0] FROM array_table;
+-----------+
| arr[3][0] |
+-----------+
| "a"       |
+-----------+

Get by field name​

Variant contains OBJECT is key-value pairs, each key is a VARCHAR, and each value is a Variant. It act like a "dictionary”, “hash”, or “map” in other programming languages. Value can be accessed by the field name.

示例 1​

This example shows how to access the values at each hierarchical level of a Variant:

Create a table with VARIANT type:

CREATE TABLE object_table(obj VARIANT NULL);

描述 object_table：

DESC object_table;
+-------+---------+------+---------+
| Field | Type    | Null | Default |
+-------+---------+------+---------+
| obj   | Variant | YES  | NULL    |
+-------+---------+------+---------+

将值 {"a":1,"b":{"c":2}} 插入到表中：

INSERT INTO object_table VALUES(parse_json('{"a":1,"b":{"c":2}}'));

通过键 a 获取值：

SELECT obj:a FROM object_table;
+-------+
| obj:a |
+-------+
| 1     |
+-------+

通过键 b 获取值：

SELECT obj:b FROM object_table;
+---------+
| obj:b   |
+---------+
| {"c":2} |
+---------+

通过键 b:c 获取子值：

SELECT obj:b:c FROM object_table;
+---------+
| obj:b:c |
+---------+
| 2       |
+---------+

示例 2​

This example shows how to query with data of the VARIANT type:

Create a table with an VARIANT column to hold the employee's contact information including name and Email address:

CREATE TABLE employees (id INT, info VARIANT);

向表中插入两行数据：

INSERT INTO employees VALUES (1, parse_json('{"Email": "amy@databend.com", "Name":"Amy"}'));
INSERT INTO employees VALUES (2, parse_json('{"Email": "bob@databend.com", "Name":"Bob"}'));

以下语句可以列出所有 ID 小于 3 的雇员的电子邮件地址：

SELECT info:Email FROM employees WHERE id < 3;

+------------------+
| info:Email       |
+------------------+
| amy@databend.com |
| bob@databend.com |
+------------------+

以下语句可以按 Bob 的名字返回 TA 的电子邮件地址：

SELECT info:Email FROM employees WHERE info:Name = 'Bob';

+------------------+
| info:Email       |
+------------------+
| bob@databend.com |
+------------------+

以下语句可以按 Bob 的 ID 和名字返回 TA 的电子邮件地址：

SELECT info:Email FROM employees WHERE id = 2 and info:Name = 'Bob';

+------------------+
| info:Email       |
+------------------+
| bob@databend.com |
+------------------+

数据类型转换​

默认情况下，将返回从 VARIANT 列检索到的元素。 若要将返回的元素转换为特定类型，请添加 :: 算符和目标数据类型（例如 expression:::type）。

SELECT Arr[0]::INT FROM array_table
+---------------+
| arr[0]::Int32 |
+---------------+
|             1 |
+---------------+

让我们执行一个更复杂的查询：

SELECT sum(arr[0]::INT) FROM array_table GROUP BY arr[0]::INT;
+--------------------+
| sum(arr[0]::Int32) |
+--------------------+
|                  1 |
+--------------------+

函数​

查看 半结构函数。