Examples

-- read a JSON file from disk, auto-infer options
SELECT * FROM 'todos.json';
-- read_json with custom options
SELECT *
FROM read_json('todos.json',
               format = 'array',
               columns = {userId: 'UBIGINT',
                         id: 'UBIGINT',
                         title: 'VARCHAR',
                         completed: 'BOOLEAN'});

# read a JSON file from stdin, auto-infer options
cat data/json/todos.json | duckdb -c "SELECT * FROM read_json_auto('/dev/stdin')"

-- read a JSON file into a table
CREATE TABLE todos (userId UBIGINT, id UBIGINT, title VARCHAR, completed BOOLEAN);
COPY todos FROM 'todos.json';
-- alternatively, create a table without specifying the schema manually
CREATE TABLE todos AS SELECT * FROM 'todos.json';

-- write the result of a query to a JSON file
COPY (SELECT * FROM todos) TO 'todos.json';

JSON Loading

JSON is an open standard file format and data interchange format that uses human-readable text to store and transmit data objects consisting of attribute–value pairs and arrays (or other serializable values). While it is not a very efficient format for tabular data, it is very commonly used, especially as a data interchange format.

The DuckDB JSON reader can automatically infer which configuration flags to use by analyzing the JSON file. This will work correctly in most situations, and should be the first option attempted. In rare situations where the JSON reader cannot figure out the correct configuration, it is possible to manually configure the JSON reader to correctly parse the JSON file.

Below are parameters that can be passed in to the JSON reader.

Parameters

When using read_json_auto, every parameter that supports auto-detection is enabled.

Examples of Format Settings

The JSON extension can attempt to determine the format of a JSON file when setting format to auto. Here are some example JSON files and the corresponding format settings that should be used.

In each of the below cases, the format setting was not needed, as DuckDB was able to infer it correctly, but it is included for illustrative purposes. A query of this shape would work in each case:

SELECT *
FROM filename.json;

Format: newline_delimited

With format = 'newline_delimited' newline-delimited JSON can be parsed. Each line is a JSON.

{"key1":"value1", "key2": "value1"}
{"key1":"value2", "key2": "value2"}
{"key1":"value3", "key2": "value3"}

SELECT *
FROM read_json_auto('records.json', format = 'newline_delimited');

Format: array

If the JSON file contains a JSON array of objects (pretty-printed or not), array_of_objects may be used.

[
    {"key1":"value1", "key2": "value1"},
    {"key1":"value2", "key2": "value2"},
    {"key1":"value3", "key2": "value3"}
]

SELECT *
FROM read_json_auto('array.json', format = 'array');

Format: unstructured

If the JSON file contains JSON that is not newline-delimited or an array, unstructured may be used.

{
    "key1":"value1",
    "key2":"value1"
}
{
    "key1":"value2",
    "key2":"value2"
}
{
    "key1":"value3",
    "key2":"value3"
}

SELECT *
FROM read_json_auto('unstructured.json', format = 'unstructured');

Examples of Records Settings

The JSON extension can attempt to determine whether a JSON file contains records when setting records = auto. When records = true, the JSON extension expects JSON objects, and will unpack the fields of JSON objects into individual columns.

Continuing with the same example file from before:

{"key1":"value1", "key2": "value1"}
{"key1":"value2", "key2": "value2"}
{"key1":"value3", "key2": "value3"}

SELECT *
FROM read_json_auto('records.json', records = true);

When records = false, the JSON extension will not unpack the top-level objects, and create STRUCTs instead:

SELECT *
FROM read_json_auto('records.json', records = false);

This is especially useful if we have non-object JSON, for example:

[1, 2, 3]
[4, 5, 6]
[7, 8, 9]

SELECT *
FROM read_json_auto('arrays.json', records = false);

Writing

The contents of tables or the result of queries can be written directly to a JSON file using the COPY statement. See the COPY documentation for more information.

read_json_auto Function

The read_json_auto is the simplest method of loading JSON files: it automatically attempts to figure out the correct configuration of the JSON reader. It also automatically deduces types of columns.

SELECT *
FROM read_json_auto('todos.json')
LIMIT 5;

The path can either be a relative path (relative to the current working directory) or an absolute path.

We can use read_json_auto to create a persistent table as well:

CREATE TABLE todos AS
    SELECT *
    FROM read_json_auto('todos.json');
DESCRIBE todos;

If we specify the columns, we can bypass the automatic detection. Note that not all columns need to be specified:

SELECT *
FROM read_json_auto('todos.json',
                    columns = {userId: 'UBIGINT',
                               completed: 'BOOLEAN'});

Multiple files can be read at once by providing a glob or a list of files. Refer to the multiple files section for more information.

COPY Statement

The COPY statement can be used to load data from a JSON file into a table. For the COPY statement, we must first create a table with the correct schema to load the data into. We then specify the JSON file to load from plus any configuration options separately.

CREATE TABLE todos (userId UBIGINT, id UBIGINT, title VARCHAR, completed BOOLEAN);
COPY todos FROM 'todos.json';
SELECT * FROM todos LIMIT 5;

For more details, see the page on the COPY statement.