yaml

27.6k downloads this week 23 stars Extension descriptor

Read YAML files into DuckDB with native YAML type support, comprehensive extraction functions, and seamless JSON interoperability

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, 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.

Installing and Loading

INSTALL yaml FROM community;
LOAD yaml;

Example

-- 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);

About yaml

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.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

Added Settings