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.
D SELECT list(actor_name) AS actors FROM film_actors; ┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ actors │ │ varchar[] │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ [Daniel Craig, Ana de Armas, Daniel Craig, Léa Seydoux, Christoph Waltz, Margot Robbie, Ryan Gosling] │ └───────────────────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT film_name, list(actor_name) AS actor_name_list FROM film_actors GROUP BY film_name ORDER BY film_name; ┌─────────────────────────────┬──────────────────────────────────────────────┐ │ film_name │ actor_name_list │ │ varchar │ varchar[] │ ├─────────────────────────────┼──────────────────────────────────────────────┤ │ Barbie │ [Margot Robbie, Ryan Gosling] │ │ James Bond - No Time to Die │ [Daniel Craig, Ana de Armas] │ │ James Bond - Spectre │ [Daniel Craig, Léa Seydoux, Christoph Waltz] │ └─────────────────────────────┴──────────────────────────────────────────────┘
D SELECT film_name, list_slice(list(actor_name), 2, 3) AS other_actors FROM film_actors GROUP BY film_name ORDER BY film_name; ┌─────────────────────────────┬────────────────────────────────┐ │ film_name │ other_actors │ │ varchar │ varchar[] │ ├─────────────────────────────┼────────────────────────────────┤ │ Barbie │ [Ryan Gosling] │ │ James Bond - No Time to Die │ [Ana de Armas] │ │ James Bond - Spectre │ [Léa Seydoux, Christoph Waltz] │ └─────────────────────────────┴────────────────────────────────┘
DuckDB also provides a convenient syntax for slicing lists, which you might find familiar if you have used list slicing in Python.
SELECT film_name, list(actor_name)[2:3] AS other_actors FROM film_actors GROUP BY film_name ORDER BY film_name;
One key difference from Python list indexing that you might have picked up on already if you have Python experience is that DuckDB counts its positional indexes starting at 1, whereas Python uses 0-based indexing.
D SELECT list_distinct(list(actor_name)) AS actors FROM film_actors; ┌─────────────────────────────────────────────────────────────────────────────────────────┐ │ actors │ │ varchar[] │ ├─────────────────────────────────────────────────────────────────────────────────────────┤ │ [Ryan Gosling, Margot Robbie, Christoph Waltz, Léa Seydoux, Ana de Armas, Daniel Craig] │ └─────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT list_sort(list_distinct(list(actor_name))) AS actors FROM film_actors; ┌─────────────────────────────────────────────────────────────────────────────────────────┐ │ actors │ │ varchar[] │ ├─────────────────────────────────────────────────────────────────────────────────────────┤ │ [Ana de Armas, Christoph Waltz, Daniel Craig, Léa Seydoux, Margot Robbie, Ryan Gosling] │ └─────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT [actor.split(' ')[-1].lower() FOR actor IN list(actor_name) ] AS actor_last_name FROM film_actors; ┌────────────────────────────────────────────────────────┐ │ actor_last_name │ │ varchar[] │ ├────────────────────────────────────────────────────────┤ │ [craig, armas, craig, seydoux, waltz, robbie, gosling] │ └────────────────────────────────────────────────────────┘
D SELECT [actor.upper() FOR actor IN list(character_name) IF length(actor) > 12 ] AS long_characters FROM film_actors; ┌───────────────────┐ │ long_characters │ │ varchar[] │ ├───────────────────┤ │ [MADELEINE SWANN] │ └───────────────────┘
DuckDB provides a range of SQL functions via its json extension, which enables you to effectively read, transform, and write JSON data.
The json extension is bundled with almost every DuckDB client (including the DuckDB CLI) and is pre-loaded on DuckDB startup.
D SELECT json_object( 'film_name', film_name, 'actor_name', actor_name ) AS json_created FROM film_actors; ┌─────────────────────────────────────────────────────────────────────────┐ │ json_created │ │ json │ ├─────────────────────────────────────────────────────────────────────────┤ │ {"film_name":"James Bond - No Time to Die","actor_name":"Daniel Craig"} │ │ {"film_name":"James Bond - No Time to Die","actor_name":"Ana de Armas"} │ │ {"film_name":"James Bond - Spectre","actor_name":"Daniel Craig"} │ │ {"film_name":"James Bond - Spectre","actor_name":"Léa Seydoux"} │ │ {"film_name":"James Bond - Spectre","actor_name":"Christoph Waltz"} │ │ {"film_name":"Barbie","actor_name":"Margot Robbie"} │ │ {"film_name":"Barbie","actor_name":"Ryan Gosling"} │ └─────────────────────────────────────────────────────────────────────────┘
D SELECT film_name, json_group_object(actor_name, character_name) AS actor_character_json FROM film_actors GROUP BY film_name; ┌─────────────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────┐ │ film_name │ actor_character_json │ │ varchar │ json │ ├─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤ │ James Bond - Spectre │ {"Daniel Craig":"James Bond","Léa Seydoux":"Madeleine Swann","Christoph Waltz":"Blofeld"} │ │ James Bond - No Time to Die │ {"Daniel Craig":"James Bond","Ana de Armas":"Paloma"} │ │ Barbie │ {"Margot Robbie":"Barbie","Ryan Gosling":"Ken"} │ └─────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────┘
It’s worth remembering that JSON objects are generally slower to work with than a DuckDB STRUCT data type, which may look superficially similar. The consistency of the STRUCT object allows DuckDB to perform several optimizations, meaning they are almost always more efficient and faster to manipulate than JSON objects.
D SELECT film_actors FROM film_actors; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ film_actors │ │ struct(film_name varchar, actor_name varchar, character_name varchar) │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ {'film_name': James Bond - No Time to Die, 'actor_name': Daniel Craig, 'character_name': James Bond} │ │ {'film_name': James Bond - No Time to Die, 'actor_name': Ana de Armas, 'character_name': Paloma} │ │ {'film_name': James Bond - Spectre, 'actor_name': Daniel Craig, 'character_name': James Bond} │ │ {'film_name': James Bond - Spectre, 'actor_name': Léa Seydoux, 'character_name': Madeleine Swann} │ │ {'film_name': James Bond - Spectre, 'actor_name': Christoph Waltz, 'character_name': Blofeld} │ │ {'film_name': Barbie, 'actor_name': Margot Robbie, 'character_name': Barbie} │ │ {'film_name': Barbie, 'actor_name': Ryan Gosling, 'character_name': Ken} │ └──────────────────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT * FROM read_json('media_tv.json'); ┌────────────┬──────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ media_type │ name │ media_payload │ │ varchar │ varchar │ struct("type" varchar, genres varchar[], premiered date, schedule struct("time" time, "days" varchar[])) │ ├────────────┼──────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ tv │ The Simpsons │ {'type': Animation, 'genres': [Comedy, Family], 'premiered': 1989-12-16, 'schedule': {'time': 19:00:00, 'days': [Sunday]}} │ │ tv │ Bluey │ {'type': Animation, 'genres': [Kids], 'premiered': 2018-10-04, 'schedule': {'time': 08:00:00, 'days': [Saturday, Sunday]}} │ │ tv │ Friends │ {'type': Scripted, 'genres': [Comedy, Romance], 'premiered': 1994-09-20, 'schedule': {'time': 20:00:00, 'days': [Thursday… │ └────────────┴──────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT * FROM read_json( 'media_tv.json', columns = { media_type: 'VARCHAR', name: 'VARCHAR', media_payload: 'STRUCT( type VARCHAR, genres VARCHAR[], premiered DATE, schedule STRUCT( time VARCHAR, days VARCHAR[] ) )' } ); ┌────────────┬──────────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ media_type │ name │ media_payload │ │ varchar │ varchar │ struct("type" varchar, genres varchar[], premiered date, schedule struct("time" varchar, "days" varchar[])) │ ├────────────┼──────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ tv │ The Simpsons │ {'type': Animation, 'genres': [Comedy, Family], 'premiered': 1989-12-16, 'schedule': {'time': 19:00, 'days': [Sunday]}} │ │ tv │ Bluey │ {'type': Animation, 'genres': [Kids], 'premiered': 2018-10-04, 'schedule': {'time': 08:00, 'days': [Saturday, Sunday]}} │ │ tv │ Friends │ {'type': Scripted, 'genres': [Comedy, Romance], 'premiered': 1994-09-20, 'schedule': {'time': 20:00, 'days': [Thursday]}} │ └────────────┴──────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D CREATE OR REPLACE TABLE media AS SELECT * FROM read_json('media_tv.json'); D DESCRIBE media; ┌───────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────┬─────────┬─────────┬─────────┬─────────┐ │ column_name │ column_type │ null │ key │ default │ extra │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├───────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────┼─────────┼─────────┼─────────┼─────────┤ │ media_type │ VARCHAR │ YES │ │ │ │ │ name │ VARCHAR │ YES │ │ │ │ │ media_payload │ STRUCT("type" VARCHAR, genres VARCHAR[], premiered DATE, schedule STRUCT("time" TIME, "days" … │ YES │ │ │ │ └───────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────┴─────────┴─────────┴─────────┴─────────┘
D SELECT name, media_payload FROM media; ┌──────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ name │ media_payload │ │ varchar │ struct("type" varchar, genres varchar[], premiered date, schedule struct("time" time, "days" varchar[])) │ ├──────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ The Simpsons │ {'type': Animation, 'genres': [Comedy, Family], 'premiered': 1989-12-16, 'schedule': {'time': 19:00:00, 'days': [Sunday]}} │ │ Bluey │ {'type': Animation, 'genres': [Kids], 'premiered': 2018-10-04, 'schedule': {'time': 08:00:00, 'days': [Saturday, Sunday]}} │ │ Friends │ {'type': Scripted, 'genres': [Comedy, Romance], 'premiered': 1994-09-20, 'schedule': {'time': 20:00:00, 'days': [Thursday]}} │ └──────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT name, media_payload.type, media_payload.genres, media_payload.premiered, media_payload.schedule FROM media; ┌──────────────┬───────────┬───────────────────┬────────────┬────────────────────────────────────────────────┐ │ name │ type │ genres │ premiered │ schedule │ │ varchar │ varchar │ varchar[] │ date │ struct("time" time, "days" varchar[]) │ ├──────────────┼───────────┼───────────────────┼────────────┼────────────────────────────────────────────────┤ │ The Simpsons │ Animation │ [Comedy, Family] │ 1989-12-16 │ {'time': 19:00:00, 'days': [Sunday]} │ │ Bluey │ Animation │ [Kids] │ 2018-10-04 │ {'time': 08:00:00, 'days': [Saturday, Sunday]} │ │ Friends │ Scripted │ [Comedy, Romance] │ 1994-09-20 │ {'time': 20:00:00, 'days': [Thursday]} │ └──────────────┴───────────┴───────────────────┴────────────┴────────────────────────────────────────────────┘
D SELECT name, media_payload.* FROM media; ┌──────────────┬───────────┬───────────────────┬────────────┬────────────────────────────────────────────────┐ │ name │ type │ genres │ premiered │ schedule │ │ varchar │ varchar │ varchar[] │ date │ struct("time" time, "days" varchar[]) │ ├──────────────┼───────────┼───────────────────┼────────────┼────────────────────────────────────────────────┤ │ The Simpsons │ Animation │ [Comedy, Family] │ 1989-12-16 │ {'time': 19:00:00, 'days': [Sunday]} │ │ Bluey │ Animation │ [Kids] │ 2018-10-04 │ {'time': 08:00:00, 'days': [Saturday, Sunday]} │ │ Friends │ Scripted │ [Comedy, Romance] │ 1994-09-20 │ {'time': 20:00:00, 'days': [Thursday]} │ └──────────────┴───────────┴───────────────────┴────────────┴────────────────────────────────────────────────┘
D CREATE OR REPLACE TABLE media_extracted AS SELECT name, media_payload.* FROM media;
D SELECT name, unnest(genres) FROM media_extracted; ┌──────────────┬────────────────┐ │ name │ unnest(genres) │ │ varchar │ varchar │ ├──────────────┼────────────────┤ │ The Simpsons │ Comedy │ │ The Simpsons │ Family │ │ Bluey │ Kids │ │ Friends │ Comedy │ │ Friends │ Romance │ └──────────────┴────────────────┘
It should also be noted that using unnest does not preserve the order of the list – so the rows will appear in an arbitrary order unless an ORDER BY clause is used in the SQL statement.
D SELECT media_type, media_payload FROM read_json('media_mixed.json'); ┌────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ media_type │ media_payload │ │ varchar │ struct(first_film_screened date, staring varchar[], "type" varchar, genres varchar[], premiered date, schedule struct("time" time, "days… │ ├────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ film │ {'first_film_screened': 2022-10-05, 'staring': [Daniel Craig, Ana de Armas], 'type': NULL, 'genres': NULL, 'premiered': NULL, 'schedule'… │ │ film │ {'first_film_screened': 2023-02-05, 'staring': [Margot Robbie, Ryan Gosling], 'type': NULL, 'genres': NULL, 'premiered': NULL, 'schedule… │ │ tv │ {'first_film_screened': NULL, 'staring': NULL, 'type': Animation, 'genres': [Comedy, Family], 'premiered': 1989-12-16, 'schedule': {'tim… │ │ tv │ {'first_film_screened': NULL, 'staring': NULL, 'type': Animation, 'genres': [Kids], 'premiered': 2018-10-04, 'schedule': {'time': 08:00:… │ │ tv │ {'first_film_screened': NULL, 'staring': NULL, 'type': Scripted, 'genres': [Comedy, Romance], 'premiered': 1994-09-20, 'schedule': {'tim… │ └────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT media_type, json_group_structure(media_payload) FROM read_json('media_mixed.json') GROUP BY media_type; ┌────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ media_type │ json_group_structure(media_payload) │ │ varchar │ json │ ├────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ film │ {"first_film_screened":"VARCHAR","staring":["VARCHAR"],"type":"NULL","genres":"NULL","premiered":"NULL","schedule":"NULL"} │ │ tv │ {"first_film_screened":"NULL","staring":"NULL","type":"VARCHAR","genres":["VARCHAR"],"premiered":"VARCHAR","schedule":{"time":"VARCHAR",… │ └────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
This query gives us a clear indication of the schemas across both types of records, including where they diverge, through the appearance of NULL values:
The key thing to observe is that if we had a larger number of media types with diverging schemas, this technique would provide a much more effective way to understand differences across schemas within a collection of heterogeneous JSON objects.
D SELECT media_type, name, media_payload.first_film_screened, media_payload.staring FROM read_json('media_mixed.json') WHERE media_type = 'film'; ┌────────────┬─────────────────────────────┬─────────────────────┬───────────────────────────────┐ │ media_type │ name │ first_film_screened │ staring │ │ varchar │ varchar │ date │ varchar[] │ ├────────────┼─────────────────────────────┼─────────────────────┼───────────────────────────────┤ │ film │ James Bond - No Time to Die │ 2022-10-05 │ [Daniel Craig, Ana de Armas] │ │ film │ Barbie │ 2023-02-05 │ [Margot Robbie, Ryan Gosling] │ └────────────┴─────────────────────────────┴─────────────────────┴───────────────────────────────┘
Let’s do the same again, but this time for records with media_type set to tv:
D SELECT media_type, name, media_payload.type, media_payload.genres, media_payload.premiered, media_payload.schedule, FROM read_json('media_mixed.json') WHERE media_type = 'tv'; ┌────────────┬──────────────┬───────────┬───────────────────┬────────────┬────────────────────────────────────────────────┐ │ media_type │ name │ type │ genres │ premiered │ schedule │ │ varchar │ varchar │ varchar │ varchar[] │ date │ struct("time" time, "days" varchar[]) │ ├────────────┼──────────────┼───────────┼───────────────────┼────────────┼────────────────────────────────────────────────┤ │ tv │ The Simpsons │ Animation │ [Comedy, Family] │ 1989-12-16 │ {'time': 19:00:00, 'days': [Sunday]} │ │ tv │ Bluey │ Animation │ [Kids] │ 2018-10-04 │ {'time': 08:00:00, 'days': [Saturday, Sunday]} │ │ tv │ Friends │ Scripted │ [Comedy, Romance] │ 1994-09-20 │ {'time': 20:00:00, 'days': [Thursday]} │ └────────────┴──────────────┴───────────┴───────────────────┴────────────┴────────────────────────────────────────────────┘
From these results, you might have noticed that we didn’t unpack the schedule object, so DuckDB has left this as a semi-structured value by converting it into a STRUCT data type. You might like to try extending this example by fully unpacking the nested properties of the schedule property into separate columns.
D SELECT * FROM read_json('https://api.tvmaze.com/singlesearch/shows?q=The%20Simpsons'); ┌───────┬──────────────────────┬──────────────┬───────────┬───┬──────────────────────┬──────────────────────┬────────────┬──────────────────────┐ │ id │ url │ name │ type │ … │ image │ summary │ updated │ _links │ │ int64 │ varchar │ varchar │ varchar │ │ struct(medium varc… │ varchar │ int64 │ struct(self struct… │ ├───────┼──────────────────────┼──────────────┼───────────┼───┼──────────────────────┼──────────────────────┼────────────┼──────────────────────┤ │ 83 │ https://www.tvmaze… │ The Simpsons │ Animation │ … │ {'medium': https:/… │ <p><b>The Simpsons… │ 1736634662 │ {'self': {'href': … │ ├───────┴──────────────────────┴──────────────┴───────────┴───┴──────────────────────┴──────────────────────┴────────────┴──────────────────────┤ │ 1 rows 23 columns (8 shown) │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT season, number, name, json_extract_string(rating, '$.average') AS avg_rating, summary FROM read_json('https://api.tvmaze.com/shows/83/episodebynumber?season=34&number=2'); ┌────────┬────────┬────────────────┬────────────┬────────────────────────────────────────────────────────────────────────────────────────────┐ │ season │ number │ name │ avg_rating │ summary │ │ int64 │ int64 │ varchar │ varchar │ varchar │ ├────────┼────────┼────────────────┼────────────┼────────────────────────────────────────────────────────────────────────────────────────────┤ │ 34 │ 2 │ One Angry Lisa │ 6.8 │ <p>Lisa gets called for jury duty while Marge becomes obsessed with her exercise bike.</p> │ └────────┴────────┴────────────────┴────────────┴────────────────────────────────────────────────────────────────────────────────────────────┘
浙公网安备 33010602011771号