Malloy Documentation
search
previousTransform Data
Sessionize - Map/Reducenext

Directly Querying a JSON API

VSCode document

DuckDB can be used to query a JSON API endpoint, and Malloy makes it simple to transform the resulting data. The example below reads exchange rate data from the US Treasury and shows the data by currency over time.

document
source: exchange_rates is 
  duckdb.table('https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?fields=country_currency_desc,exchange_rate,record_date&page[size]=10000&filter=country_currency_desc.json')

Query by Country

This query builds a line chart showing exchange rate values over time. With a simple Malloy query, we can easily transform and analyze a dataset directly from an HTTP API, without any intermediate database required.

document
run: exchange_rates -> {
  group_by: data.country_currency_desc
  # line_chart
  nest: by_date is  {
    group_by: 
      data.record_date
      rate is data.exchange_rate::number
  }
}
QUERY RESULTS
country_​currency_​descby_​date
AFGHANISTAN-AFGHANI
2016201720182019record_date6065707580rate
ALBANIA-LEK
2016201720182019record_date105110115120125130rate
ALGERIA-DINAR
2016201720182019record_date100105110115120rate
ANGOLA-KWANZA
2016201720182019record_date100150200250300350rate
ANTIGUA-BARBUDA-E. CARIBBEAN DOLLAR
2016201720182019record_date3rate
ARGENTINA-PESO
2016201720182019record_date102030405060rate
ARMENIA-DRAM
2016201720182019record_date470475480485rate
AUSTRALIA-DOLLAR
2016201720182019record_date1.301.351.401.451.50rate
AUSTRIA-EURO
2016201720182019record_date0.820.840.860.880.900.920.94rate
AZERBAIJAN-MANAT
Fri 30record_date2rate
AZERBAIJAN-NEW MANAT
2016201720182019record_date1.01.21.41.61.8rate
Afghanistan-Afghani
2005201020152020record_date020,00040,00060,00080,000rate
Albania-Lek
2005201020152020record_date8090100110120130140150rate
Algeria-Dinar
2005201020152020record_date6080100120140rate
Angola-Kwanza
2005201020152020record_date0200400600800rate
Antigua & Barbuda-E. Caribbean Dollar
2005201020152020record_date3rate
Argentina-Peso
2005201020152020record_date0100200300400rate
Armenia-Dram
2005201020152020record_date300350400450500550600rate
Australia-Dollar
2005201020152020record_date1.01.21.41.61.82.0rate
Austria-Euro
2002200420062008201020122014201620182020record_date0.60.70.80.91.01.11.2rate
Austria-Schilling
2002200320042005200620072008record_date051015rate
Azerbaidjan-Second Manat
2002200420062008201020122014record_date01,0002,0003,0004,0005,000rate
Azerbaijan-Manat
200620082010201220142016201820202022record_date0.81.01.21.41.6rate
BAHAMAS-DOLLAR
2016201720182019record_date1rate
BAHRAIN-DINAR
2016201720182019record_date0rate
BANGLADESH-TAKA
2016201720182019record_date798081828384rate
BARBADOS-DOLLAR
2016201720182019record_date2rate
BELARUS-NEW RUBLE
October2017AprilJulyOctober2018AprilJulyOctober2019AprilJulyrecord_date1.901.952.002.052.102.15rate
BELARUS-RUBLE
AprilJulyOctober2016AprilJulyOctober2017Aprilrecord_date15,00016,00017,00018,00019,00020,000rate
BELGIUM-EURO
2016201720182019record_date0.820.840.860.880.900.920.94rate
BELIZE-DOLLAR
2016201720182019record_date2rate
BENIN-CFA FRANC
2016201720182019record_date520540560580600620rate
BERMUDA-DOLLAR
2016201720182019record_date1rate
BOLIVIA-BOLIVIANO
2016201720182019record_date6.786.806.826.846.866.886.90rate
BOSNIA-HERCEGOVINA-MARKA
201620172018record_date1.601.651.701.751.801.85rate
BOSNIA-MARKA
2019FebruaryMarchAprilMayJuneJulyAugustSeptemberrecord_date1.701.721.741.761.781.80rate
BOTSWANA-PULA
2016201720182019record_date9.510.010.511.0rate
BRAZIL-REAL
2016201720182019record_date3.23.43.63.84.04.2rate
BRUNEI-DOLLAR
2016201720182019record_date1.321.341.361.381.401.421.44rate
BULGARIA-LEV
2016201720182019record_date1.601.651.701.751.801.85rate
BULGARIA-LEV New
Mon 30record_date2rate
BURKINA FASO-CFA FRANC
2016201720182019record_date520540560580600620rate
BURMA-KYAT
2016201720182019record_date1,0001,1001,2001,3001,4001,500rate
BURMA-MYANMAR-KYAT
October2016AprilJulyOctober2017AprilJulyOctober2018AprilJulyrecord_date1,2001,3001,4001,500rate
BURUNDI-FRANC
2016201720182019record_date1,6001,6501,7001,7501,800rate
Bahamas-Dollar
2005201020152020record_date1rate
Bahrain-Dinar
2005201020152020record_date0.3740.3750.3760.3770.3780.3790.380rate
Bangladesh-Taka
2005201020152020record_date5060708090100110rate
Barbados-Dollar
2005201020152020record_date2.0002.0052.0102.0152.020rate
Belarus-New Ruble
2020202120222023record_date2.42.62.83.03.2rate
Belarus-Ruble
2002200420062008201020122014201620182020record_date02,0004,0006,0008,00010,00012,00014,000rate
Belaruse-New Ruble
Thu 30record_date2.62.83.03.23.4rate
Belgium-Euro
2002200420062008201020122014201620182020record_date0.60.70.80.91.01.11.2rate
Belgium-Francs
2002200320042005200620072008record_date01020304050rate
Belize-Dollar
2005201020152020record_date2.002.052.102.152.20rate
Benin-CFA Franc
2005201020152020record_date400500600700800rate
Bermuda-Dollar
2005201020152020record_date1rate
Bolivia-Boliviano
2005201020152020record_date6.57.07.58.0rate
Bosnia-Marka
2005201020152020record_date1.21.41.61.82.02.22.4rate
Botswana-Pula
2005201020152020record_date468101214rate
Brazil-Real
2005201020152020record_date23456rate
Brunei-Dollar
2005201020152020record_date1.21.31.41.51.61.71.8rate
Bulgaria-Lev New
2005201020152020record_date1.21.41.61.82.02.2rate
Burkina Faso-CFA Franc
2005201020152020record_date400500600700800rate
Burma-Kyat
20022004200620082010201220142016201820202022record_date5001,0001,5002,000rate
Burundi-Franc
2005201020152020record_date1,0001,5002,0002,5003,000rate
CAMBODIA (KHMER)-RIEL
2016201720182019record_date4,0504,0604,0704,0804,0904,100rate
CAMEROON-CFA FRANC
2016201720182019record_date560580600620rate
CANADA-DOLLAR
2016201720182019record_date1.251.301.351.40rate
CAPE VERDE-ESCUDO
2016201720182019record_date9095100105rate
CAYMAN ISLANDS-DOLLAR
2016201720182019record_date0.850.900.951.00rate
CENTRAL AFRICAN REPUBLIC-CFA FRANC
2016201720182019record_date560580600620rate
CHAD-CFA FRANC
2016201720182019record_date560580600620rate
CHILE-PESO
2016201720182019record_date600620640660680700720rate
CHINA-RENMINBI
2016201720182019record_date6.26.46.66.87.07.2rate
COLOMBIA-PESO
2016201720182019record_date2,6002,8003,0003,2003,400rate
COMOROS-FRANC
2016201720182019record_date360380400420440460rate
CONGO, DEM. REP-CONGOLESE FRANC
201620172018record_date1,0001,2001,4001,600rate
CONGO-CFA FRANC
2016201720182019record_date560580600620rate
COSTA RICA-COLON
2016201720182019record_date520540560580600rate
COTE D'IVOIRE-CFA FRANC
2016201720182019record_date520540560580600620rate
CROATIA-KUNA
2016201720182019record_date6.06.26.46.66.87.0rate
CUBA-Chavito
Mon 30record_date1rate
CUBA-PESO
2016201720182019record_date1rate
CYPRUS-EURO
2016201720182019record_date0.820.840.860.880.900.920.94rate
CZECH REPUBLIC-KORUNA
2019FebruaryMarchAprilMayJuneJulyAugustSeptemberrecord_date21.822.022.222.422.622.823.0rate
CZECH-KORUNA
201620172018record_date202122232425rate
Cambodia-Riel
2005201020152020record_date3,9004,0004,1004,200rate
Cameroon-CFA Franc
2005201020152020record_date400500600700800rate
Canada-Dollar
2005201020152020record_date1.01.11.21.31.41.51.6rate
Cape Verde-Escudo
2005201020152020record_date708090100110120130rate
Cayman Island-Dollar
201220142016201820202022record_date1rate
Cayman Islands-Dollar
2023FebruaryMarchAprilMayJunerecord_date1rate
Central African Rep.-CFA Franc
2005201020152020record_date400500600700800rate
Chad-CFA Franc
2005201020152020record_date400500600700800rate
Chile-Peso
2005201020152020record_date4005006007008009001,000rate
China-Renminbi
2005201020152020record_date6.06.57.07.58.0rate
Colombia-Peso
2005201020152020record_date1,5002,0002,5003,0003,5004,0004,5005,000rate
Comoros-Franc
2005201020152020record_date400450500550600rate
Congo-CFA Franc
2005201020152020record_date400500600700800rate
Costa Rica-Colon
2005201020152020record_date300400500600700rate
Cote D'ivoire-CFA Franc
2005201020152020record_date400500600700800rate
Croatia-Euro
Sat 31record_date1rate
Croatia-KUNA
20022004200620082010201220142016201820202022record_date56789rate
Cross Border-Euro
20062008201020122014201620182020record_date0.650.700.750.800.850.90rate
Cuba-Chavito
200620082010201220142016201820202022record_date0.920.940.960.981.00rate
Cuba-Peso
AprilJulyOctober2022AprilJulyOctober2023Aprilrecord_date05101520rate
Cyprus-Euro
201220142016201820202022record_date0.700.750.800.850.90rate
Cyprus-Pound
2002200420062008201020122014record_date0.40.50.60.70.8rate
Czech Republic-Koruna
2023FebruaryMarchAprilMayJunerecord_date21.021.221.421.621.822.022.2rate
Czech. Republic-Koruna
20022004200620082010201220142016201820202022record_date152025303540rate
DEMOCRATIC REPUBLIC OF CONGO-FRANC
2019FebruaryMarchAprilMayJuneJulyAugustSeptemberrecord_date1,6301,6351,6401,6451,650rate
DENMARK-KRONE
2016201720182019record_date6.06.26.46.66.87.0rate
DJIBOUTI-FRANC
2016201720182019record_date177rate
DOMINICAN REPUBLIC-PESO
2016201720182019record_date4446485052rate
Dem. Rep. of Congo-Congolese Franc
2023FebruaryMarchAprilMayJunerecord_date2,0002,1002,2002,3002,400rate
Dem. Rep. of Congo-Franc
20022004200620082010201220142016201820202022record_date05001,0001,5002,000rate
Denmark-Krone
2005201020152020record_date56789rate
Djibouti-Franc
2005201020152020record_date174.5175.0175.5176.0176.5177.0rate
Dominican Republic-Peso
2005201020152020record_date2030405060rate
ECUADOR-DOLARES
2016201720182019record_date1rate
EGYPT-POUND
2016201720182019record_date81012141618rate
EL SALVADOR-DOLARES
2016201720182019record_date1rate
EQUATORIAL GUINEA-CFA FRANC
2016201720182019record_date560580600620rate
ERITREA-NAKFA
2016201720182019record_date15rate
ESTONIA-EURO
2016201720182019record_date0.820.840.860.880.900.920.94rate
ETHIOPIA-BIRR
2016201720182019record_date202224262830rate
EURO ZONE-EURO