DuckDB SQL查询结果转换为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格式的方法。

jgauffin · Answer

您的问题中没有明确指出在何处执行查询以及期望在哪里获取查询结果。

如果您正在使用duckdb命令行工具（CLI），则可以在执行任何查询前设置模式，如下所示：

  v0.9.2 3c695d7ba9
  D .mode json
  D SELECT city, temp_hi from weather;
  [{"city":"San Francisco","temp_hi":50},{"city":"Vienna","temp_hi":35}]

若您是通过Python客户端API来使用duckdb，从源代码检查来看，它似乎并未像CLI工具那样实现JSON模式输出功能。虽然它有一个名为render_mode的参数，但它只有两种选项可供选择。您可以尝试编写查询并将其传递给show()函数，如 con.sql("SELECT city, temp_hi form weather").show(render_mode=1)，不过请注意，render_mode 参数在此情况下可能并不能直接用于JSON输出。您还可以使用 fetchall() 函数，它会返回一个包含结果的元组。

无论如何，如果您采用Python编程方式，为什么不利用现成的数据转换选项呢？例如，将结果转换为pandas DataFrame：

import duckdb

with duckdb.connect("weather.db") as con:
    result = con.sql("SELECT city, temp_hi from weather").df()
weather = result.to_json(orient="records")
print(weather)
# 输出：[{"city": "San Francisco", "temp_hi": 50},{"city": "Vienna", "temp_hi": 35}]

trojanfoe · Answer

可以使用 list聚合函数配合 STRUCT类型来达到目的。STRUCT可以通过花括号或struct_pack函数进行定义：

SELECT {city: list(city), temp_hi: list(temp_hi)}::JSON AS j FROM weather;
SELECT struct_pack(city := list(city), temp_hi := list(temp_hi))::JSON AS j FROM weather;

这两种写法都将产生同样的结果：

┌───────────────────────────────────────────────────────┐
│                           j                           │
│                         json                          │
├───────────────────────────────────────────────────────┤
│ {"city":["San Francisco","Vienna"],"temp_hi":[50,35]} │
└───────────────────────────────────────────────────────┘