DuckDB SQL的"select结果转换为JSON"

Question

是否有一种方法可以直接将对表的SELECT查询结果转换为JSON，而无需先将其写入文件？也许可以使用duckdb的JSON扩展实现这一目标？

我也可以使用Python客户端，在那里我可以将结果转换为pandas DataFrame，然后再转换为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');

一个示例查询语句可能是 SELECT city, temp_hi FROM weather;，期望得到的JSON格式如下：

{
  "city": ["San Francisco", "Vienna"],
  "temp_hi": [50, 35]
}

所以总结一下，我正在寻找一种不需先将结果转换为Python对象就能直接生成所需JSON的方法。

matt b · Answer

如果您在使用DuckDB命令行工具（CLI），则可以在查询任何数据之前设置“mode”，如下所示：

v0.9.2 3c695d7ba9
D .mode json
D SELECT city, temp_hi from weather;
// 输出将以JSON格式展现：
[{"city": "旧金山", "temp_hi": 50},
{"city": "维也纳", "temp_hi": 35}]

若您是通过Python客户端API来操作DuckDB，在查看其源代码后发现，该API并未像CLI工具那样实现JSON模式输出功能。尽管它确实有一个名为render_mode的参数【链接指向GitHub DuckDB的部分源代码】，但这个参数目前只有两种选项可用。您可以尝试编写查询语句，并将render_mode传递给show()函数，例如con.sql("SELECT city, temp_hi FROM weather").show(render_mode=1)。另外，也可以直接使用fetchall()方法获取结果元组。

不过，如果选择Python编程方式，为什么不利用转换选项呢？比如将结果转换成Pandas DataFrame：

import duckdb

# 连接到数据库
with duckdb.connect("weather.db") as con:
    # 执行SQL查询并转换为DataFrame
    result = con.sql("SELECT city, temp_hi FROM weather").df()
    
# 将DataFrame转换为JSON格式
weather = result.to_json(orient='records')
print(weather)
# 输出：
# [{"city": "旧金山", "temp_hi": 50}, {"city": "维也纳", "temp_hi": 35}]

Matteo Italia · Answer

你可以在DuckDB中使用聚合函数list与数据类型STRUCT相结合。STRUCT可以通过大括号或者struct_pack函数进行定义：

```sql
-- 使用花括号定义STRUCT并结合list聚合函数
SELECT {city: list(city), temp_hi: list(temp_hi)}::JSON AS j FROM weather;

-- 使用struct_pack函数定义STRUCT并结合list聚合函数
SELECT struct_pack(city := list(city), temp_hi := list(temp_hi))::JSON AS j FROM weather;

这两条SQL语句会得到相同的JSON格式结果：

┌───────────────────────────────────────────────────────┐
│                           j                           │
│                         json                          │
├───────────────────────────────────────────────────────┤
│ {"city":["旧金山","维也纳"],"temp_hi":[50,35]} │
└───────────────────────────────────────────────────────┘