Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

JSON Processing Functions

## JSON Extraction Functions

There are two extraction functions, which have their respective operators. The operators can only be used if the string is stored as the `JSON` logical type. These functions supports the same two location notations as JSON Scalar functions.

Function | Alias | Operator | Description
--- | --- | --- | ---
`json_exists(json, path)` |  |  | Returns `true` if the supplied path exists in the `json`, and `false` otherwise.
`json_extract(json, path)` | `json_extract_path` | `->` | Extracts `JSON` from `json` at the given `path`. If `path` is a `LIST`, the result will be a `LIST` of `JSON`.
`json_extract_string(json, path)` | `json_extract_path_text` | `->>` | Extracts `VARCHAR` from `json` at the given `path`. If `path` is a `LIST`, the result will be a `LIST` of `VARCHAR`.
`json_value(json, path)` |  |  | Extracts `JSON` from `json` at the given `path`. If the `json` at the supplied path is not a scalar value, it will return `NULL`.

Note that the arrow operator `->`, which is used for JSON extracts, has a low precedence as it is also used in lambda functions.

Therefore, you need to surround the `->` operator with parentheses when expressing operations such as equality comparisons (`=`). For example:

    SELECT ((JSON '{"field": 42}')->'field') = 42;

> Warning DuckDB's JSON data type uses 0-based indexing.

Examples:

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

    SELECT json_extract(j, '$.family') FROM example;

    "anatidae"

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

    "anatidae"

    SELECT j->'$.species[0]' FROM example;

    "duck"

    SELECT j->'$.species[*]' FROM example;

    ["duck", "goose", "swan", null]

    SELECT j->>'$.species[*]' FROM example;

    [duck, goose, swan, null]

    SELECT j->'$.species'->0 FROM example;

    "duck"

    SELECT j->'species'->['0','1'] FROM example;

    ["duck", "goose"]

    SELECT json_extract_string(j, '$.family') FROM example;

    anatidae

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

    anatidae

    SELECT j->>'$.species[0]' FROM example;

    duck

    SELECT j->'species'->>0 FROM example;

    duck

    SELECT j->'species'->>['0','1'] FROM example;

    [duck, goose]

Note that DuckDB's JSON data type uses 0-based indexing.

If multiple values need to be extracted from the same JSON, it is more efficient to extract a list of paths:

The following will cause the JSON to be parsed twice,:

Resulting in a slower query that uses more memory:

    SELECT
        json_extract(j, 'family') AS family,
        json_extract(j, 'species') AS species
    FROM example;

family | species
--- | ---
"anatidae" | ["duck","goose","swan",null]

The following produces the same result but is faster and more memory-efficient:

    WITH extracted AS (
        SELECT json_extract(j, ['family', 'species']) AS extracted_list
        FROM example
    )
    SELECT
        extracted_list[1] AS family,
        extracted_list[2] AS species
    FROM extracted;

## JSON Scalar Functions

The following scalar JSON functions can be used to gain information about the stored JSON values. With the exception of `json_valid(json)`, all JSON functions produce an error when invalid JSON is supplied.

We support two kinds of notations to describe locations within JSON: JSON Pointer and JSONPath.

Function | Description
--- | ---
`json_array_length(json[, path])` | Return the number of elements in the JSON array `json`, or `0` if it is not a JSON array. If `path` is specified, return the number of elements in the JSON array at the given `path`. If `path` is a `LIST`, the result will be `LIST` of array lengths.
`json_contains(json_haystack, json_needle)` | Returns `true` if `json_needle` is contained in `json_haystack`. Both parameters are of JSON type, but `json_needle` can also be a numeric value or a string, however the string must be wrapped in double quotes.
`json_keys(json[, path])` | Returns the keys of `json` as a `LIST` of `VARCHAR`, if `json` is a JSON object. If `path` is specified, return the keys of the JSON object at the given `path`. If `path` is a `LIST`, the result will be `LIST` of `LIST` of `VARCHAR`.
`json_structure(json)` | Return the structure of `json`. Defaults to `JSON` if the structure is inconsistent (e.g., incompatible types in an array).
`json_type(json[, path])` | Return the type of the supplied `json`, which is one of `ARRAY`, `BIGINT`, `BOOLEAN`, `DOUBLE`, `OBJECT`, `UBIGINT`, `VARCHAR`, and `NULL`. If `path` is specified, return the type of the element at the given `path`. If `path` is a `LIST`, the result will be `LIST` of types.
`json_valid(json)` | Return whether `json` is valid JSON.
`json(json)` | Parse and minify `json`.

The JSONPointer syntax separates each field with a `/`. For example, to extract the first element of the array with key `duck`, you can do:

    SELECT json_extract('{"duck": [1, 2, 3]}', '/duck/0');

    1

The JSONPath syntax separates fields with a `.`, and accesses array elements with `[i]`, and always starts with `$`. Using the same example, we can do the following:

    SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[0]');

    1

Note that DuckDB's JSON data type uses 0-based indexing.

JSONPath is more expressive, and can also access from the back of lists:

    SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[#-1]');

    3

JSONPath also allows escaping syntax tokens, using double quotes:

    SELECT json_extract('{"duck.goose": [1, 2, 3]}', '$."duck.goose"[1]');

    2

Examples using the anatidae biological family:

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

    SELECT json(j) FROM example;

    {"family":"anatidae","species":["duck","goose","swan",null]}

    SELECT j.family FROM example;

    "anatidae"

    SELECT j.species[0] FROM example;

    "duck"

    SELECT json_valid(j) FROM example;

    true

    SELECT json_valid('{');

    false

    SELECT json_array_length('["duck", "goose", "swan", null]');

    4

    SELECT json_array_length(j, 'species') FROM example;

    4

    SELECT json_array_length(j, '/species') FROM example;

    4

    SELECT json_array_length(j, '$.species') FROM example;

    4

    SELECT json_array_length(j, ['$.species']) FROM example;

    [4]

    SELECT json_type(j) FROM example;

    OBJECT

    SELECT json_keys(j) FROM example;

    [family, species]

    SELECT json_structure(j) FROM example;

    {"family":"VARCHAR","species":["VARCHAR"]}

    SELECT json_structure('["duck", {"family": "anatidae"}]');

    ["JSON"]

    SELECT json_contains('{"key": "value"}', '"value"');

    true

    SELECT json_contains('{"key": 1}', '1');

    true

    SELECT json_contains('{"top_key": {"key": "value"}}', '{"key": "value"}');

    true

## JSON Aggregate Functions

There are three JSON aggregate functions.

Function | Description
--- | ---
`json_group_array(any)` | Return a JSON array with all values of `any` in the aggregation.
`json_group_object(key, value)` | Return a JSON object with all `key`, `value` pairs in the aggregation.
`json_group_structure(json)` | Return the combined `json_structure` of all `json` in the aggregation.

Examples:

    CREATE TABLE example1 (k VARCHAR, v INTEGER);
    INSERT INTO example1 VALUES ('duck', 42), ('goose', 7);

    SELECT json_group_array(v) FROM example1;

    [42, 7]

    SELECT json_group_object(k, v) FROM example1;

    {"duck":42,"goose":7}

    CREATE TABLE example2 (j JSON);
    INSERT INTO example2 VALUES
        ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
        ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

    SELECT json_group_structure(j) FROM example2;

    {"family":"VARCHAR","species":["VARCHAR"],"coolness":"DOUBLE","hair":"BOOLEAN"}

## Transforming JSON to Nested Types

In many cases, it is inefficient to extract values from JSON one-by-one. Instead, we can “extract” all values at once, transforming JSON to the nested types `LIST` and `STRUCT`.

Function | Description
--- | ---
`json_transform(json, structure)` | Transform `json` according to the specified `structure`.
`from_json(json, structure)` | Alias for `json_transform`.
`json_transform_strict(json, structure)` | Same as `json_transform`, but throws an error when type casting fails.
`from_json_strict(json, structure)` | Alias for `json_transform_strict`.

The `structure` argument is JSON of the same form as returned by `json_structure`. The `structure` argument can be modified to transform the JSON into the desired structure and types. It is possible to extract fewer key/value pairs than are present in the JSON, and it is also possible to extract more: missing keys become `NULL`.

Examples:

    CREATE TABLE example (j JSON);
    INSERT INTO example VALUES
        ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
        ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

    SELECT json_transform(j, '{"family": "VARCHAR", "coolness": "DOUBLE"}') FROM example;

    {'family': anatidae, 'coolness': 42.420000}
    {'family': canidae, 'coolness': NULL}

    SELECT json_transform(j, '{"family": "TINYINT", "coolness": "DECIMAL(4, 2)"}') FROM example;

    {'family': NULL, 'coolness': 42.42}
    {'family': NULL, 'coolness': NULL}

    SELECT json_transform_strict(j, '{"family": "TINYINT", "coolness": "DOUBLE"}') FROM example;

    Invalid Input Error: Failed to cast value: "anatidae"

##### About this page

- Report content issue
- Edit this page on GitHub