本教程详细讲解如何在 duckdb 中，利用 sql 的 `list` 聚合函数与 `Struct` 数据类型，将查询结果直接聚合成一个列式 json 对象，避免了将数据导出到 python 等外部环境进行二次处理。文章通过具体代码示例，演示了两种构建 `struct` 并转换为 json 的方法，帮助用户高效地在数据库层完成数据格式转换。
在现代数据处理流程中，将 SQL 查询结果转换为 jsON 格式是一种常见的需求，尤其是在构建 API 响应或与其他系统集成时。DuckDB 作为一款高性能的嵌入式分析型数据库，提供了强大的 SQL 功能，能够直接在数据库内部完成这种转换，从而简化开发流程并提升效率。本教程将重点介绍如何将查询结果聚合成一个列式 json 对象，即 JSON 对象的键对应查询的列名，值是该列所有数据的列表。
DuckDB 中 JSON 转换的核心概念
要实现将查询结果聚合成列式 JSON，我们需要利用 DuckDB 的两个核心功能：STRUCT 数据类型和 list 聚合函数。
-
STRUCT 数据类型：STRUCT 允许您将多个不同数据类型的字段组合成一个单一的复合数据类型。它类似于其他编程语言中的结构体或对象。在 DuckDB 中，STRUCT 可以通过两种主要方式定义：
- 使用花括号 {} 直接定义，例如 {key1: value1, key2: value2}。
- 使用 struct_pack() 函数，例如 struct_pack(key1 := value1, key2 := value2)。 这两种方式都允许您创建具有命名字段的复杂数据结构。
-
list 聚合函数：list 是一个聚合函数，它的作用是将指定列的所有值收集到一个列表中。例如，list(column_name) 将返回 column_name 列中所有行的值构成的列表。这正是我们实现列式 JSON 中“值是列表”这一需求的关键。
结合 STRUCT 和 list，我们可以先将每列的数据聚合成一个列表，然后将这些列表作为 STRUCT 的字段值，最后将整个 STRUCT 强制转换为 JSON 类型。
准备示例数据
为了演示，我们首先创建一个 weather 表并插入一些示例数据：
CREATE TABLE weather ( city VARCHAR, temp_lo INTEGER, -- minimum temperature on a day temp_hi INTEGER, -- maximum temperature on a day prcp REAL, date DATE ); INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); INSERT INTO weather VALUES ('Vienna', -5, 35, 10, '2000-01-01'); INSERT INTO weather VALUES ('London', 10, 15, 0.5, '2023-03-15');我们的目标是将 city 和 temp_hi 两列的数据聚合成如下所示的 JSON 格式：
{"city": ["San Francisco", "Vienna", "London"], "temp_hi": [50, 35, 15]}方法一：使用花括号 {} 定义 STRUCT
这是最简洁直观的方法，通过在 select 语句中使用花括号来定义 STRUCT。
SELECT {city: list(city), temp_hi: list(temp_hi)}::JSON AS j FROM weather;代码解释：
- list(city)：将 city 列的所有值聚合成一个列表 [“San Francisco”, “Vienna”, “London”]。
- list(temp_hi)：将 temp_hi 列的所有值聚合成一个列表 [50, 35, 15]。
- {city: …, temp_hi: …}：这定义了一个 STRUCT，其中包含两个字段：city (值为 list(city) 的结果) 和 temp_hi (值为 list(temp_hi) 的结果)。
- ::JSON：这是一个类型转换操作符，将前面生成的 STRUCT 强制转换为 JSON 类型。DuckDB 会自动将 STRUCT 映射为 JSON 对象。
- AS j：为最终的 JSON 结果列指定别名 j。
执行结果：
┌─────────────────────────────────────────────────────────────────────────┐ │ j │ │ json │ ├─────────────────────────────────────────────────────────────────────────┤ │ {"city":["San Francisco","Vienna","London"],"temp_hi":[50,35,15]} │ └─────────────────────────────────────────────────────────────────────────┘方法二：使用 struct_pack() 函数
struct_pack() 函数提供了另一种更明确的方式来构建 STRUCT，特别适用于字段名可能与关键字冲突或需要更清晰的命名参数时。
SELECT struct_pack(city := list(city), temp_hi := list(temp_hi))::JSON AS j FROM weather;代码解释：
- struct_pack(…)：这是一个函数调用，用于创建 STRUCT。
- city := list(city)：使用命名参数语法 key := value，将 list(city) 的结果赋值给 STRUCT 中的 city 字段。
- temp_hi := list(temp_hi)：同理，将 list(temp_hi) 的结果赋值给 STRUCT 中的 temp_hi 字段。
- ::JSON 和 AS j 的作用与方法一相同。
执行结果：
此方法将产生与方法一完全相同的 JSON 输出。
┌─────────────────────────────────────────────────────────────────────────┐ │ j │ │ json │ ├─────────────────────────────────────────────────────────────────────────┤ │ {"city":["San Francisco","Vienna","London"],"temp_hi":[50,35,15]} │ └─────────────────────────────────────────────────────────────────────────┘注意事项与最佳实践
- 明确目标 JSON 结构： 本教程侧重于生成列式 JSON。如果您的目标是生成行式 JSON（即每行数据作为一个独立的 JSON 对象，然后将所有行对象聚合为一个 JSON 数组），则需要使用 TO_JSON() 结合 JSON_GROUP_ARRAY() 或 JSON_AGG() 等函数。选择正确的方法取决于您所需的 JSON 输出格式。
- 数据类型转换： 始终记得使用 ::JSON 将 STRUCT 显式转换为 JSON 类型。虽然 DuckDB 在某些上下文中可能进行隐式转换，但显式转换能确保结果符合预期。
- 选择需要转换的列： 在 list() 函数中只包含您希望出现在最终 JSON 中的列。如果您有大量列，但只需要其中几列转换为 JSON，这种方式非常高效。
- 性能考量： 对于非常大的数据集，list 聚合函数会收集所有数据到内存中。虽然 DuckDB 经过优化，但在极端情况下仍需注意内存使用。如果数据量巨大且不适合单次聚合，可能需要考虑分批处理或使用其他流式处理方法。
- 嵌套结构： 如果需要更复杂的嵌套 JSON 结构，可以在 STRUCT 内部嵌套其他 STRUCT 或 list。例如，{key1: list({sub_key1: colA, sub_key2: colB})} 可以创建包含对象列表的结构。
总结
DuckDB 提供了强大而灵活的 SQL 功能，允许开发者直接在数据库层将查询结果聚合成复杂的 JSON 结构。通过巧妙地结合 list 聚合函数和 STRUCT 数据类型（无论是通过花括号 {} 还是 struct_pack() 函数），我们可以轻松地生成列式 JSON 输出，从而简化数据处理流程，提高效率，并减少对外部编程语言的依赖。掌握这些技巧，将使您在 DuckDB 中处理和导出结构化数据时更加得心应手。