Cheat Sheet - DuckDB

Read a JSON file from disk

select * 
from read_json('~/path/to/document.json')
limit 5;

Selecting into json column

-- DuckDB arrays are 1-indexed !!

select
    a.icao,
    a.name,
    a.runways[1]->'helipad' as isHelipad
from read_json('airports.json') a
where
    a.icao = 'LSZH'

Selecting a JSON array

select
    a.icao,
    a.name,
    json_extract(a.runways, '$[*].helipad')
from read_json('airports.json') a
where
    a.icao = 'LSZH'

-- LSZH,Zurich,"[false, false, false, false, false, false, true, true]"

Extract JSON array into single rows

select
    json_extract(runway.value, '$.helipad') as isHelipad
from read_json('airports.json') a,
    json_each(a.runways) as runway
where
    a.icao = 'LSZH'