[FIXED] duckdb SQL select result to JSON

Issue

Is there a way to convert the result of a SELECT query on a table directly to JSON without writing it to a file? Perhaps with the JSON extension of duckdb?

I could also use the python client, where I'd convert the result to a pandas dataframe and then to JSON, but I figured there should be a more direct way.

Example:

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');

An example query would be "SELECT city, temp_hi FROM weather;", and the desired json would look like:

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

So to recap, I'm looking for way to create the desired JSON directly, without converting the result to a python object first.

Solution

You can use the list aggregate function with a STRUCT. The latter can be defined using curly braces or 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;

Both of them will produce the same result:

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

Answered By - Gabor Szarnyas