Data Import
Examples
#### Loading JSON {#docs:data:json:overview::loading-json}  
Read a JSON file from disk, auto-infer options:  
```sql
SELECT * FROM 'todos.json';
```  
Use the `read_json` function with custom options:  
```sql
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:  
```bash
cat data/json/todos.json | duckdb -c "SELECT * FROM read_json('/dev/stdin')"
```  
Read a JSON file into a table:  
```sql
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](#docs:sql:statements:create_table::create-table--as-select-ctas):  
```sql
CREATE TABLE todos AS
SELECT * FROM 'todos.json';
```  
#### Writing JSON {#docs:data:json:overview::writing-json}  
Write the result of a query to a JSON file:  
```sql
COPY (SELECT * FROM todos) TO 'todos.json';
```  
#### JSON Data Type {#docs:data:json:overview::json-data-type}  
Create a table with a column for storing JSON data and insert data into it:  
```sql
CREATE TABLE example (j JSON);
INSERT INTO example VALUES
('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');
```  
#### Retrieving JSON Data {#docs:data:json:overview::retrieving-json-data}  
Retrieve the family key's value:  
```sql
SELECT j.family FROM example;
```  
```text
"anatidae"
```  
Extract the family key's value with a [JSONPath](https://goessner.net/articles/JsonPath/) expression:  
```sql
SELECT j->'$.family' FROM example;
```  
```text
"anatidae"
```  
Extract the family key's value with a [JSONPath](https://goessner.net/articles/JsonPath/) expression as a `VARCHAR`:  
```sql
SELECT j->>'$.family' FROM example;
```  
```text
anatidae
```