DuckDB supports SQL functions that are useful for reading values from existing JSON and creating new JSON data. JSON is supported with the json extension which is shipped with most DuckDB distributions and is auto-loaded on first use. If you would like to install or load it manually, please consult the “Installing and Loading” page.

About JSON

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.

Bestpractice DuckDB implements multiple interfaces for JSON extraction: JSONPath and JSON Pointer. Both of them work with the arrow operator (->) and the json_extract function call. It's best to pick one syntax and use it in your entire application.

Indexing

Warning Following PostgreSQL's conventions, DuckDB uses 1-based indexing for its ARRAY and LIST data types but 0-based indexing for the JSON data type.

Examples

Loading JSON

Read a JSON file from disk, auto-infer options:

SELECT * FROM 'todos.json';

Use the read_json function 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('/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 with a CREATE TABLE ... AS SELECT clause:

CREATE TABLE todos AS
    SELECT * FROM 'todos.json';

Writing JSON

Write the result of a query to a JSON file:

COPY (SELECT * FROM todos) TO 'todos.json';

JSON Data Type

Create a table with a column for storing JSON data and insert data into it:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

Retrieving JSON Data

Retrieve the family key's value:

SELECT j.family FROM example;

"anatidae"

Extract the family key's value with a JSONPath expression:

SELECT j->'$.family' FROM example;

"anatidae"

Extract the family key's value with a JSONPath expression as a VARCHAR:

SELECT j->>'$.family' FROM example;

anatidae