Downloads 18.8kthis week

GitHub stars 22

Extension descriptor (YAML)

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

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

| 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

Added Settings

This extension does not add any settings.