Querying JSON data from an API

Before we end this chapter, we’ll cover one more topic: querying JSON data retrieved from a remote API. We can use DuckDB’s ability to work with data returned by a programming interface.

In Chapter 5, we encountered DuckDB’s httpfs extension, which enables us to read remotely hosted files and interact with object storage using the S3 API. One of the applications of this functionality is retrieving and querying JSON data exposed by an HTTP API.

As an example of a publicly available REST API that’s exposed over HTTP, we’ll be using TVmaze, a free user-curated television database service. Their API (https://www.tvmaze.com/api) is a free service that allows us to query for information about television shows, episodes, actors, and scheduling information. As TVmaze’s API returns JSON data, we can query the API using DuckDB via the httpfs extensions, and then work with the results using the json extension.

Let...