DuckDB is a lightweight, fast database management system designed for analytics and embedded use cases. Its versatility makes it an excellent choice for developers and data analysts.

One useful feature of DuckDB is the ability to output query results in different formats, such as JSON, directly from the command-line interface (CLI). By default, the DuckDB CLI uses the duckbox output mode for query results (which outputs the results in a table-like format), but we can change that.

In this article, we’ll walk through the steps to output query results as JSON when using the DuckDB CLI.

Check the Current Output Mode

Before changing the output mode, you may want to check the current setting.

Example:

.mode

Sample output:

current output mode: duckbox

The current output mode is duckbox, which is the default mode. This is not JSON, and so we’ll change it to JSON in the next step.

Change the Output Mode

You can change the output mode in DuckDB CLI using the .mode command followed by the desired mode.

To change it to JSON, use the following:

.mode json

That sets the output mode to json.

Verify the Change

We can verify the new output mode by running a query.

Example:

SELECT * FROM t1;

Output:

[{"c1":1,"c2":"Ashbor"},
{"c1":2,"c2":"Bromley"},
{"c1":3,"c2":"Calchum"}]

As expected, the query results are output in JSON format.

Other Output Modes

The DuckDB CLI supports various other output modes. To see a list of output modes, pass mode to the .help command:

.help mode

Output:

.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     duckbox   Tables with extensive features
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     jsonlines Results in a NDJSON
     latex     LaTeX tabular environment code
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements
     trash     No output

This shows the syntax for the .mode command, as well as a list of supported modes.