DuckDB SQL 查询结果直接转换为列式 JSON 教程

本教程详细讲解如何在 duckdb 中，利用 sql 的 `list` 聚合函数与 `struct` 数据类型，将查询结果直接聚合成一个列式 json 对象，避免了将数据导出到 python 等外部环境进行二次处理。文章通过具体代码示例，演示了两种构建 `struct` 并转换为 json 的方法，帮助用户高效地在数据库层完成数据格式转换。

在现代数据处理流程中，将 SQL 查询结果转换为 JSON 格式是一种常见的需求，尤其是在构建 API 响应或与其他系统集成时。DuckDB 作为一款高性能的嵌入式分析型数据库，提供了强大的 SQL 功能，能够直接在数据库内部完成这种转换，从而简化开发流程并提升效率。本教程将重点介绍如何将查询结果聚合成一个列式 JSON 对象，即 JSON 对象的键对应查询的列名，值是该列所有数据的列表。

DuckDB 中 JSON 转换的核心概念

要实现将查询结果聚合成列式 JSON，我们需要利用 DuckDB 的两个核心功能：STRUCT 数据类型和 list 聚合函数。

1. STRUCT 数据类型：STRUCT 允许您将多个不同数据类型的字段组合成一个单一的复合数据类型。它类似于其他编程语言中的结构体或对象。在 DuckDB 中，STRUCT 可以通过两种主要方式定义：

   • 使用花括号 {} 直接定义，例如 {key1: value1, key2: value2}。
   • 使用 struct_pack() 函数，例如 struct_pack(key1 := value1, key2 := value2)。 这两种方式都允许您创建具有命名字段的复杂数据结构。

2. 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。

标签： python js json 编程语言 聚合函数 隐式转换