本教程详细介绍了如何在duckdb中将sql查询结果直接转换为特定格式的json字符串，而无需通过文件或python中间层。核心方法是利用duckdb的struct数据类型结合list聚合函数，将指定列的数据聚合成列表，然后构建一个结构体并将其转换为json格式，从而高效地生成符合要求的json输出。
引言：DuckDB中SQL结果到JSON的直接转换
在数据处理和分析中，将SQL查询结果直接转换为JSON格式是一种常见的需求，尤其是在与API接口或前端应用交互时。DuckDB作为一款高性能的嵌入式OLAP数据库，提供了强大的SQL功能，并且支持JSON数据类型。本教程将深入探讨如何利用DuckDB的内置功能，特别是STRUCT数据类型和聚合函数，实现将查询结果高效地转换为特定JSON结构，即以列名为键、列数据列表为值的JSON对象。
环境准备与示例数据
首先，我们需要创建一个示例表并插入一些数据，以便演示JSON转换过程。
-- 创建天气表
CREATE TABLE weather (
city VARCHAR,
temp_lo INTEGER, -- 每日最低温度
temp_hi INTEGER, -- 每日最高温度
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');理解目标JSON结构
我们期望的JSON输出格式是：
{"city": ["San Francisco", "Vienna"], "temp_hi": [50, 35]}这种格式的特点是，每个键对应一个SQL查询中的列名，而其值是一个包含该列所有行数据的列表。要实现这种“列转列表”的聚合方式，我们需要使用DuckDB的聚合函数。
核心方法：list聚合函数与STRUCT类型
DuckDB提供了list聚合函数，它可以将一列中的所有值收集到一个列表中。而STRUCT数据类型则允许我们将多个不同类型的值组合成一个复合结构，这非常适合构建我们目标JSON的内部结构。
1. 使用list函数聚合列数据
为了将city和temp_hi两列的数据分别聚合成列表，我们可以这样做：
SELECT list(city) AS cities, list(temp_hi) AS high_temps FROM weather;
这将产生两列结果，每列都是一个列表：
┌───────────────────────────────┬───────────────────────────┐ │ cities │ high_temps │ │ varchar[] │ integer[] │ ├───────────────────────────────┼───────────────────────────┤ │ [San Francisco, Vienna] │ [50, 35] │ └───────────────────────────────┴───────────────────────────┘
2. 构建STRUCT结构
接下来，我们需要将这些聚合后的列表组合成一个结构体，其中键是列名（或我们希望的JSON键），值是对应的列表。DuckDB提供了两种方式来定义STRUCT：
-
使用花括号 {} 语法：
SELECT {city: list(city), temp_hi: list(temp_hi)} FROM weather; -
使用 struct_pack() 函数：
SELECT struct_pack(city := list(city), temp_hi := list(temp_hi)) FROM weather;
这两种方法都会生成一个STRUCT类型的结果，其内部结构与我们目标JSON非常相似：
┌──────────────────────────────────────────────────────────┐ │ struct │ │ struct │ ├──────────────────────────────────────────────────────────┤ │ {'city': ['San Francisco', 'Vienna'], 'temp_hi': [50, 35]} │ └──────────────────────────────────────────────────────────┘
3. 将STRUCT转换为JSON
最后一步是将这个STRUCT类型的结果显式地转换为JSON类型。DuckDB支持类型转换操作符::。
-- 方法一：使用花括号定义STRUCT
SELECT {city: list(city), temp_hi: list(temp_hi)}::JSON AS j FROM weather;
-- 方法二：使用struct_pack()函数定义STRUCT
SELECT struct_pack(city := list(city), temp_hi := list(temp_hi))::JSON AS j FROM weather;无论使用哪种方法，最终都将得到我们期望的JSON输出：
┌───────────────────────────────────────────────────────┐
│ j │
│ json │
├───────────────────────────────────────────────────────┤
│ {"city":["San Francisco","Vienna"],"temp_hi":[50,35]} │
└───────────────────────────────────────────────────────┘通过这种方式，我们成功地将SQL查询结果直接转换为了所需的JSON格式，而无需任何外部工具或中间文件。
注意事项与最佳实践
- DuckDB JSON扩展：虽然DuckDB通常默认支持JSON功能，但在某些特定环境中，可能需要确保JSON扩展已加载。如果遇到JSON相关的错误，可以尝试运行 INSTALL json; LOAD json;。
- 性能考量：将整个查询结果聚合到一个单一的JSON字符串中，对于非常大的数据集可能会消耗大量内存和CPU资源。这种方法最适用于结果集大小适中或需要一次性获取所有聚合数据的场景。
- JSON结构灵活性：本教程展示的是一种特定的JSON结构（列名作为键，列数据列表作为值）。如果需要生成“每行一个JSON对象”的数组结构（例如 [{"city": "SF", "temp_hi": 50}, {"city": "Vienna", "temp_hi": 35}]），则需要采用不同的方法，通常是先将每行转换为STRUCT，再转换为JSON，最后使用list聚合这些JSON对象。
- 错误处理：在实际应用中，考虑数据中可能存在的NULL值对JSON生成的影响。DuckDB的JSON转换会适当地处理NULL值（通常转换为JSON null）。
总结
DuckDB通过其强大的类型系统和聚合功能，为SQL查询结果直接转换为JSON提供了高效且灵活的解决方案。通过结合list聚合函数和STRUCT数据类型，并最终进行::JSON类型转换，开发者可以轻松地生成符合特定需求的JSON输出，从而简化数据处理流程并提高应用程序的集成效率。理解并熟练运用这些功能，将使您在DuckDB的数据操作中更加游刃有余。
