本文详细介绍了在 duckdb 中如何直接将 sql 查询结果转换为 json 格式，无需借助外部编程语言或中间对象转换。核心方法是利用 duckdb 的 `list` 聚合函数和 `struct` 数据类型，通过 sql 语句构建结构化数据并直接转换为 json。教程提供了两种实现方式：使用花括号 `{}` 定义结构体和使用 `struct_pack()` 函数，并附带了完整的示例代码和预期输出，帮助用户高效地在数据库层完成数据格式转换。
DuckDB SQL 查询结果直接转换为 JSON
在数据处理和应用开发中，将数据库查询结果转换为 JSON 格式是一种常见需求。虽然许多编程语言客户端（如 Python）可以先将查询结果转换为数据帧或对象，再序列化为 JSON，但 DuckDB 提供了更为直接和高效的 SQL 解决方案，允许用户在数据库内部直接完成这一转换，从而减少数据传输和处理的开销。本文将详细介绍如何利用 DuckDB 的 LIST 聚合函数和 STRUCT 数据类型来实现这一目标。
核心概念
要将表格的列数据聚合成 JSON 数组，并以键值对的形式组织，我们需要理解 DuckDB 中的两个关键功能：
- LIST 聚合函数：此函数可以将指定列的所有值聚合到一个列表中。例如，list(column_name) 会返回该列所有行的值组成的列表。
- STRUCT 数据类型：结构体（STRUCT）是一种复合数据类型，允许将多个不同类型的值打包成一个单一的结构。在 DuckDB SQL 中，可以通过两种方式定义结构体：使用花括号 {} 或 struct_pack() 函数。结构体非常适合表示 JSON 对象中的键值对。
结合这两者，我们可以先用 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, 20, 5.5, '2023-03-15');方法一：使用花括号 {} 定义结构体
这是定义结构体最简洁的方式。我们通过 {key: value, ...} 的语法来创建一个结构体，其中 key 是 JSON 字段名，value 是通过 list() 聚合函数得到的列数据列表。最后，使用 ::JSON 进行类型转换，将其转换为 JSON 格式。
假设我们想将 city 和 temp_hi 列转换为 JSON，格式为 {"city": ["San Francisco", "Vienna", "London"], "temp_hi": [50, 35, 20]}。
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, 20]。
- {city: ..., temp_hi: ...}：定义一个结构体，其中 city 是键，list(city) 的结果是其值；temp_hi 是键，list(temp_hi) 的结果是其值。
- ::JSON：将生成的结构体强制转换为 JSON 数据类型。
方法二：使用 struct_pack() 函数定义结构体
struct_pack() 函数提供了另一种更显式地创建结构体的方式。它的语法是 struct_pack(key1 := value1, key2 := value2, ...)。这种方式在某些情况下可能更具可读性，尤其是在字段名与列名不同时。
同样，我们将 city 和 temp_hi 列转换为 JSON：
SELECT struct_pack(city := list(city), temp_hi := list(temp_hi))::JSON AS j FROM weather;
代码解析：
- struct_pack(...)：调用 struct_pack 函数来创建结构体。
- city := list(city)：指定结构体中的一个字段，键为 city，值为 list(city) 的结果。
- temp_hi := list(temp_hi)：指定结构体中的另一个字段，键为 temp_hi，值为 list(temp_hi) 的结果。
- ::JSON：将生成的结构体强制转换为 JSON 数据类型。
预期输出
无论采用哪种方法，上述查询都将产生相同的 JSON 输出结果：
┌───────────────────────────────────────────────────────────────────┐
│ j │
│ json │
├───────────────────────────────────────────────────────────────────┤
│ {"city":["San Francisco","Vienna","London"],"temp_hi":[50,35,20]} │
└───────────────────────────────────────────────────────────────────┘总结与注意事项
- 直接性与效率：这种方法直接在 SQL 层完成 JSON 转换，避免了数据在数据库和应用程序之间来回传输，提高了效率，尤其适用于大数据集。
- 灵活性：你可以根据需要选择任意列进行聚合，并将其包含在最终的 JSON 结构中。
- JSON 结构：这种方法生成的 JSON 结构是每个键对应一个值数组。如果需要每行数据作为一个独立的 JSON 对象，然后将所有对象放入一个 JSON 数组中，则需要结合 to_json 函数和 array_agg 函数，这属于另一种高级用法。
- 错误处理：确保聚合的列数据类型与 JSON 兼容。DuckDB 会自动处理基本类型的转换。
- DuckDB 版本：确保您的 DuckDB 版本支持 LIST 聚合函数和 STRUCT 类型以及 ::JSON 转换。这些功能在较新版本的 DuckDB 中是标准配置。
通过掌握上述技术，开发者可以更有效地利用 DuckDB 的强大功能，直接在数据库层处理和格式化数据，从而简化应用程序逻辑并提升整体性能。
