Working with public REST API's

Editor's note: by combining the read_json capability of DuckDB with the ability to retrieve arbitrary https URLs, you can easily use DuckDB to read data from public and unprotected APIs. Note that you can't currently pass arbitrary HTTP headers to access protected APIs.

Query JSON from a public REST APISQL

-- DuckDB can directly query a public REST API returning a JSON 
-- Query the "tvmaze.com" API for the TV show "South Park"

SELECT name, type, summary
FROM read_json('https://api.tvmaze.com/singlesearch/shows?q=South%20Park', 
auto_detect=true, format='newline_delimited');
Copy code

Format the response from a public REST API SQL

-- Format the response from a public REST API 
-- Retrieve a list of temperature readings for Sydney, Australia 
-- The returned payload has hourly temps for 24 hours
-- We can use JSONPath to extract data from a JSON documemnt 
-- to find the most recent temperature

SELECT json_extract(hourly, '$.temperature_2m[1]')
from read_json('https://api.open-meteo.com/v1/forecast?latitude=-33.8678&longitude=151.2073&hourly=temperature_2m&forecast_days=1', auto_detect=true, format='newline_delimited');
Copy code

Simon Aubury

Edited 02/23/24