版本: 最新版本-3.5

JSON

自 2.2.0 版本起，StarRocks 支持 JSON。本文介绍 JSON 的基本概念、StarRocks 如何创建 JSON 类型列、加载和查询 JSON 数据，以及如何通过 JSON 函数和运算符构造和处理 JSON 数据。

什么是 JSON

JSON 是一种轻量级的数据交换格式。JSON 类型的数据是半结构化的，支持树状结构。JSON 数据具有层次性、灵活性、易于阅读和处理的特点，广泛应用于数据存储和分析场景。JSON 支持 NUMBER、STRING、BOOLEAN、ARRAY、OBJECT 和 NULL 值等数据类型。

有关 JSON 的更多信息，请参阅 JSON 官方网站。有关 JSON 数据输入和输出语法，请参阅 JSON 规范 RFC 7159。

StarRocks 支持存储和高效查询及分析 JSON 数据。StarRocks 使用二进制格式编码来存储 JSON 数据，而不是直接存储输入文本，这降低了数据计算和查询期间的解析成本，从而提高了查询效率。

使用 JSON 数据

创建 JSON 类型列

创建表时，使用关键字 JSON 将列 j 指定为 JSON 类型。

CREATE TABLE `tj` (
    `id` INT(11) NOT NULL COMMENT "",
    `j`  JSON NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`id`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
    "replication_num" = "3",
    "storage_format" = "DEFAULT"
);

加载数据并存储为 JSON 类型

StarRocks 支持以下方法加载数据并将其存储为 JSON 类型。

方法 1：使用 INSERT INTO 将数据写入 JSON 类型列（例如，列 j）。

INSERT INTO tj (id, j) VALUES (1, parse_json('{"a": 1, "b": true}'));
INSERT INTO tj (id, j) VALUES (2, parse_json('{"a": 2, "b": false}'));
INSERT INTO tj (id, j) VALUES (3, parse_json('{"a": 3, "b": true}'));
INSERT INTO tj (id, j) VALUES (4, json_object('a', 4, 'b', false)); 

PARSE_JSON 函数可以基于字符串类型的数据构造 JSON 类型的数据。JSON_OBJECT 函数可以构造 JSON 对象类型的数据，允许将现有表转换为 JSON 类型。有关更多信息，请参阅 PARSE_JSON 和 JSON_OBJECT。

方法 2：使用 Stream Load 导入 JSON 文件并将其存储为 JSON 类型。有关导入方法，请参阅导入 JSON 数据。
- 要导入 JSON 文件根节点处的 JSON 对象并将其存储为 JSON 类型，请将 jsonpaths 设置为 $。
- 要导入 JSON 文件中 JSON 对象的值并将其存储为 JSON 类型，请将 jsonpaths 设置为 $.a（其中 a 表示键）。有关更多 JSON 路径表达式，请参阅 JSON 路径。
方法 3：使用 Broker Load 导入 Parquet 文件并将其存储为 JSON 类型。有关导入方法，请参阅 Broker Load。

导入期间支持数据类型转换，如下所示

Parquet 文件中的数据类型	转换后的 JSON 数据类型
整数类型（INT8、INT16、INT32、INT64、UINT8、UINT16、UINT32、UINT64）	JSON Number
浮点类型（FLOAT、DOUBLE）	JSON Number
BOOLEAN	JSON Boolean
STRING	JSON String
MAP	JSON Object
STRUCT	JSON Object
LIST	JSON Array
UNION、TIMESTAMP 和其他类型	不支持

方法 4：使用 Routine Load 从 Kafka 持续消费 JSON 格式的数据，并将其导入 StarRocks。

查询和处理 JSON 类型数据

StarRocks 支持查询和处理 JSON 类型数据，并支持使用 JSON 函数和运算符。

此示例使用表 tj 进行说明。

mysql> select * from tj;
+------+----------------------+
| id   |          j           |
+------+----------------------+
| 1    | {"a": 1, "b": true}  |
| 2    | {"a": 2, "b": false} |
| 3    | {"a": 3, "b": true}  |
| 4    | {"a": 4, "b": false} |
+------+----------------------+

示例 1：过滤 JSON 类型列中满足条件 id=1 的数据。

mysql> select * from tj where id = 1;
+------+---------------------+
| id   |           j         |
+------+---------------------+
| 1    | {"a": 1, "b": true} |
+------+---------------------+

示例 2：根据 JSON 类型列过滤表中的数据。

在以下示例中，j->'a' 返回 JSON 类型的数据。您可以将其与第一个示例进行比较，后者对数据执行隐式转换；或者使用 CAST 函数将 JSON 类型的数据构造为 INT 进行比较。

mysql> select * from tj where j->'a' = 1;
+------+---------------------+
| id   | j                   |
+------+---------------------+
|    1 | {"a": 1, "b": true} |
+------+---------------------+

mysql> select * from tj where cast(j->'a' as INT) = 1; 
+------+---------------------+
|   id |         j           |
+------+---------------------+
|   1  | {"a": 1, "b": true} |
+------+---------------------+

示例 3：根据 JSON 类型列过滤表中的数据（您可以使用 CAST 函数将 JSON 类型列构造为 BOOLEAN 类型）。

mysql> select * from tj where cast(j->'b' as boolean);
+------+---------------------+
|  id  |          j          |
+------+---------------------+
| 1    | {"a": 1, "b": true} |
| 3    | {"a": 3, "b": true} |
+------+---------------------+

示例 4：过滤 JSON 类型列中满足条件的数据并执行数值运算。

mysql> select cast(j->'a' as int) from tj where cast(j->'b' as boolean);
+-----------------------+
|  CAST(j->'a' AS INT)  |
+-----------------------+
|          3            |
|          1            |
+-----------------------+

mysql> select sum(cast(j->'a' as int)) from tj where cast(j->'b' as boolean);
+----------------------------+
| sum(CAST(j->'a' AS INT))  |
+----------------------------+
|              4             |
+----------------------------+

示例 5：基于 JSON 类型列进行排序。

mysql> select * from tj
       where j->'a' <= 3
       order by cast(j->'a' as int);
+------+----------------------+
| id   | j                    |
+------+----------------------+
|    1 | {"a": 1, "b": true}  |
|    2 | {"a": 2, "b": false} |
|    3 | {"a": 3, "b": true}  |
|    4 | {"a": 4, "b": false} |
+------+----------------------+

JSON 函数和运算符

JSON 函数和运算符可用于构造和处理 JSON 数据。有关详细信息，请参阅 JSON 函数和运算符。

JSON Array

JSON 可以包含嵌套数据，例如对象、数组或嵌套在数组中的其他 JSON 数据类型。StarRocks 提供了一组丰富的函数和运算符来处理这些复杂的嵌套 JSON 数据结构。以下将介绍如何处理包含数组的 JSON 数据。

假设 events 表中有一个 JSON 字段 event_data，内容如下

{
  "user": "Alice",
  "actions": [
    {"type": "click", "timestamp": "2024-03-17T10:00:00Z", "quantity": 1},
    {"type": "view", "timestamp": "2024-03-17T10:05:00Z", "quantity": 2},
    {"type": "purchase", "timestamp": "2024-03-17T10:10:00Z", "quantity": 3}
  ]
}

以下示例演示了几种常见的 JSON 数组分析场景

提取数组元素：从 actions 数组中提取特定字段（如 type、timestamp 等）并执行投影操作。
数组展开：使用 json_each 函数将嵌套的 JSON 数组展开为多行多列表结构，以便后续分析。
数组计算：使用 Array 函数过滤、转换和聚合数组元素，例如计算特定类型的操作的数量。

1. 从 JSON Array 中提取元素

要从 JSON Array 中提取嵌套元素，可以使用以下语法

返回类型仍然是 JSON Array，您可以使用 CAST 表达式进行类型转换。

MySQL > SELECT json_query(event_data, '$.actions[*].type') as json_array FROM events;
+-------------------------------+
| json_array                    |
+-------------------------------+
| ["click", "view", "purchase"] |
+-------------------------------+

MySQL > SELECT cast(json_query(event_data, '$.actions[*].type') as array<string>) array_string FROM events;
+-----------------------------+
| array_string                |
+-----------------------------+
| ["click","view","purchase"] |
+-----------------------------+

2. 使用 json_each 展开

StarRocks 提供了 json_each 函数来展开 JSON 数组，将其转换为多行数据。例如

MySQL > select value from events, json_each(event_data->'actions');
+--------------------------------------------------------------------------+
| value                                                                    |
+--------------------------------------------------------------------------+
| {"quantity": 1, "timestamp": "2024-03-17T10:00:00Z", "type": "click"}    |
| {"quantity": 2, "timestamp": "2024-03-17T10:05:00Z", "type": "view"}     |
| {"quantity": 3, "timestamp": "2024-03-17T10:10:00Z", "type": "purchase"} |
+--------------------------------------------------------------------------+

要分别提取 type 和 timestamp 字段

MySQL > select value->'timestamp', value->'type' from events, json_each(event_data->'actions');
+------------------------+---------------+
| value->'timestamp'     | value->'type' |
+------------------------+---------------+
| "2024-03-17T10:00:00Z" | "click"       |
| "2024-03-17T10:05:00Z" | "view"        |
| "2024-03-17T10:10:00Z" | "purchase"    |
+------------------------+---------------+

之后，JSON Array 数据将变为熟悉的关系模型，允许使用常用函数进行分析。

3. 使用 Array 函数进行过滤和计算

StarRocks 还支持 ARRAY 相关函数，可以与 JSON 函数结合使用，以实现更高效的查询。通过结合这些函数，您可以过滤、转换和聚合 JSON 数组数据。以下示例演示了如何使用这些函数

MySQL > 
WITH step1 AS (
 SELECT cast(event_data->'actions' as ARRAY<JSON>) as docs
   FROM events
)
SELECT array_filter(doc -> get_json_string(doc, 'type') = 'click', docs) as clicks
FROM step1
+---------------------------------------------------------------------------+
| clicks                                                                    |
+---------------------------------------------------------------------------+
| ['{"quantity": 1, "timestamp": "2024-03-17T10:00:00Z", "type": "click"}'] |
+---------------------------------------------------------------------------+

此外，您可以结合其他 ARRAY 函数对数组元素执行聚合计算

MySQL > 
WITH step1 AS (
 SELECT cast(event_data->'actions' as ARRAY<JSON>) as docs
   FROM events
), step2 AS (
    SELECT array_filter(doc -> get_json_string(doc, 'type') = 'click', docs) as clicks
    FROM step1
)
SELECT array_sum(
            array_map(doc -> get_json_double(doc, 'quantity'), clicks)
            ) as click_amount
FROM step2
+--------------+
| click_amount |
+--------------+
| 1.0          |
+--------------+

限制和注意事项

JSON 类型数据当前支持的最大长度为 16 MB。
ORDER BY、GROUP BY 和 JOIN 子句不支持引用 JSON 类型列。如果需要引用它们，您可以提前使用 CAST 函数将 JSON 类型列转换为其他 SQL 类型。有关具体转换方法，请参阅 JSON 类型转换。
JSON 类型列可以存在于 Duplicate Key 表、Primary Key 表和 Unique Key 表中，但不能存在于 Aggregate 表中。
JSON 类型列不支持作为 Duplicate Key 表、Primary Key 表和 Unique Key 表中的分区键、分桶键或维度列，并且不能用于 JOIN、GROUP BY 或 ORDER BY 子句中。
StarRocks 支持使用 <、<=、>、>=、=、!= 运算符查询 JSON 数据，但不支持 IN 运算符。

什么是 JSON​

使用 JSON 数据​

创建 JSON 类型列​

加载数据并存储为 JSON 类型​

查询和处理 JSON 类型数据​

JSON 函数和运算符​

JSON Array​

1. 从 JSON Array 中提取元素​

2. 使用 json_each 展开​

3. 使用 Array 函数进行过滤和计算​

限制和注意事项​

您觉得这篇文档怎么样？