DuckDB - Study Notes 6

DuckDB’s nested data types: LIST, MAP, and STRUCT.

D SELECT [7,8,9] AS list_int;
┌───────────┐
│ list_int  │
│  int32[]  │
├───────────┤
│ [7, 8, 9] │
└───────────┘

D SELECT [
      'Quantum of Solace',
      'Skyfall',
      'Spectre',
      'No Time to Die'] AS list_string;
┌───────────────────────────────────────────────────────┐
│                      list_string                      │
│                       varchar[]                       │
├───────────────────────────────────────────────────────┤
│ [Quantum of Solace, Skyfall, Spectre, No Time to Die] │
└───────────────────────────────────────────────────────┘

LIST can be empty (with zero elements) and is essentially unbounded, allowing for any number of elements. However, note that every element within LIST must have the same data type. You cannot mix INTEGER and VARCHAR values in a LIST data type, for example.

D CREATE OR REPLACE TABLE movies AS
  SELECT MAP(
      [
          'Quantum of Solace',
          'Skyfall',
          'Spectre',
          'No Time to Die'
      ],
      [2008, 2012, 2015, 2021]
  ) AS movie_release_map;

D SELECT movie_release_map
  FROM movies;
┌───────────────────────────────────────────────────────────────────────────┐
│                             movie_release_map                             │
│                           map(varchar, integer)                           │
├───────────────────────────────────────────────────────────────────────────┤
│ {Quantum of Solace=2008, Skyfall=2012, Spectre=2015, No Time to Die=2021} │
└───────────────────────────────────────────────────────────────────────────┘

D SELECT movie_release_map['Quantum of Solace']
  FROM movies;
┌────────────────────────────────────────┐
│ movie_release_map['Quantum of Solace'] │
│                int32[]                 │
├────────────────────────────────────────┤
│ [2008]                                 │
└────────────────────────────────────────┘

D SELECT movie_release_map['Quantum of Solace'][1]
  FROM movies;
┌───────────────────────────────────────────┐
│ movie_release_map['Quantum of Solace'][1] │
│                   int32                   │
├───────────────────────────────────────────┤
│                                      2008 │
└───────────────────────────────────────────┘

D SELECT { movie: 'No Time to Die',
      release_year: 2021,
      box_office: 771.2
  } AS struct_movie;
┌──────────────────────────────────────────────────────────────────────┐
│                             struct_movie                             │
│ struct(movie varchar, release_year integer, box_office decimal(4,1)) │
├──────────────────────────────────────────────────────────────────────┤
│ {'movie': No Time to Die, 'release_year': 2021, 'box_office': 771.2} │
└──────────────────────────────────────────────────────────────────────┘

SELECT 'apple' AS fruit
UNION
SELECT 'banana' AS fruit
UNION
SELECT 'cherry' AS fruit;


SELECT ARRAY[1, 2, 3] AS nums
UNION
SELECT ARRAY[4, 5, 6] AS nums;

D CREATE OR REPLACE TABLE film_actors AS
  SELECT *
  FROM read_csv('film_actors.csv');
D SELECT *
  FROM film_actors
  LIMIT 5;
┌─────────────────────────────┬─────────────────┬─────────────────┐
│          film_name          │   actor_name    │ character_name  │
│           varchar           │     varchar     │     varchar     │
├─────────────────────────────┼─────────────────┼─────────────────┤
│ James Bond - No Time to Die │ Daniel Craig    │ James Bond      │
│ James Bond - No Time to Die │ Ana de Armas    │ Paloma          │
│ James Bond - Spectre        │ Daniel Craig    │ James Bond      │
│ James Bond - Spectre        │ Léa Seydoux     │ Madeleine Swann │
│ James Bond - Spectre        │ Christoph Waltz │ Blofeld         │
└─────────────────────────────┴─────────────────┴─────────────────┘

A particularly useful pattern that DuckDB supports is aggregating column-wise row groups into LIST instances that contain the contents of each group as a single value. This functionality is enabled through DuckDB’s list aggregate function.