---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: yaml
excerpt: |
DuckDB Community Extensions
Read YAML files into DuckDB with native YAML type support, comprehensive extraction functions, and seamless JSON interoperability
extension:
name: yaml
description: Read YAML files into DuckDB with native YAML type support, comprehensive extraction functions, and seamless JSON interoperability
version: 1.10.1
language: C++
build: cmake
license: MIT
maintainers:
- teaguesterling
# Pinned vcpkg for Windows yaml-cpp compatibility (older vcpkg had Windows build issues).
vcpkg_commit: "656be05781442d5c4cb14978f6c7bf47b6e12b32"
repo:
github: teaguesterling/duckdb_yaml
andium: 0bdc812c064fa7b85617f75680d9c1177263f741
ref: 4d9aaffb613d1e381087866414d25c329d7501a3
# variegata pins the v1.5.6 track. scripts/build.py sends a v1.5.6 build here when
# present and to `ref` otherwise, and unlike ref_next it has no skip branch -- so
# once `ref` is 2.0-only a v1.5.6 build would fail rather than be skipped. Equal to
# `ref` while both lines build; advance only to a commit v1.5.6 can still build.
variegata: 4d9aaffb613d1e381087866414d25c329d7501a3
ref_next: 4d9aaffb613d1e381087866414d25c329d7501a3
docs:
hello_world: |
-- Load the extension
LOAD yaml;
-- Query YAML files directly
SELECT * FROM 'config.yaml';
SELECT * FROM 'data/*.yml' WHERE active = true;
-- Create tables with YAML columns
CREATE TABLE configs(id INTEGER, config YAML);
INSERT INTO configs VALUES (1, E'server: production\nport: 8080\nfeatures: [logging, metrics]');
-- Extract data using YAML functions
SELECT
config ->> '$.server' AS environment, -- Arrow operator
yaml_extract(config, '$.port') AS port,
yaml_value(config, '$.port') AS port_scalar
FROM configs;
-- Analyze YAML structure
SELECT yaml_structure(config) FROM configs;
-- Returns: {"server":"VARCHAR","port":"UBIGINT","features":["VARCHAR"]}
-- Check containment and merge documents
SELECT yaml_contains(config, 'server: production') AS is_prod FROM configs;
SELECT yaml_merge_patch(config, 'debug: true') AS with_debug FROM configs;
-- Convert between YAML and JSON
SELECT yaml_to_json(config) AS json_config FROM configs;
SELECT to_yaml({name: 'John', age: 30}) AS yaml_person;
-- Write query results to YAML
COPY (SELECT * FROM users) TO 'output.yaml' (FORMAT yaml, STYLE block);
extended_description: |
The YAML extension brings comprehensive YAML support to DuckDB, enabling seamless integration of YAML data within SQL queries.
**Key Features:**
- **Native YAML Type**: Full YAML type support with automatic casting between YAML, JSON, and VARCHAR
- **File Reading**: Read YAML files with `read_yaml()` and `read_yaml_objects()` functions supporting multi-document files, top-level sequences, and robust error handling
- **Direct File Querying**: Query YAML files directly using `FROM 'file.yaml'` syntax
- **Extraction Functions**: Query YAML data with `yaml_extract()`, `yaml_type()`, `yaml_exists()`, `yaml_value()`, `->>` operator, and path-based extraction
- **Document Operations**: Check containment with `yaml_contains()`, merge documents with `yaml_merge_patch()`, and analyze structure with `yaml_structure()`
- **Type Detection**: Comprehensive automatic type detection for temporal types (DATE, TIME, TIMESTAMP), optimal numeric types, and boolean values
- **Column Type Specification**: Explicitly define column types when reading YAML files for schema consistency
- **YAML Output**: Write query results to YAML files using `COPY TO` with configurable formatting styles
- **Multi-Document Support**: Handle files with multiple YAML documents separated by `---`
- **Error Recovery**: Continue processing valid documents even when some contain errors
- **JSON Interoperability**: Seamless conversion between YAML and JSON formats
- **Frontmatter Extraction**: Extract YAML frontmatter metadata from other files
**Example Use Cases:**
- Configuration file management and querying
- Log file analysis and processing
- Data migration between YAML and relational formats
- Integration with YAML-based CI/CD pipelines
- Processing Kubernetes manifests and Helm charts
The extension is built using yaml-cpp and follows DuckDB's extension development best practices, ensuring reliable performance and cross-platform compatibility.
**Note**: This extension was written primarily using Claude and Claude Code as an exercise in AI-driven development.
extension_star_count: 23
extension_star_count_pretty: 23
extension_download_count: 27640
extension_download_count_pretty: 27.6k
image: '/images/community_extensions/social_preview/preview_community_extension_yaml.png'
layout: community_extension_doc
---
### Compatibility Matrix
This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ✅ | ✅ | ✅ | ✅ | ✅ |
| Linux ARM64 | ✅ | ✅ | ✅ | ✅ | ✅ |
| macOS AMD64 | ✅ | ✅ | ✅ | ✅ | ✅ |
| macOS ARM64 | ✅ | ✅ | ✅ | ✅ | ✅ |
| Windows AMD64 | ✅ | ✅ | ✅ | ✅ | ✅ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| WebAssembly | ✅ | ✅ | ✅ | ✅ | ✅ |
### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```
{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}
{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions
| function_name | function_type | description | comment | examples |
|------------------------------|---------------|---------------------------------------------------------------------------------------------------------------|---------|---------------------------------------------------------------------------------------------------|
| ->> | scalar | Extract a scalar value as VARCHAR from a YAML document at the specified path. | NULL | ['name: Alice' ->> '$.name'] |
| copy_format_yaml | scalar | NULL | NULL | |
| format_yaml | scalar | Format a SQL value as YAML with configurable style, multiline, and indentation options. | NULL | [format_yaml({'name': 'Alice', 'hobbies': ['reading', 'gaming']}, style := 'block', indent := 4)] |
| from_yaml | scalar | Parse a YAML string or document into a structured DuckDB type. | NULL | [from_yaml('name: Alice
age: 30', {'name': 'VARCHAR', 'age': 'INTEGER'})] |
| parse_yaml | table | Parse a YAML string into a table. | NULL | [SELECT * FROM parse_yaml('a: 1
b: 2')] |
| read_yaml | table | Read YAML files into a tabular format. | NULL | [SELECT * FROM read_yaml('data.yaml')] |
| read_yaml_frontmatter | table | Read YAML frontmatter from documents. | NULL | [SELECT * FROM read_yaml_frontmatter('doc.md')] |
| read_yaml_objects | table | Read YAML files as structured YAML objects. | NULL | [SELECT * FROM read_yaml_objects('data.yaml')] |
| to_yaml | scalar | Convert any SQL value or structure into a YAML document string (alias for value_to_yaml). | NULL | [to_yaml({'name': 'Alice', 'age': 30})] |
| value_to_yaml | scalar | Convert any SQL value or structure into a YAML document string. | NULL | [value_to_yaml({'name': 'Alice', 'age': 30})] |
| yaml | scalar | Parse a YAML string and return a value of YAML type. | NULL | [yaml('name: Alice')] |
| yaml_agg | aggregate | Aggregate values into a YAML array. | NULL | [yaml_agg(x)] |
| yaml_array_elements | table | Unnest a YAML array into rows of YAML values. | NULL | [SELECT * FROM yaml_array_elements('[10, 20, 30]')] |
| yaml_array_length | scalar | Return the number of elements in a YAML array, optionally at a given path. | NULL | [yaml_array_length('[1, 2, 3]'), yaml_array_length('items: [a, b]', '$.items')] |
| yaml_build_object | scalar | Construct a YAML mapping from alternating key and value arguments. | NULL | [yaml_build_object('name', 'Alice', 'age', 30)] |
| yaml_contains | scalar | Check if target YAML document contains candidate YAML document. | NULL | [yaml_contains('a: 1
b: 2', 'a: 1')] |
| yaml_each | table | Unnest a YAML mapping into key-value pairs. | NULL | [SELECT * FROM yaml_each('a: 1
b: 2')] |
| yaml_exists | scalar | Check if a path exists within a YAML document. | NULL | [yaml_exists('a: 1', '$.a')] |
| yaml_extract | scalar | Extract a YAML value from a YAML document at the specified path. | NULL | [yaml_extract('a: {b: 2}', '$.a.b')] |
| yaml_extract_path | scalar | Extract a YAML value from a YAML document at the specified path (alias for yaml_extract). | NULL | [yaml_extract_path('a: {b: 2}', '$.a.b')] |
| yaml_extract_path_text | scalar | Extract a scalar value as VARCHAR from a YAML document at the specified path (alias for yaml_extract_string). | NULL | [yaml_extract_path_text('name: Alice', '$.name')] |
| yaml_extract_string | scalar | Extract a scalar value as VARCHAR from a YAML document at the specified path. | NULL | [yaml_extract_string('name: Alice', '$.name')] |
| yaml_get_default_style | scalar | Get the current default output style for YAML functions. | NULL | [yaml_get_default_style()] |
| yaml_get_max_expansion_nodes | scalar | Get the maximum number of YAML nodes allowed during alias/anchor expansion. | NULL | [yaml_get_max_expansion_nodes()] |
| yaml_get_max_input_size | scalar | Get the maximum allowed byte size for a YAML document. | NULL | [yaml_get_max_input_size()] |
| yaml_get_max_nesting_depth | scalar | Get the maximum allowed nesting depth for YAML parsing. | NULL | [yaml_get_max_nesting_depth()] |
| yaml_keys | scalar | Return the keys of a YAML mapping as a list of VARCHAR, optionally at a given path. | NULL | [yaml_keys('a: 1
b: 2'), yaml_keys('root: {x: 10, y: 20}', '$.root')] |
| yaml_merge_patch | scalar | Apply a JSON Merge Patch (RFC 7386) to a target YAML document. | NULL | [yaml_merge_patch('a: 1
b: 2', 'a: 3
c: 4')] |
| yaml_set_default_style | scalar | Set the default output style for YAML functions ('flow' or 'block'). | NULL | [yaml_set_default_style('block')] |
| yaml_set_max_expansion_nodes | scalar | Set the maximum number of YAML nodes created during alias/anchor expansion. | NULL | [yaml_set_max_expansion_nodes(100000)] |
| yaml_set_max_input_size | scalar | Set the maximum allowed byte size for a YAML document. | NULL | [yaml_set_max_input_size(10485760)] |
| yaml_set_max_nesting_depth | scalar | Set the maximum allowed nesting depth for YAML parsing. | NULL | [yaml_set_max_nesting_depth(1000)] |
| yaml_structure | scalar | Return a JSON representation describing the structure and types of the YAML document. | NULL | [yaml_structure('a: 1
b: [2, 3]')] |
| yaml_to_json | scalar | Convert a YAML string or document to a JSON string. | NULL | [yaml_to_json('name: Alice
age: 30')] |
| yaml_type | scalar | Return the YAML type of the input value or at a given path ('null', 'scalar', 'array', 'object'). | NULL | [yaml_type('[1, 2, 3]'), yaml_type('a: 1', '$.a')] |
| yaml_valid | scalar | Return true if the input string is valid YAML, false otherwise. | NULL | [yaml_valid('name: Alice')] |
| yaml_value | scalar | Extract a scalar value only as VARCHAR from a YAML document, returning NULL for non-scalars. | NULL | [yaml_value('a: 1', '$.a')] |
### Overloaded Functions
This extension does not add any function overloads.
### Added Types
| type_name | type_size | logical_type | type_category | internal |
|-----------|----------:|--------------|---------------|----------|
| yaml | 16 | VARCHAR | STRING | true |
### Added Settings
| name | description | input_type | scope | aliases | typed_value |
|----------------|---------------------------------------------------------|------------|--------|---------|-------------|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |