DuckDB’s CLI allows you to output query results in different formats, including NDJSON (Newline Delimited JSON).

NDJSON is similar to JSON, except that with NDJSON, each line contains its own self-contained JSON document.

This article shows you how to check your current output mode, and then change it to NDJSON.

Checking the Current Output Mode

To check your current output mode in the DuckDB CLI, use the .mode command without any arguments:

.mode

This will display your current output mode, which is typically duckbox by default.

Here’s an example of the output of this command:

current output mode: duckbox

That’s the default output mode.

Changing to NDJSON Mode

You might assume that switching to NDJSON mode would be a simple matter of using .mode ndjson. But you’d be wrong. At least, you’d be wrong in the current version of DuckDB (at the time of this writing). You would actually need to use jsonlines as the mode name.

So, to switch to NDJSON output mode, use the .mode command followed by jsonlines:

.mode jsonlines

The jsonlines mode is specifically for NDJSON output.

Verifying the Change

Let’s verify the change by running a simple query:

-- Create a sample table
CREATE TABLE users(id INTEGER, name VARCHAR, city VARCHAR);

-- Insert some sample data
INSERT INTO users VALUES 
    (1, 'Azur', 'New York'),
    (2, 'Beeble', 'San Francisco'),
    (3, 'Chantalope', 'Chicago');

-- Query the table
SELECT * FROM users;

The output will now be in NDJSON format, with each row appearing as a separate JSON object on its own line:

{"id":1,"name":"Azur","city":"New York"}
{"id":2,"name":"Beeble","city":"San Francisco"}
{"id":3,"name":"Chantalope","city":"Chicago"}

We can see that the query result was returned in NDJSON format.

This is similar, but different to JSON. Let’s quickly do a JSON example to compare:

.mode json
SELECT * FROM users;

Output:

[{"id":1,"name":"Azur","city":"New York"},
{"id":2,"name":"Beeble","city":"San Francisco"},
{"id":3,"name":"Chantalope","city":"Chicago"}]

The only difference is that the JSON document starts and ends in square brackets, and each enclosed document is separated by a comma. This makes the whole thing a JSON document.

The NDJSON example on the other hand, consists of three separate JSON documents, separated by a new line character (\n). None of these JSON documents are enclosed in an outer document.

This means that the NDJSON document wouldn’t validate against a JSON validator. But that won’t be a problem if it’s your intention to use NDJSON and you have reason to use it over JSON.

Switch Back to the Original Mode

To switch back to the default duckbox mode at any time, simply run:

.mode duckbox

You can choose any other mode of course.

Available Modes

You can run the following command to get a list of valid modes:

.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