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',
               json_format='array_of_records',
               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 json_format settings

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

In each of the below cases, the json_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;

records

The records setting can be used to parse newline-delimited JSON. Each line is a JSON object (or record).

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

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

array_of_records

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_of_records.json, json_format=array_of_records);

values

In the case of values, DuckDB can even handle files that are not valid JSON. Here, each row is treated as its own JSON value.

["value1","value2"]
["value3","value4"]

SELECT * FROM read_json_auto(values.json, json_format=values);

array_of_values

If the JSON file contains a JSON array that does not contain objects, array_of_values may be used.

[
    "value1",
    "value2",
    "value3"
]

SELECT * FROM read_json_auto(array_of_values.json, json_format=array_of_values);

| json | |——–| | value1 | | value2 | | value3 |

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;

More on the copy statement can be found here.