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.

The read_json Function

The read_json 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. In the following example, we use the todos.json file,

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

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

CREATE TABLE todos AS
    SELECT *
    FROM read_json('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(
    '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.

Functions for Reading JSON Objects

The following table functions are used to read JSON:

Parameters

These functions have the following parameters:

The format parameter specifies how to read the JSON from a file. With 'unstructured', the top-level JSON is read, e.g. for birds.json:

{
  "duck": 42
}
{
  "goose": [1, 2, 3]
}

FROM read_json('birds.json', format = 'unstructured');

will result in two objects being read:

┌──────────────────────────────┐
│             json             │
│             json             │
├──────────────────────────────┤
│ {\n    "duck": 42\n}         │
│ {\n    "goose": [1, 2, 3]\n} │
└──────────────────────────────┘

With 'newline_delimited', NDJSON is read, where each JSON is separated by a newline (\n), e.g., for birds-nd.json:

{"duck": 42}
{"goose": [1, 2, 3]}

FROM read_json_objects('birds-nd.json', format = 'newline_delimited');

will also result in two objects being read:

┌──────────────────────┐
│         json         │
│         json         │
├──────────────────────┤
│ {"duck": 42}         │
│ {"goose": [1, 2, 3]} │
└──────────────────────┘

With 'array', each array element is read, e.g., for birds-array.json:

[
    {
        "duck": 42
    },
    {
        "goose": [1, 2, 3]
    }
]

FROM read_json_objects('birds-array.json', format = 'array');

will again result in two objects being read:

┌──────────────────────────────────────┐
│                 json                 │
│                 json                 │
├──────────────────────────────────────┤
│ {\n        "duck": 42\n    }         │
│ {\n        "goose": [1, 2, 3]\n    } │
└──────────────────────────────────────┘

Functions for Reading JSON as a Table

DuckDB also supports reading JSON as a table, using the following functions:

Parameters

Besides the maximum_object_size, format, ignore_errors and compression, these functions have additional parameters:

Note that DuckDB can convert JSON arrays directly to its internal LIST type, and missing keys become NULL:

SELECT *
FROM read_json(
    ['birds1.json', 'birds2.json'],
    columns = {duck: 'INTEGER', goose: 'INTEGER[]', swan: 'DOUBLE'}
);

DuckDB can automatically detect the types like so:

SELECT goose, duck FROM read_json('*.json.gz');
SELECT goose, duck FROM '*.json.gz'; -- equivalent

DuckDB can read (and auto-detect) a variety of formats, specified with the format parameter. Querying a JSON file that contains an 'array', e.g.:

[
  {
    "duck": 42,
    "goose": 4.2
  },
  {
    "duck": 43,
    "goose": 4.3
  }
]

Can be queried exactly the same as a JSON file that contains 'unstructured' JSON, e.g.:

{
    "duck": 42,
    "goose": 4.2
}
{
    "duck": 43,
    "goose": 4.3
}

Both can be read as the table:

If your JSON file does not contain 'records', i.e., any other type of JSON than objects, DuckDB can still read it. This is specified with the records parameter. The records parameter specifies whether the JSON contains records that should be unpacked into individual columns, i.e., reading the following file with records:

{"duck": 42, "goose": [1, 2, 3]}
{"duck": 43, "goose": [4, 5, 6]}

Results in two columns:

You can read the same file with records set to 'false', to get a single column, which is a STRUCT containing the data:

For additional examples reading more complex data, please see the “Shredding Deeply Nested JSON, One Vector at a Time” blog post.

Loading with the COPY Statement Using FORMAT JSON

When the json extension is installed, FORMAT JSON is supported for COPY FROM, IMPORT DATABASE, as well as COPY TO, and EXPORT DATABASE. See the COPY statement and the IMPORT / EXPORT clauses.

By default, COPY expects newline-delimited JSON. If you prefer copying data to/from a JSON array, you can specify ARRAY true, e.g.,

COPY (SELECT * FROM range(5) r(i))
TO 'numbers.json' (ARRAY true);

will create the following file:

[
	{"i":0},
	{"i":1},
	{"i":2},
	{"i":3},
	{"i":4}
]

This can be read back to DuckDB as follows:

CREATE TABLE numbers (i BIGINT);
COPY numbers FROM 'numbers.json' (ARRAY true);

The format can be detected automatically the format like so:

CREATE TABLE numbers (i BIGINT);
COPY numbers FROM 'numbers.json' (AUTO_DETECT true);

We can also create a table from the auto-detected schema:

CREATE TABLE numbers AS
    FROM 'numbers.json';

Parameters