struct

Back to DuckDB Data Engineering Glossary

A struct in DuckDB is a composite data type that allows you to group multiple fields of different types into a single unit. It's similar to a record or object in other programming languages. Structs are particularly useful when working with nested data structures or when you want to organize related information together.

You can create a struct in DuckDB using the following syntax:

Copy code
SELECT {'name': 'Alice', 'age': 30, 'city': 'New York'} AS person;

This creates a struct with three fields: name (string), age (integer), and city (string).

You can access individual fields of a struct using dot notation:

Copy code
SELECT ({'name': 'Alice', 'age': 30, 'city': 'New York'}).name AS person_name;

Structs can be nested within other structs, allowing for complex data structures:

Copy code
SELECT {
  'person': {'name': 'Alice', 'age': 30},
  'address': {'city': 'New York', 'zip': '10001'}
} AS user_info;

When working with tables, you can define columns as structs, enabling you to store multiple related pieces of information in a single column:

Copy code
CREATE TABLE employees (
  id INTEGER,
  info STRUCT(name VARCHAR, age INTEGER, department VARCHAR)
);

INSERT INTO employees VALUES (1, {'name': 'Alice', 'age': 30, 'department': 'HR'});

SELECT id, info.name, info.department FROM employees;

Structs in DuckDB provide a powerful way to work with complex, hierarchical data structures within a relational database context, bridging the gap between traditional tabular data and more flexible, nested data formats.