Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

duckdb SQL select result to JSON

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.

Tags:

2

Answers


  1. 0

    It is unclear where are you writing the query and where are you expecting the output of the query.

    • In case you are using the duckdb CLI tool, you can set "mode" before querying anything like this

      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}]

    • If its using the Python client API, from check the source code, it appears it doesn’t have implemented a JSON mode like the CLI tool does. It does have a render_mode parameter but it only has 2 options. You can test it by writing a query and passing the render_mode to the show() function. like con.sql("SELECT city, temp_hi form weather").show(render_mode=1). You can just use the fetchall() which returns a tuple of the results

      Anyways if going with the Python route, why not use the conversion options? such as a 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}]
    Login or Signup to reply.
  2. 0

    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]} │
└───────────────────────────────────────────────────────┘
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search