本教程旨在详细阐述如何在duckdb中直接将sql查询结果转换为特定格式的json对象，而无需借助中间文件写入或python客户端处理。通过利用duckdb的`list`聚合函数和`struct`数据类型，您可以灵活地将多行多列数据聚合成一个json对象，其中键为列名，值为对应列的所有数据组成的列表，从而实现高效且原生的数据转换。
在数据分析和处理中，将数据库查询结果直接转换为JSON格式是一种常见的需求，尤其是在构建API响应或与其他系统集成时。DuckDB作为一个高性能的嵌入式分析数据库，提供了强大的SQL功能，包括对JSON数据类型的原生支持。本教程将介绍两种核心方法，教您如何将SELECT语句的输出直接转换为一个聚合的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');核心概念：STRUCT 和 LIST 聚合函数
要实现将多行数据聚合为单个JSON对象，并以列名为键、列值为列表的形式呈现，我们需要结合使用DuckDB的两个关键特性：
- STRUCT 数据类型：STRUCT允许您将不同数据类型的多个值组合成一个单一的复合结构。在SQL中，这通常用于表示具有多个字段的记录或对象。DuckDB支持通过花括号 {} 或 struct_pack() 函数来定义STRUCT。
- list 聚合函数：list()是一个聚合函数，它将一个组中指定列的所有值收集到一个列表中。例如，list(city)将返回所有行的城市名组成的列表。
通过将list()聚合函数应用于STRUCT的每个字段，我们可以构建出所需的JSON结构。
方法一：使用花括号 {} 定义 STRUCT
这是最直观和简洁的方法，通过在SELECT语句中使用花括号来定义一个STRUCT，然后将其显式转换为JSON类型。
SELECT {city: list(city), temp_hi: list(temp_hi)}::JSON AS j FROM weather;代码解析：
- {city: list(city), temp_hi: list(temp_hi)}：这定义了一个匿名的STRUCT。
- city: list(city)：STRUCT中的第一个字段名为city，其值是weather表中所有city值聚合而成的列表。
- temp_hi: list(temp_hi)：STRUCT中的第二个字段名为temp_hi，其值是weather表中所有temp_hi值聚合而成的列表。
- ::JSON：这是一个类型转换操作符，将整个STRUCT结构强制转换为JSON数据类型。DuckDB的JSON扩展会自动处理这种转换，将STRUCT的字段名作为JSON对象的键，字段值作为JSON对象的值。
- AS j：为最终的JSON结果列指定别名j。
执行结果：
┌─────────────────────────────────────────────────────────────┐
│ j │
│ json │
├─────────────────────────────────────────────────────────────┤
│ {"city":["San Francisco","Vienna","London"],"temp_hi":[50,35,20]} │
└─────────────────────────────────────────────────────────────┘方法二：使用 struct_pack() 函数
struct_pack()函数提供了另一种更显式的方式来创建STRUCT。它的语法是struct_pack(field_name := value, ...)。
SELECT struct_pack(city := list(city), temp_hi := list(temp_hi))::JSON AS j FROM weather;
代码解析：
- struct_pack(city := list(city), temp_hi := list(temp_hi))：这使用struct_pack()函数创建了一个STRUCT。
- city := list(city)：定义了名为city的字段，其值是所有city值的列表。
- temp_hi := list(temp_hi)：定义了名为temp_hi的字段，其值是所有temp_hi值的列表。
- ::JSON 和 AS j 的作用与方法一相同。
执行结果：
与方法一完全相同，这两种方式在功能上是等价的，您可以根据个人偏好选择使用。
┌─────────────────────────────────────────────────────────────┐
│ j │
│ json │
├─────────────────────────────────────────────────────────────┤
│ {"city":["San Francisco","Vienna","London"],"temp_hi":[50,35,20]} │
└─────────────────────────────────────────────────────────────┘注意事项
- JSON扩展：虽然DuckDB内置了JSON支持，但确保您的DuckDB环境能够处理::JSON类型转换。通常情况下，这是默认启用的。
- 数据聚合行为：这种方法会将所有符合查询条件的行聚合成一个单一的JSON对象。如果您的查询结果集非常大，生成的JSON对象也可能非常大，这可能会对内存或网络传输造成影响。
- JSON结构：生成的JSON结构是固定的，即顶级是一个JSON对象，其键是您在STRUCT中定义的字段名，值是对应列所有数据的列表。如果需要每行生成一个JSON对象（即JSON数组），则需要使用不同的方法，例如to_json(STRUCT_PACK(...))结合ARRAY_AGG或json_group_array（如果DuckDB支持此类聚合）。然而，本教程专注于生成问题中描述的特定聚合JSON格式。
- 错误处理：如果list()聚合的列包含NULL值，这些NULL值也会被包含在生成的列表中。请根据您的需求在聚合前进行WHERE过滤或使用COALESCE等函数处理NULL值。
总结
通过结合使用DuckDB的list聚合函数和STRUCT数据类型（无论是通过花括号{}还是struct_pack()函数），您可以高效且直接地将SQL查询结果转换为特定的JSON对象格式。这种方法避免了中间文件操作或外部编程语言的介入，极大地简化了数据处理流程，并提高了性能。掌握这一技巧，将使您在DuckDB中处理和输出JSON数据时更加得心应手。
