DuckDB SQL查询结果高效转换为JSON对象教程

本教程旨在详细阐述如何在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的两个关键特性：

1. STRUCT 数据类型：STRUCT允许您将不同数据类型的多个值组合成一个单一的复合结构。在SQL中，这通常用于表示具有多个字段的记录或对象。DuckDB支持通过花括号 {} 或 struct_pack() 函数来定义STRUCT。
2. 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。

执行结果：

Natural Language Playlist

探索语言和音乐之间丰富而复杂的关系，并使用 Transformer 语言模型构建播放列表。

67

查看详情

┌─────────────────────────────────────────────────────────────┐
│                              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]} │
└─────────────────────────────────────────────────────────────┘

注意事项

1. JSON扩展：虽然DuckDB内置了JSON支持，但确保您的DuckDB环境能够处理::JSON类型转换。通常情况下，这是默认启用的。
2. 数据聚合行为：这种方法会将所有符合查询条件的行聚合成一个单一的JSON对象。如果您的查询结果集非常大，生成的JSON对象也可能非常大，这可能会对内存或网络传输造成影响。
3. JSON结构：生成的JSON结构是固定的，即顶级是一个JSON对象，其键是您在STRUCT中定义的字段名，值是对应列所有数据的列表。如果需要每行生成一个JSON对象（即JSON数组），则需要使用不同的方法，例如to_json(STRUCT_PACK(...))结合ARRAY_AGG或json_group_array（如果DuckDB支持此类聚合）。然而，本教程专注于生成问题中描述的特定聚合JSON格式。
4. 错误处理：如果list()聚合的列包含NULL值，这些NULL值也会被包含在生成的列表中。请根据您的需求在聚合前进行WHERE过滤或使用COALESCE等函数处理NULL值。

总结

通过结合使用DuckDB的list聚合函数和STRUCT数据类型（无论是通过花括号{}还是struct_pack()函数），您可以高效且直接地将SQL查询结果转换为特定的JSON对象格式。这种方法避免了中间文件操作或外部编程语言的介入，极大地简化了数据处理流程，并提高了性能。掌握这一技巧，将使您在DuckDB中处理和输出JSON数据时更加得心应手。

以上就是DuckDB SQL查询结果高效转换为JSON对象教程的详细内容，更多请关注php中文网其它相关文章！