DuckDB for dynamically parsing JSON data from a URL

bank-holidays.sql

/*
    DuckDB for dynamically parsing JSON data from a URL

    DuckDB version: 0.10.2

    Bill Wallis, 2024-05-09
*/


select version();


select
    division,
    unnest(events.events, recursive:=true),
from (
    unpivot 'https://www.gov.uk/bank-holidays.json'
    on "england-and-wales", "scotland", "northern-ireland"
    into
        name division
        value events
);

sample.md

division	title	date	notes	bunting
england-and-wales	New Year’s Day	2018-01-01		true
england-and-wales	Good Friday	2018-03-30		false
england-and-wales	Easter Monday	2018-04-02		true
england-and-wales	Early May bank holiday	2018-05-07		true
england-and-wales	Spring bank holiday	2018-05-28		true
england-and-wales	Summer bank holiday	2018-08-27		true
england-and-wales	Christmas Day	2018-12-25		true
england-and-wales	Boxing Day	2018-12-26		true

This is part of the following LinkedIn post:

https://www.linkedin.com/posts/bill-wallis_sql-duckdb-activity-7183788325726507009-4Dcf

...and features in the DuckDB snippets:

https://duckdbsnippets.com/snippets/172/uk-bank-holidays