malloy logo Malloy Documentation
search

Malloy by Example

This document will assumes a working knowledge of SQL and will rapidly take you through some of Malloy's key language features.

Using this Guide

For every Malloy Query you can see the formatted result, or raw result as JSON, or the SQL used to produce the result.

Click tab to to see the HTML, JSON or SQL result: 👈👈

SQL SELECT vs Malloy's query

The statement to run a query in Malloy is query:. There are two types of queries in Malloy, reductions which have group_by: or aggregate: statements, and projections which have project: statements and do not group or aggregate results.

Projection: SELECT with no GROUP BY

In SQL

SELECT code, full_name, state, faa_region, fac_type, elevation
FROM `malloy-data.faa.airports`
ORDER BY code

Equivalent in Malloy

query: table('malloy-data.faa.airports') -> {
  project: code, full_name, state, faa_region, fac_type, elevation
  order_by: code
}
QUERY RESULTS
codefull_​namestatefaa_​regionfac_​typeelevation
00ATOTAL RFPAAEAHELIPORT11
00CANIMAS AIR PARKCOANMAIRPORT6,684
00CAGOLDSTONE /GTS/CAAWPAIRPORT3,038
00EAT&T - APACHE JUNCTIONAZAWPHELIPORT2,527
00FTCJC-NORTHEAST CAMPUSTXASWHELIPORT600
00FDRINGHAVERFLASOHELIPORT25
00FLRIVER OAKFLASOAIRPORT35
00GALT WORLDGAASOAIRPORT700
00ICHAPMAN MEMORIAL FIELDOHAGLAIRPORT1,180
00IIBAILEY GENERATION STATIONINAGLHELIPORT600
00ILHAMMERILAGLAIRPORT840
00INST MARY MEDICAL CENTERINAGLAIRPORT634
00ISHAYENGA'S CANT FIND FARMSILAGLAIRPORT820
00JGEORGIA-PACIFICGAASOAIRPORT133
00KREDPATH RLAILAGLAIRPORT470
00KSHAYDEN FARMKSACEAIRPORT1,100
00LLYALL-ROBERTSCAAWPAIRPORT925
00LLAC & R COMPONENTSILAGLHELIPORT600
00MTHIGPEN FIELDMSASOAIRPORT351
00MIDOW CHEMICALMIAGLHELIPORT588
00MNBATTLE LAKE MUNIMNAGLAIRPORT1,365
00NBUCKSNJAEAAIRPORT105
00NCNORTH RALEIGHNCASOAIRPORT348
00NJCOLGATE-PISCATAWAYNJAEAHELIPORT78
00OIMIAMI VALLEY HOSPITALOHAGLHELIPORT905
00ORSTEEL SYSTEMSORANMHELIPORT195
00PAR J DPAAEAHELIPORT402
00PSTHOMAS FIELDPAAEAAIRPORT800
00QTHAYER AVIATIONCAAWPAIRPORT54
00RLIVINGSTON MUNITXASWAIRPORT151
00SMC KENZIE BRIDGE STATEORANMAIRPORT1,620
00TASW REGION FAATXASWHELIPORT598
00TSALPINE RANGETXASWAIRPORT670
00VMEADOW LAKECOANMAIRPORT6,874
00VACITYVAAEAHELIPORT12
00WLOWER GRANITE STATEWAANMAIRPORT719
00WAHOWELLWAANMAIRPORT160
00WINORTHERN LITEWIAGLAIRPORT860
00XDORAL RESORT & COUNTRY CLUBFLASOHELIPORT6
00XSL P ASKEW FARMSTXASWAIRPORT3,110
00YKAPAUN-WILSON FIELDMNAGLAIRPORT1,122
01APURKEYPILEAKAALAIRPORT1,950
01AKPROVIDENCE SEWARD MEDICAL CENTERAKAALHELIPORT120
01AZYATAZAWPHELIPORT3,300
01BWOODSTOCKCTANEAIRPORT465
01CGRANTMIAGLAIRPORT815
01CALUGO SUBSTATIONCAAWPHELIPORT3,733
01COST VINCENT GENERAL HOSPITALCOANMHELIPORT10,175
01CTBERLIN FAIRGROUNDSCTANEHELIPORT60
01FDFLORIDA HOSPITAL-ALTAMONTEFLASOHELIPORT86
01FLCEDAR KNOLL FLYING RANCHFLASOAIRPORT19
01GPERRY-WARSAWNYAEAAIRPORT1,559
01GAMEDICAL CENTERGAASOHELIPORT319
01HMC CHRISTYILAGLAIRPORT630
01IAUGUST ACRESOHAGLAIRPORT1,100
01IIMYERS FIELDINAGLAIRPORT950
01ILHOOPESTON COMMUNITY MEMORIAL HOSPITALILAGLHELIPORT583
01JHILLIARD AIRPARKFLASOAIRPORT59
01KCALDWELL MUNIKSACEAIRPORT1,157
01KSFLYING N RANCHKSACEAIRPORT1,485
01KYLOURDES HOSPITALKYASOHELIPORT419
01LABARHAMLAASWAIRPORT90
01LLSCHUMAIER RLAILAGLAIRPORT555
01MTISHOMINGO COUNTYMSASOAIRPORT578
01MACOMPAQ ANDOVERMAANEHELIPORT140
01MESAINT PETER'SMEANESEAPLANE BASE608
01MIFLOW THROUGH TERMINALMIAGLHELIPORT736
01MNBARNESMNAGLSEAPLANE BASE1,358
01MOHIGHWAY PATROL TROOP C HEADQUARTERSMOACEHELIPORT615
01NCTOPSAIL AIRPARKNCASOAIRPORT65
01NEDETOURNEACEAIRPORT3,000
01NHMOORE AIRFIELDNHANEAIRPORT835
01NJALBERT GUIDO MEMORIALNJAEAHELIPORT10
01NYVASSAR HOSPITALNYAEAHELIPORT100
01OHGILLMOROHAGLAIRPORT665
01OIGALION COMMUNITY HOSPITALOHAGLHELIPORT1,140
01OKLAWRENCEOKASWAIRPORT1,525
01ORRED & WHITE FLYING SERVICEORANMAIRPORT4,346
01PAPINEPAAEAHELIPORT1,215
01PNBIERLY(PERSONAL USE)PAAEAAIRPORT960
01PSNORT'S RESORTPAAEAAIRPORT1,060
01TARED ADAIRTXASWHELIPORT90
01TSST JOSEPH HOSPITALTXASWHELIPORT675
01TXMIMS FARMTXASWULTRALIGHT610
01UDUCKWATERNVAWPAIRPORT5,124
01VAURORA AIRPARKCOANMAIRPORT5,680
01VAOEHDAVAAEAAIRPORT500
01WSAWYERVAAEASTOLPORT13
01WAWILLAPA HARBORWAANMHELIPORT154
01WIPREHN CRANBERRY COMPANYWIAGLAIRPORT930
01XJACKSON MEMORIAL HOSPITALFLASOHELIPORT53
01XSMEADOWOOD RANCHTXASWHELIPORT500
02AGRAGG-WADE FIELDALASOAIRPORT586
02AKRUSTIC WILDERNESSAKAALAIRPORT190
02AZWESTCHESTER FARM AIRSTRIPAZAWPAIRPORT4,200
02CCAPITOLWIAGLAIRPORT850
02CASWEPI BETA PLATFORM ELLENCAAWPHELIPORT122
02COMC CULLOUGHCOANMAIRPORT7,615
02CTSTRANGERS POINTCTANEHELIPORT540
02EMESA LUTHERAN HOSPITALAZAWPHELIPORT1,248
[
  {
    "code": "00A",
    "full_name": "TOTAL RF",
    "state": "PA",
    "faa_region": "AEA",
    "fac_type": "HELIPORT",
    "elevation": 11
  },
  {
    "code": "00C",
    "full_name": "ANIMAS AIR PARK",
    "state": "CO",
    "faa_region": "ANM",
    "fac_type": "AIRPORT",
    "elevation": 6684
  },
  {
    "code": "00CA",
    "full_name": "GOLDSTONE /GTS/",
    "state": "CA",
    "faa_region": "AWP",
    "fac_type": "AIRPORT",
    "elevation": 3038
  },
  {
    "code": "00E",
    "full_name": "AT&T - APACHE JUNCTION",
    "state": "AZ",
    "faa_region": "AWP",
    "fac_type": "HELIPORT",
    "elevation": 2527
  },
  {
    "code": "00F",
    "full_name": "TCJC-NORTHEAST CAMPUS",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "HELIPORT",
    "elevation": 600
  },
  {
    "code": "00FD",
    "full_name": "RINGHAVER",
    "state": "FL",
    "faa_region": "ASO",
    "fac_type": "HELIPORT",
    "elevation": 25
  },
  {
    "code": "00FL",
    "full_name": "RIVER OAK",
    "state": "FL",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 35
  },
  {
    "code": "00GA",
    "full_name": "LT WORLD",
    "state": "GA",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 700
  },
  {
    "code": "00I",
    "full_name": "CHAPMAN MEMORIAL FIELD",
    "state": "OH",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 1180
  },
  {
    "code": "00II",
    "full_name": "BAILEY GENERATION STATION",
    "state": "IN",
    "faa_region": "AGL",
    "fac_type": "HELIPORT",
    "elevation": 600
  },
  {
    "code": "00IL",
    "full_name": "HAMMER",
    "state": "IL",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 840
  },
  {
    "code": "00IN",
    "full_name": "ST MARY MEDICAL CENTER",
    "state": "IN",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 634
  },
  {
    "code": "00IS",
    "full_name": "HAYENGA'S CANT FIND FARMS",
    "state": "IL",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 820
  },
  {
    "code": "00J",
    "full_name": "GEORGIA-PACIFIC",
    "state": "GA",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 133
  },
  {
    "code": "00K",
    "full_name": "REDPATH RLA",
    "state": "IL",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 470
  },
  {
    "code": "00KS",
    "full_name": "HAYDEN FARM",
    "state": "KS",
    "faa_region": "ACE",
    "fac_type": "AIRPORT",
    "elevation": 1100
  },
  {
    "code": "00L",
    "full_name": "LYALL-ROBERTS",
    "state": "CA",
    "faa_region": "AWP",
    "fac_type": "AIRPORT",
    "elevation": 925
  },
  {
    "code": "00LL",
    "full_name": "AC & R COMPONENTS",
    "state": "IL",
    "faa_region": "AGL",
    "fac_type": "HELIPORT",
    "elevation": 600
  },
  {
    "code": "00M",
    "full_name": "THIGPEN FIELD",
    "state": "MS",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 351
  },
  {
    "code": "00MI",
    "full_name": "DOW CHEMICAL",
    "state": "MI",
    "faa_region": "AGL",
    "fac_type": "HELIPORT",
    "elevation": 588
  },
  {
    "code": "00MN",
    "full_name": "BATTLE LAKE MUNI",
    "state": "MN",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 1365
  },
  {
    "code": "00N",
    "full_name": "BUCKS",
    "state": "NJ",
    "faa_region": "AEA",
    "fac_type": "AIRPORT",
    "elevation": 105
  },
  {
    "code": "00NC",
    "full_name": "NORTH RALEIGH",
    "state": "NC",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 348
  },
  {
    "code": "00NJ",
    "full_name": "COLGATE-PISCATAWAY",
    "state": "NJ",
    "faa_region": "AEA",
    "fac_type": "HELIPORT",
    "elevation": 78
  },
  {
    "code": "00OI",
    "full_name": "MIAMI VALLEY HOSPITAL",
    "state": "OH",
    "faa_region": "AGL",
    "fac_type": "HELIPORT",
    "elevation": 905
  },
  {
    "code": "00OR",
    "full_name": "STEEL SYSTEMS",
    "state": "OR",
    "faa_region": "ANM",
    "fac_type": "HELIPORT",
    "elevation": 195
  },
  {
    "code": "00PA",
    "full_name": "R J D",
    "state": "PA",
    "faa_region": "AEA",
    "fac_type": "HELIPORT",
    "elevation": 402
  },
  {
    "code": "00PS",
    "full_name": "THOMAS FIELD",
    "state": "PA",
    "faa_region": "AEA",
    "fac_type": "AIRPORT",
    "elevation": 800
  },
  {
    "code": "00Q",
    "full_name": "THAYER AVIATION",
    "state": "CA",
    "faa_region": "AWP",
    "fac_type": "AIRPORT",
    "elevation": 54
  },
  {
    "code": "00R",
    "full_name": "LIVINGSTON MUNI",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "AIRPORT",
    "elevation": 151
  },
  {
    "code": "00S",
    "full_name": "MC KENZIE BRIDGE STATE",
    "state": "OR",
    "faa_region": "ANM",
    "fac_type": "AIRPORT",
    "elevation": 1620
  },
  {
    "code": "00TA",
    "full_name": "SW REGION FAA",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "HELIPORT",
    "elevation": 598
  },
  {
    "code": "00TS",
    "full_name": "ALPINE RANGE",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "AIRPORT",
    "elevation": 670
  },
  {
    "code": "00V",
    "full_name": "MEADOW LAKE",
    "state": "CO",
    "faa_region": "ANM",
    "fac_type": "AIRPORT",
    "elevation": 6874
  },
  {
    "code": "00VA",
    "full_name": "CITY",
    "state": "VA",
    "faa_region": "AEA",
    "fac_type": "HELIPORT",
    "elevation": 12
  },
  {
    "code": "00W",
    "full_name": "LOWER GRANITE STATE",
    "state": "WA",
    "faa_region": "ANM",
    "fac_type": "AIRPORT",
    "elevation": 719
  },
  {
    "code": "00WA",
    "full_name": "HOWELL",
    "state": "WA",
    "faa_region": "ANM",
    "fac_type": "AIRPORT",
    "elevation": 160
  },
  {
    "code": "00WI",
    "full_name": "NORTHERN LITE",
    "state": "WI",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 860
  },
  {
    "code": "00X",
    "full_name": "DORAL RESORT & COUNTRY CLUB",
    "state": "FL",
    "faa_region": "ASO",
    "fac_type": "HELIPORT",
    "elevation": 6
  },
  {
    "code": "00XS",
    "full_name": "L P ASKEW FARMS",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "AIRPORT",
    "elevation": 3110
  },
  {
    "code": "00Y",
    "full_name": "KAPAUN-WILSON FIELD",
    "state": "MN",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 1122
  },
  {
    "code": "01A",
    "full_name": "PURKEYPILE",
    "state": "AK",
    "faa_region": "AAL",
    "fac_type": "AIRPORT",
    "elevation": 1950
  },
  {
    "code": "01AK",
    "full_name": "PROVIDENCE SEWARD MEDICAL CENTER",
    "state": "AK",
    "faa_region": "AAL",
    "fac_type": "HELIPORT",
    "elevation": 120
  },
  {
    "code": "01AZ",
    "full_name": "YAT",
    "state": "AZ",
    "faa_region": "AWP",
    "fac_type": "HELIPORT",
    "elevation": 3300
  },
  {
    "code": "01B",
    "full_name": "WOODSTOCK",
    "state": "CT",
    "faa_region": "ANE",
    "fac_type": "AIRPORT",
    "elevation": 465
  },
  {
    "code": "01C",
    "full_name": "GRANT",
    "state": "MI",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 815
  },
  {
    "code": "01CA",
    "full_name": "LUGO SUBSTATION",
    "state": "CA",
    "faa_region": "AWP",
    "fac_type": "HELIPORT",
    "elevation": 3733
  },
  {
    "code": "01CO",
    "full_name": "ST VINCENT GENERAL HOSPITAL",
    "state": "CO",
    "faa_region": "ANM",
    "fac_type": "HELIPORT",
    "elevation": 10175
  },
  {
    "code": "01CT",
    "full_name": "BERLIN FAIRGROUNDS",
    "state": "CT",
    "faa_region": "ANE",
    "fac_type": "HELIPORT",
    "elevation": 60
  },
  {
    "code": "01FD",
    "full_name": "FLORIDA HOSPITAL-ALTAMONTE",
    "state": "FL",
    "faa_region": "ASO",
    "fac_type": "HELIPORT",
    "elevation": 86
  },
  {
    "code": "01FL",
    "full_name": "CEDAR KNOLL FLYING RANCH",
    "state": "FL",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 19
  },
  {
    "code": "01G",
    "full_name": "PERRY-WARSAW",
    "state": "NY",
    "faa_region": "AEA",
    "fac_type": "AIRPORT",
    "elevation": 1559
  },
  {
    "code": "01GA",
    "full_name": "MEDICAL CENTER",
    "state": "GA",
    "faa_region": "ASO",
    "fac_type": "HELIPORT",
    "elevation": 319
  },
  {
    "code": "01H",
    "full_name": "MC CHRISTY",
    "state": "IL",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 630
  },
  {
    "code": "01I",
    "full_name": "AUGUST ACRES",
    "state": "OH",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 1100
  },
  {
    "code": "01II",
    "full_name": "MYERS FIELD",
    "state": "IN",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 950
  },
  {
    "code": "01IL",
    "full_name": "HOOPESTON COMMUNITY MEMORIAL HOSPITAL",
    "state": "IL",
    "faa_region": "AGL",
    "fac_type": "HELIPORT",
    "elevation": 583
  },
  {
    "code": "01J",
    "full_name": "HILLIARD AIRPARK",
    "state": "FL",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 59
  },
  {
    "code": "01K",
    "full_name": "CALDWELL MUNI",
    "state": "KS",
    "faa_region": "ACE",
    "fac_type": "AIRPORT",
    "elevation": 1157
  },
  {
    "code": "01KS",
    "full_name": "FLYING N RANCH",
    "state": "KS",
    "faa_region": "ACE",
    "fac_type": "AIRPORT",
    "elevation": 1485
  },
  {
    "code": "01KY",
    "full_name": "LOURDES HOSPITAL",
    "state": "KY",
    "faa_region": "ASO",
    "fac_type": "HELIPORT",
    "elevation": 419
  },
  {
    "code": "01LA",
    "full_name": "BARHAM",
    "state": "LA",
    "faa_region": "ASW",
    "fac_type": "AIRPORT",
    "elevation": 90
  },
  {
    "code": "01LL",
    "full_name": "SCHUMAIER RLA",
    "state": "IL",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 555
  },
  {
    "code": "01M",
    "full_name": "TISHOMINGO COUNTY",
    "state": "MS",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 578
  },
  {
    "code": "01MA",
    "full_name": "COMPAQ ANDOVER",
    "state": "MA",
    "faa_region": "ANE",
    "fac_type": "HELIPORT",
    "elevation": 140
  },
  {
    "code": "01ME",
    "full_name": "SAINT PETER'S",
    "state": "ME",
    "faa_region": "ANE",
    "fac_type": "SEAPLANE BASE",
    "elevation": 608
  },
  {
    "code": "01MI",
    "full_name": "FLOW THROUGH TERMINAL",
    "state": "MI",
    "faa_region": "AGL",
    "fac_type": "HELIPORT",
    "elevation": 736
  },
  {
    "code": "01MN",
    "full_name": "BARNES",
    "state": "MN",
    "faa_region": "AGL",
    "fac_type": "SEAPLANE BASE",
    "elevation": 1358
  },
  {
    "code": "01MO",
    "full_name": "HIGHWAY PATROL TROOP C HEADQUARTERS",
    "state": "MO",
    "faa_region": "ACE",
    "fac_type": "HELIPORT",
    "elevation": 615
  },
  {
    "code": "01NC",
    "full_name": "TOPSAIL AIRPARK",
    "state": "NC",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 65
  },
  {
    "code": "01NE",
    "full_name": "DETOUR",
    "state": "NE",
    "faa_region": "ACE",
    "fac_type": "AIRPORT",
    "elevation": 3000
  },
  {
    "code": "01NH",
    "full_name": "MOORE AIRFIELD",
    "state": "NH",
    "faa_region": "ANE",
    "fac_type": "AIRPORT",
    "elevation": 835
  },
  {
    "code": "01NJ",
    "full_name": "ALBERT GUIDO MEMORIAL",
    "state": "NJ",
    "faa_region": "AEA",
    "fac_type": "HELIPORT",
    "elevation": 10
  },
  {
    "code": "01NY",
    "full_name": "VASSAR HOSPITAL",
    "state": "NY",
    "faa_region": "AEA",
    "fac_type": "HELIPORT",
    "elevation": 100
  },
  {
    "code": "01OH",
    "full_name": "GILLMOR",
    "state": "OH",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 665
  },
  {
    "code": "01OI",
    "full_name": "GALION COMMUNITY HOSPITAL",
    "state": "OH",
    "faa_region": "AGL",
    "fac_type": "HELIPORT",
    "elevation": 1140
  },
  {
    "code": "01OK",
    "full_name": "LAWRENCE",
    "state": "OK",
    "faa_region": "ASW",
    "fac_type": "AIRPORT",
    "elevation": 1525
  },
  {
    "code": "01OR",
    "full_name": "RED & WHITE FLYING SERVICE",
    "state": "OR",
    "faa_region": "ANM",
    "fac_type": "AIRPORT",
    "elevation": 4346
  },
  {
    "code": "01PA",
    "full_name": "PINE",
    "state": "PA",
    "faa_region": "AEA",
    "fac_type": "HELIPORT",
    "elevation": 1215
  },
  {
    "code": "01PN",
    "full_name": "BIERLY(PERSONAL USE)",
    "state": "PA",
    "faa_region": "AEA",
    "fac_type": "AIRPORT",
    "elevation": 960
  },
  {
    "code": "01PS",
    "full_name": "NORT'S RESORT",
    "state": "PA",
    "faa_region": "AEA",
    "fac_type": "AIRPORT",
    "elevation": 1060
  },
  {
    "code": "01TA",
    "full_name": "RED ADAIR",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "HELIPORT",
    "elevation": 90
  },
  {
    "code": "01TS",
    "full_name": "ST JOSEPH HOSPITAL",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "HELIPORT",
    "elevation": 675
  },
  {
    "code": "01TX",
    "full_name": "MIMS FARM",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "ULTRALIGHT",
    "elevation": 610
  },
  {
    "code": "01U",
    "full_name": "DUCKWATER",
    "state": "NV",
    "faa_region": "AWP",
    "fac_type": "AIRPORT",
    "elevation": 5124
  },
  {
    "code": "01V",
    "full_name": "AURORA AIRPARK",
    "state": "CO",
    "faa_region": "ANM",
    "fac_type": "AIRPORT",
    "elevation": 5680
  },
  {
    "code": "01VA",
    "full_name": "OEHDA",
    "state": "VA",
    "faa_region": "AEA",
    "fac_type": "AIRPORT",
    "elevation": 500
  },
  {
    "code": "01W",
    "full_name": "SAWYER",
    "state": "VA",
    "faa_region": "AEA",
    "fac_type": "STOLPORT",
    "elevation": 13
  },
  {
    "code": "01WA",
    "full_name": "WILLAPA HARBOR",
    "state": "WA",
    "faa_region": "ANM",
    "fac_type": "HELIPORT",
    "elevation": 154
  },
  {
    "code": "01WI",
    "full_name": "PREHN CRANBERRY COMPANY",
    "state": "WI",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 930
  },
  {
    "code": "01X",
    "full_name": "JACKSON MEMORIAL HOSPITAL",
    "state": "FL",
    "faa_region": "ASO",
    "fac_type": "HELIPORT",
    "elevation": 53
  },
  {
    "code": "01XS",
    "full_name": "MEADOWOOD RANCH",
    "state": "TX",
    "faa_region": "ASW",
    "fac_type": "HELIPORT",
    "elevation": 500
  },
  {
    "code": "02A",
    "full_name": "GRAGG-WADE FIELD",
    "state": "AL",
    "faa_region": "ASO",
    "fac_type": "AIRPORT",
    "elevation": 586
  },
  {
    "code": "02AK",
    "full_name": "RUSTIC WILDERNESS",
    "state": "AK",
    "faa_region": "AAL",
    "fac_type": "AIRPORT",
    "elevation": 190
  },
  {
    "code": "02AZ",
    "full_name": "WESTCHESTER FARM AIRSTRIP",
    "state": "AZ",
    "faa_region": "AWP",
    "fac_type": "AIRPORT",
    "elevation": 4200
  },
  {
    "code": "02C",
    "full_name": "CAPITOL",
    "state": "WI",
    "faa_region": "AGL",
    "fac_type": "AIRPORT",
    "elevation": 850
  },
  {
    "code": "02CA",
    "full_name": "SWEPI BETA PLATFORM ELLEN",
    "state": "CA",
    "faa_region": "AWP",
    "fac_type": "HELIPORT",
    "elevation": 122
  },
  {
    "code": "02CO",
    "full_name": "MC CULLOUGH",
    "state": "CO",
    "faa_region": "ANM",
    "fac_type": "AIRPORT",
    "elevation": 7615
  },
  {
    "code": "02CT",
    "full_name": "STRANGERS POINT",
    "state": "CT",
    "faa_region": "ANE",
    "fac_type": "HELIPORT",
    "elevation": 540
  },
  {
    "code": "02E",
    "full_name": "MESA LUTHERAN HOSPITAL",
    "state": "AZ",
    "faa_region": "AWP",
    "fac_type": "HELIPORT",
    "elevation": 1248
  }
]
SELECT 
   base.code as code,
   base.full_name as full_name,
   base.state as state,
   base.faa_region as faa_region,
   base.fac_type as fac_type,
   base.elevation as elevation
FROM `malloy-data.faa.airports` as base
ORDER BY 1 ASC

Reduction: SELECT with GROUP BY and/or aggregation

In SQL

SELECT
   base.fac_type as fac_type,
   COUNT( 1) as airport_count
FROM `malloy-data.faa.airports` as base
WHERE base.state='CA'
GROUP BY 1
ORDER BY 2 desc

Equivalent in Malloy

query: table('malloy-data.faa.airports') -> {
  group_by: fac_type
  aggregate: airport_count is count()
  where: state = 'CA'
  order_by: airport_count desc
}
QUERY RESULTS
fac_​typeairport_​count
AIRPORT569
HELIPORT396
SEAPLANE BASE12
GLIDERPORT3
STOLPORT2
ULTRALIGHT2
[
  {
    "fac_type": "AIRPORT",
    "airport_count": 569
  },
  {
    "fac_type": "HELIPORT",
    "airport_count": 396
  },
  {
    "fac_type": "SEAPLANE BASE",
    "airport_count": 12
  },
  {
    "fac_type": "GLIDERPORT",
    "airport_count": 3
  },
  {
    "fac_type": "STOLPORT",
    "airport_count": 2
  },
  {
    "fac_type": "ULTRALIGHT",
    "airport_count": 2
  }
]
SELECT 
   base.fac_type as fac_type,
   COUNT( 1) as airport_count
FROM `malloy-data.faa.airports` as base
WHERE base.state='CA'
GROUP BY 1
ORDER BY 2 desc

Source: A data source for queries

Malloy separates a query from the source of the data. A source can be thought of as a table and a collection of computations and relationships which are relevant to that table. (Source Documentation).

Fields can be defined as part of a source.

  • A measure: is a declared aggregate calculation (think function that operates across the table) which can be used in aggregate: elements in a query stage

  • A dimension: is a declared scalar calculation which that can be used in group_by: or project: elements of a query stage

source: airports is table('malloy-data.faa.airports') {
  dimension: elevation_in_meters is elevation * 0.3048
  dimension: state_and_county is concat(state,' - ', county)
  measure: airport_count is count()
  measure: avg_elevation_in_meters is elevation_in_meters.avg()
}

Querying against a Source

Queries can be run against source: objects and can utilize the modeled fields from that source, as well as introduce new ones. (Query Documentation)

using the above declared airports source

query: airports -> {
  limit: 10
  where: fac_type = 'HELIPORT'
  group_by: state
  aggregate:
    airport_count           // <-- declared in source
    avg_elevation_in_meters // <-- declared in source
}
QUERY RESULTS
stateairport_​countavg_​elevation_​in_​meters
TX435138.687
CA396276.402
PA307216.255
FL28018.101
NJ24748.15
[
  {
    "state": "TX",
    "airport_count": 435,
    "avg_elevation_in_meters": 138.68680275862076
  },
  {
    "state": "CA",
    "airport_count": 396,
    "avg_elevation_in_meters": 276.40203030303024
  },
  {
    "state": "PA",
    "airport_count": 307,
    "avg_elevation_in_meters": 216.2551035830619
  },
  {
    "state": "FL",
    "airport_count": 280,
    "avg_elevation_in_meters": 18.100765714285703
  },
  {
    "state": "NJ",
    "airport_count": 247,
    "avg_elevation_in_meters": 48.149761943319824
  }
]
SELECT 
   airports.state as state,
   COUNT( 1) as airport_count,
   AVG((airports.elevation*0.3048)) as avg_elevation_in_meters
FROM `malloy-data.faa.airports` as airports
WHERE airports.fac_type='HELIPORT'
GROUP BY 1
ORDER BY 2 desc
LIMIT 10

Dimensional calculations are no different from columns

using the above declared airports source

query: airports -> {
  group_by: state_and_county // <-- declared in source
  aggregate: airport_count
  order_by: 1 desc
}
QUERY RESULTS
state_​and_​countyairport_​count
WY - WESTON3
WY - WASHAKIE4
WY - UINTA3
WY - TETON7
WY - SWEETWATER4
[
  {
    "state_and_county": "WY - WESTON",
    "airport_count": 3
  },
  {
    "state_and_county": "WY - WASHAKIE",
    "airport_count": 4
  },
  {
    "state_and_county": "WY - UINTA",
    "airport_count": 3
  },
  {
    "state_and_county": "WY - TETON",
    "airport_count": 7
  },
  {
    "state_and_county": "WY - SWEETWATER",
    "airport_count": 4
  }
]
SELECT 
   concat(airports.state,' - ',airports.county) as state_and_county,
   COUNT( 1) as airport_count
FROM `malloy-data.faa.airports` as airports
GROUP BY 1
ORDER BY 1 desc

Defining Named Queries inside a Source

A source can also contain a set of useful queries relating to that source.

using the above declared airports source


source: airports is table('malloy-data.faa.airports') {
  measure: airport_count is count()

  query: by_state is {        // <-- can be called by name
    group_by: state
    aggregate: airport_count
  }
}

Note that the source is implied, so the query operator (->) and source are not needed to define the named query.

Executing Named Queries

The simplest form of a query in Malloy is the name of a source, the query operator ->, and the name of one of its contained queries.

using the above declared airports source

query: airports -> by_state
QUERY RESULTS
stateairport_​count
TX1,845
CA984
IL890
FL856
PA804
[
  {
    "state": "TX",
    "airport_count": 1845
  },
  {
    "state": "CA",
    "airport_count": 984
  },
  {
    "state": "IL",
    "airport_count": 890
  },
  {
    "state": "FL",
    "airport_count": 856
  },
  {
    "state": "PA",
    "airport_count": 804
  }
]
SELECT 
   airports.state as state,
   COUNT( 1) as airport_count
FROM `malloy-data.faa.airports` as airports
GROUP BY 1
ORDER BY 2 desc

Filtering a Source

You can filter a source by adding a filter expression using the where: keyword and then use this refined version of airports to run the by_state query. (Filter Documentation)

using the above declared airports source

query: airports  {
  where: fac_type = 'SEAPLANE BASE'   // <- run the query with an added filter
}
-> by_state
QUERY RESULTS
stateairport_​count
AK104
MN72
FL43
ME38
NY23
[
  {
    "state": "AK",
    "airport_count": 104
  },
  {
    "state": "MN",
    "airport_count": 72
  },
  {
    "state": "FL",
    "airport_count": 43
  },
  {
    "state": "ME",
    "airport_count": 38
  },
  {
    "state": "NY",
    "airport_count": 23
  }
]
SELECT 
   airports.state as state,
   COUNT( 1) as airport_count
FROM `malloy-data.faa.airports` as airports
WHERE (airports.fac_type='SEAPLANE BASE')
AND (airports.fac_type='SEAPLANE BASE')
GROUP BY 1
ORDER BY 2 desc

Filtering Measures

The input to an aggregate computation can be filtered.

using the above declared airports source

query: airports -> {
  group_by: state
  aggregate: airport_count
  aggregate: heliport_count is airport_count { where: fac_type = 'HELIPORT' } // <-- add a filter
}
QUERY RESULTS
stateairport_​countheliport_​count
TX1,845435
CA984396
IL890245
FL856280
PA804307
[
  {
    "state": "TX",
    "airport_count": 1845,
    "heliport_count": 435
  },
  {
    "state": "CA",
    "airport_count": 984,
    "heliport_count": 396
  },
  {
    "state": "IL",
    "airport_count": 890,
    "heliport_count": 245
  },
  {
    "state": "FL",
    "airport_count": 856,
    "heliport_count": 280
  },
  {
    "state": "PA",
    "airport_count": 804,
    "heliport_count": 307
  }
]
SELECT 
   airports.state as state,
   COUNT( 1) as airport_count,
   (COUNT( CASE WHEN airports.fac_type='HELIPORT' THEN 1 END)) as heliport_count
FROM `malloy-data.faa.airports` as airports
GROUP BY 1
ORDER BY 2 desc

Composing with Queries

For the next section assume the following source declaration.

source: airports is table('malloy-data.faa.airports') {
  measure: airport_count is count()
  measure: avg_elevation is elevation.avg()

  query: top_5_states is {
    group_by: state
    aggregate: airport_count
    limit: 5
  }

  query: by_facility_type is {
    group_by: fac_type
    aggregate: airport_count
  }
}

The nest: property embeds one query in another

Malloy allows you to create nested subtables easily in a query. In the case below, the top level query groups by state and nested query groups by facility type. This mechanism is really useful for understanding data and creating complex data structures. (Nesting Documentation)

using the above declared airports source

query: airports -> {
  group_by: state
  aggregate: airport_count
  limit: 5
  nest: by_facility_type is {
    group_by: fac_type
    aggregate: airport_count
  }
}
QUERY RESULTS
stateairport_​countby_​facility_​type
TX1,845
fac_​typeairport_​count
AIRPORT1,389
HELIPORT435
STOLPORT8
ULTRALIGHT8
GLIDERPORT5
CA984
fac_​typeairport_​count
AIRPORT569
HELIPORT396
SEAPLANE BASE12
GLIDERPORT3
STOLPORT2
ULTRALIGHT2
IL890
fac_​typeairport_​count
AIRPORT625
HELIPORT245
SEAPLANE BASE8
ULTRALIGHT6
BALLOONPORT2
STOLPORT2
GLIDERPORT2
FL856
fac_​typeairport_​count
AIRPORT511
HELIPORT280
SEAPLANE BASE43
STOLPORT13
ULTRALIGHT5
GLIDERPORT4
PA804
fac_​typeairport_​count
AIRPORT468
HELIPORT307
ULTRALIGHT13
SEAPLANE BASE10
STOLPORT3
GLIDERPORT3
[
  {
    "state": "TX",
    "airport_count": 1845,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1389
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 435
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 8
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 5
      }
    ]
  },
  {
    "state": "CA",
    "airport_count": 984,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 569
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 396
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 12
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "IL",
    "airport_count": 890,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 625
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 245
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 8
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 6
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 2
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 856,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 511
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 280
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 43
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 13
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 5
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "PA",
    "airport_count": 804,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 468
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 307
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 13
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 10
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 3
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1) THEN
      airports.state
      END as state__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set=1 THEN
      airports.fac_type
      END as fac_type__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  GROUP BY 1,2,4
)
SELECT
  state__0 as state,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    fac_type__1 as fac_type, 
    airport_count__1 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as by_facility_type
FROM __stage0
GROUP BY 1
ORDER BY 2 desc
LIMIT 5

Queries can contain multiple nested queries.

using the above declared airports source

query: airports -> {
  group_by: faa_region
  aggregate: airport_count
  nest: top_5_states
  nest: by_facility_type
}
QUERY RESULTS
faa_​regionairport_​counttop_​5_​statesby_​facility_​type
AGL4,437
stateairport_​count
IL890
OH749
IN643
WI543
MN507
fac_​typeairport_​count
AIRPORT3,443
HELIPORT826
SEAPLANE BASE119
ULTRALIGHT30
STOLPORT11
GLIDERPORT4
BALLOONPORT4
ASW3,268
stateairport_​count
TX1,845
LA500
OK443
AR299
NM181
fac_​typeairport_​count
AIRPORT2,341
HELIPORT861
ULTRALIGHT32
SEAPLANE BASE19
STOLPORT9
GLIDERPORT6
ASO2,924
stateairport_​count
FL856
GA440
NC400
TN285
AL260
fac_​typeairport_​count
AIRPORT2,038
HELIPORT770
SEAPLANE BASE57
STOLPORT33
ULTRALIGHT17
GLIDERPORT8
BALLOONPORT1
AEA2,586
stateairport_​count
PA804
NY576
VA421
NJ378
MD229
fac_​typeairport_​count
AIRPORT1,525
HELIPORT964
SEAPLANE BASE61
ULTRALIGHT18
STOLPORT8
GLIDERPORT7
BALLOONPORT3
ANM2,102
stateairport_​count
WA484
OR441
CO425
MT259
ID238
fac_​typeairport_​count
AIRPORT1,524
HELIPORT527
SEAPLANE BASE25
STOLPORT13
ULTRALIGHT8
GLIDERPORT4
BALLOONPORT1
[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "top_5_states": [
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MN",
        "airport_count": 507
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 3443
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 826
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 119
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 30
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 11
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 4
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "top_5_states": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "NM",
        "airport_count": 181
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 2341
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 861
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 32
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 19
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 9
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 6
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "top_5_states": [
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 2038
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 770
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 57
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 33
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 17
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 8
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "top_5_states": [
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "MD",
        "airport_count": 229
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1525
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 964
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 61
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 18
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 7
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 3
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "top_5_states": [
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "ID",
        "airport_count": 238
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1524
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 527
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 25
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 13
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 1
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1,2) THEN
      airports.faa_region
      END as faa_region__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set=1 THEN
      airports.state
      END as state__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1,
    CASE WHEN group_set=2 THEN
      airports.fac_type
      END as fac_type__2,
    CASE WHEN group_set=2 THEN
      COUNT( 1)
      END as airport_count__2
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,2,1)))
  GROUP BY 1,2,4,6
)
SELECT
  faa_region__0 as faa_region,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    state__1 as state, 
    airport_count__1 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc  LIMIT 5) as top_5_states,
  ARRAY_AGG(CASE WHEN group_set=2 THEN STRUCT(
    fac_type__2 as fac_type, 
    airport_count__2 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__2 desc ) as by_facility_type
FROM __stage0
GROUP BY 1
ORDER BY 2 desc

Queries can be nested to any level of depth.

using the above declared airports source

query: airports -> {
  group_by: faa_region
  aggregate: airport_count
  nest: by_state_and_county is {
    group_by: state
    aggregate: airport_count
    nest: by_county is {
      group_by: county
      aggregate: airport_count
      limit: 4
    }
  }
  nest: by_facility_type
}
QUERY RESULTS
faa_​regionairport_​countby_​state_​and_​countyby_​facility_​type
AGL4,437
stateairport_​countby_​county
IL890
countyairport_​count
COOK51
LA SALLE39
MC HENRY29
DE KALB27
OH749
countyairport_​count
CUYAHOGA27
FRANKLIN27
STARK23
MONTGOMERY22
IN643
countyairport_​count
MARION27
ALLEN24
HAMILTON20
MARSHALL18
WI543
countyairport_​count
DANE30
WALWORTH22
WINNEBAGO17
ST CROIX16
MN507
countyairport_​count
ST LOUIS28
HENNEPIN23
DAKOTA17
CROW WING17
MI489
countyairport_​count
OAKLAND25
KENT24
WAYNE17
LIVINGSTON15
ND436
countyairport_​count
CASS39
RICHLAND20
MC LEAN20
WARD19
SD180
countyairport_​count
PENNINGTON7
MEADE7
MINNEHAHA7
BROWN6
fac_​typeairport_​count
AIRPORT3,443
HELIPORT826
SEAPLANE BASE119
ULTRALIGHT30
STOLPORT11
GLIDERPORT4
BALLOONPORT4
ASW3,268
stateairport_​countby_​county
TX1,845
countyairport_​count
HARRIS135
TARRANT63
DENTON53
DALLAS42
LA500
countyairport_​count
PLAQUEMINES31
VERMILION29
CALCASIEU23
LAFOURCHE21
OK443
countyairport_​count
OKLAHOMA31
TULSA25
ROGERS16
DELAWARE13
AR299
countyairport_​count
PULASKI20
BENTON19
LONOKE13
MISSISSIPPI10
NM181
countyairport_​count
CATRON13
LINCOLN10
LUNA9
SANTA FE9
fac_​typeairport_​count
AIRPORT2,341
HELIPORT861
ULTRALIGHT32
SEAPLANE BASE19
STOLPORT9
GLIDERPORT6
ASO2,924
stateairport_​countby_​county
FL856
countyairport_​count
PALM BEACH45
DADE44
POLK43
MARION37
GA440
countyairport_​count
FULTON22
PIKE17
CARROLL14
FAYETTE12
NC400
countyairport_​count
WAKE15
MECKLENBURG14
UNION14
ROWAN14
TN285
countyairport_​count
SHELBY24
DAVIDSON17
KNOX14
RUTHERFORD10
AL260
countyairport_​count
BALDWIN25
JEFFERSON19
MOBILE17
MADISON14
MS243
countyairport_​count
WASHINGTON15
HARRISON12
HINDS11
BOLIVAR9
KY202
countyairport_​count
JEFFERSON13
FAYETTE7
MC LEAN6
BOONE6
SC189
countyairport_​count
GREENVILLE13
LEXINGTON9
BEAUFORT9
CHARLESTON9
PR40
countyairport_​count
--PUERTO RICO40
VI9
countyairport_​count
-VIRGIN ISLANDS-9
fac_​typeairport_​count
AIRPORT2,038
HELIPORT770
SEAPLANE BASE57
STOLPORT33
ULTRALIGHT17
GLIDERPORT8
BALLOONPORT1
AEA2,586
stateairport_​countby_​county
PA804
countyairport_​count
BUCKS55
MONTGOMERY44
ALLEGHENY31
CHESTER27
NY576
countyairport_​count
SUFFOLK34
ERIE26
DUTCHESS20
NIAGARA20
VA421
countyairport_​count
FAUQUIER23
SHENANDOAH13
FAIRFAX12
ACCOMACK12
NJ378
countyairport_​count
MONMOUTH31
MIDDLESEX29
BURLINGTON29
HUNTERDON27
MD229
countyairport_​count
BALTIMORE24
CARROLL14
ANNE ARUNDEL14
PRINCE GEORGES14
WV116
countyairport_​count
KANAWHA8
MASON7
RALEIGH6
PRESTON6
DE42
countyairport_​count
KENT17
SUSSEX14
NEW CASTLE11
DC20
countyairport_​count
WASHINGTON18
ARLINGTON1
LOUDOUN1
fac_​typeairport_​count
AIRPORT1,525
HELIPORT964
SEAPLANE BASE61
ULTRALIGHT18
STOLPORT8
GLIDERPORT7
BALLOONPORT3
ANM2,102
stateairport_​countby_​county
WA484
countyairport_​count
KING61
PIERCE27
SPOKANE26
SNOHOMISH25
OR441
countyairport_​count
CLACKAMAS34
LINN26
LANE24
WASHINGTON24
CO425
countyairport_​count
WELD40
EL PASO26
ADAMS23
JEFFERSON21
MT259
countyairport_​count
FLATHEAD20
LEWIS AND CLARK16
MISSOULA14
GALLATIN11
ID238
countyairport_​count
VALLEY27
KOOTENAI21
IDAHO18
BONNER18
UT140
countyairport_​count
SALT LAKE21
SAN JUAN13
UTAH10
IRON9
WY115
countyairport_​count
LARAMIE12
CARBON9
PARK9
CAMPBELL9
fac_​typeairport_​count
AIRPORT1,524
HELIPORT527
SEAPLANE BASE25
STOLPORT13
ULTRALIGHT8
GLIDERPORT4
BALLOONPORT1
[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "by_state_and_county": [
      {
        "state": "IL",
        "airport_count": 890,
        "by_county": [
          {
            "county": "COOK",
            "airport_count": 51
          },
          {
            "county": "LA SALLE",
            "airport_count": 39
          },
          {
            "county": "MC HENRY",
            "airport_count": 29
          },
          {
            "county": "DE KALB",
            "airport_count": 27
          }
        ]
      },
      {
        "state": "OH",
        "airport_count": 749,
        "by_county": [
          {
            "county": "CUYAHOGA",
            "airport_count": 27
          },
          {
            "county": "FRANKLIN",
            "airport_count": 27
          },
          {
            "county": "STARK",
            "airport_count": 23
          },
          {
            "county": "MONTGOMERY",
            "airport_count": 22
          }
        ]
      },
      {
        "state": "IN",
        "airport_count": 643,
        "by_county": [
          {
            "county": "MARION",
            "airport_count": 27
          },
          {
            "county": "ALLEN",
            "airport_count": 24
          },
          {
            "county": "HAMILTON",
            "airport_count": 20
          },
          {
            "county": "MARSHALL",
            "airport_count": 18
          }
        ]
      },
      {
        "state": "WI",
        "airport_count": 543,
        "by_county": [
          {
            "county": "DANE",
            "airport_count": 30
          },
          {
            "county": "WALWORTH",
            "airport_count": 22
          },
          {
            "county": "WINNEBAGO",
            "airport_count": 17
          },
          {
            "county": "ST CROIX",
            "airport_count": 16
          }
        ]
      },
      {
        "state": "MN",
        "airport_count": 507,
        "by_county": [
          {
            "county": "ST LOUIS",
            "airport_count": 28
          },
          {
            "county": "HENNEPIN",
            "airport_count": 23
          },
          {
            "county": "DAKOTA",
            "airport_count": 17
          },
          {
            "county": "CROW WING",
            "airport_count": 17
          }
        ]
      },
      {
        "state": "MI",
        "airport_count": 489,
        "by_county": [
          {
            "county": "OAKLAND",
            "airport_count": 25
          },
          {
            "county": "KENT",
            "airport_count": 24
          },
          {
            "county": "WAYNE",
            "airport_count": 17
          },
          {
            "county": "LIVINGSTON",
            "airport_count": 15
          }
        ]
      },
      {
        "state": "ND",
        "airport_count": 436,
        "by_county": [
          {
            "county": "CASS",
            "airport_count": 39
          },
          {
            "county": "RICHLAND",
            "airport_count": 20
          },
          {
            "county": "MC LEAN",
            "airport_count": 20
          },
          {
            "county": "WARD",
            "airport_count": 19
          }
        ]
      },
      {
        "state": "SD",
        "airport_count": 180,
        "by_county": [
          {
            "county": "PENNINGTON",
            "airport_count": 7
          },
          {
            "county": "MEADE",
            "airport_count": 7
          },
          {
            "county": "MINNEHAHA",
            "airport_count": 7
          },
          {
            "county": "BROWN",
            "airport_count": 6
          }
        ]
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 3443
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 826
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 119
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 30
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 11
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 4
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "by_state_and_county": [
      {
        "state": "TX",
        "airport_count": 1845,
        "by_county": [
          {
            "county": "HARRIS",
            "airport_count": 135
          },
          {
            "county": "TARRANT",
            "airport_count": 63
          },
          {
            "county": "DENTON",
            "airport_count": 53
          },
          {
            "county": "DALLAS",
            "airport_count": 42
          }
        ]
      },
      {
        "state": "LA",
        "airport_count": 500,
        "by_county": [
          {
            "county": "PLAQUEMINES",
            "airport_count": 31
          },
          {
            "county": "VERMILION",
            "airport_count": 29
          },
          {
            "county": "CALCASIEU",
            "airport_count": 23
          },
          {
            "county": "LAFOURCHE",
            "airport_count": 21
          }
        ]
      },
      {
        "state": "OK",
        "airport_count": 443,
        "by_county": [
          {
            "county": "OKLAHOMA",
            "airport_count": 31
          },
          {
            "county": "TULSA",
            "airport_count": 25
          },
          {
            "county": "ROGERS",
            "airport_count": 16
          },
          {
            "county": "DELAWARE",
            "airport_count": 13
          }
        ]
      },
      {
        "state": "AR",
        "airport_count": 299,
        "by_county": [
          {
            "county": "PULASKI",
            "airport_count": 20
          },
          {
            "county": "BENTON",
            "airport_count": 19
          },
          {
            "county": "LONOKE",
            "airport_count": 13
          },
          {
            "county": "MISSISSIPPI",
            "airport_count": 10
          }
        ]
      },
      {
        "state": "NM",
        "airport_count": 181,
        "by_county": [
          {
            "county": "CATRON",
            "airport_count": 13
          },
          {
            "county": "LINCOLN",
            "airport_count": 10
          },
          {
            "county": "LUNA",
            "airport_count": 9
          },
          {
            "county": "SANTA FE",
            "airport_count": 9
          }
        ]
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 2341
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 861
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 32
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 19
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 9
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 6
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "by_state_and_county": [
      {
        "state": "FL",
        "airport_count": 856,
        "by_county": [
          {
            "county": "PALM BEACH",
            "airport_count": 45
          },
          {
            "county": "DADE",
            "airport_count": 44
          },
          {
            "county": "POLK",
            "airport_count": 43
          },
          {
            "county": "MARION",
            "airport_count": 37
          }
        ]
      },
      {
        "state": "GA",
        "airport_count": 440,
        "by_county": [
          {
            "county": "FULTON",
            "airport_count": 22
          },
          {
            "county": "PIKE",
            "airport_count": 17
          },
          {
            "county": "CARROLL",
            "airport_count": 14
          },
          {
            "county": "FAYETTE",
            "airport_count": 12
          }
        ]
      },
      {
        "state": "NC",
        "airport_count": 400,
        "by_county": [
          {
            "county": "WAKE",
            "airport_count": 15
          },
          {
            "county": "MECKLENBURG",
            "airport_count": 14
          },
          {
            "county": "UNION",
            "airport_count": 14
          },
          {
            "county": "ROWAN",
            "airport_count": 14
          }
        ]
      },
      {
        "state": "TN",
        "airport_count": 285,
        "by_county": [
          {
            "county": "SHELBY",
            "airport_count": 24
          },
          {
            "county": "DAVIDSON",
            "airport_count": 17
          },
          {
            "county": "KNOX",
            "airport_count": 14
          },
          {
            "county": "RUTHERFORD",
            "airport_count": 10
          }
        ]
      },
      {
        "state": "AL",
        "airport_count": 260,
        "by_county": [
          {
            "county": "BALDWIN",
            "airport_count": 25
          },
          {
            "county": "JEFFERSON",
            "airport_count": 19
          },
          {
            "county": "MOBILE",
            "airport_count": 17
          },
          {
            "county": "MADISON",
            "airport_count": 14
          }
        ]
      },
      {
        "state": "MS",
        "airport_count": 243,
        "by_county": [
          {
            "county": "WASHINGTON",
            "airport_count": 15
          },
          {
            "county": "HARRISON",
            "airport_count": 12
          },
          {
            "county": "HINDS",
            "airport_count": 11
          },
          {
            "county": "BOLIVAR",
            "airport_count": 9
          }
        ]
      },
      {
        "state": "KY",
        "airport_count": 202,
        "by_county": [
          {
            "county": "JEFFERSON",
            "airport_count": 13
          },
          {
            "county": "FAYETTE",
            "airport_count": 7
          },
          {
            "county": "MC LEAN",
            "airport_count": 6
          },
          {
            "county": "BOONE",
            "airport_count": 6
          }
        ]
      },
      {
        "state": "SC",
        "airport_count": 189,
        "by_county": [
          {
            "county": "GREENVILLE",
            "airport_count": 13
          },
          {
            "county": "LEXINGTON",
            "airport_count": 9
          },
          {
            "county": "BEAUFORT",
            "airport_count": 9
          },
          {
            "county": "CHARLESTON",
            "airport_count": 9
          }
        ]
      },
      {
        "state": "PR",
        "airport_count": 40,
        "by_county": [
          {
            "county": "--PUERTO RICO",
            "airport_count": 40
          }
        ]
      },
      {
        "state": "VI",
        "airport_count": 9,
        "by_county": [
          {
            "county": "-VIRGIN ISLANDS-",
            "airport_count": 9
          }
        ]
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 2038
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 770
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 57
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 33
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 17
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 8
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "by_state_and_county": [
      {
        "state": "PA",
        "airport_count": 804,
        "by_county": [
          {
            "county": "BUCKS",
            "airport_count": 55
          },
          {
            "county": "MONTGOMERY",
            "airport_count": 44
          },
          {
            "county": "ALLEGHENY",
            "airport_count": 31
          },
          {
            "county": "CHESTER",
            "airport_count": 27
          }
        ]
      },
      {
        "state": "NY",
        "airport_count": 576,
        "by_county": [
          {
            "county": "SUFFOLK",
            "airport_count": 34
          },
          {
            "county": "ERIE",
            "airport_count": 26
          },
          {
            "county": "DUTCHESS",
            "airport_count": 20
          },
          {
            "county": "NIAGARA",
            "airport_count": 20
          }
        ]
      },
      {
        "state": "VA",
        "airport_count": 421,
        "by_county": [
          {
            "county": "FAUQUIER",
            "airport_count": 23
          },
          {
            "county": "SHENANDOAH",
            "airport_count": 13
          },
          {
            "county": "FAIRFAX",
            "airport_count": 12
          },
          {
            "county": "ACCOMACK",
            "airport_count": 12
          }
        ]
      },
      {
        "state": "NJ",
        "airport_count": 378,
        "by_county": [
          {
            "county": "MONMOUTH",
            "airport_count": 31
          },
          {
            "county": "MIDDLESEX",
            "airport_count": 29
          },
          {
            "county": "BURLINGTON",
            "airport_count": 29
          },
          {
            "county": "HUNTERDON",
            "airport_count": 27
          }
        ]
      },
      {
        "state": "MD",
        "airport_count": 229,
        "by_county": [
          {
            "county": "BALTIMORE",
            "airport_count": 24
          },
          {
            "county": "CARROLL",
            "airport_count": 14
          },
          {
            "county": "ANNE ARUNDEL",
            "airport_count": 14
          },
          {
            "county": "PRINCE GEORGES",
            "airport_count": 14
          }
        ]
      },
      {
        "state": "WV",
        "airport_count": 116,
        "by_county": [
          {
            "county": "KANAWHA",
            "airport_count": 8
          },
          {
            "county": "MASON",
            "airport_count": 7
          },
          {
            "county": "RALEIGH",
            "airport_count": 6
          },
          {
            "county": "PRESTON",
            "airport_count": 6
          }
        ]
      },
      {
        "state": "DE",
        "airport_count": 42,
        "by_county": [
          {
            "county": "KENT",
            "airport_count": 17
          },
          {
            "county": "SUSSEX",
            "airport_count": 14
          },
          {
            "county": "NEW CASTLE",
            "airport_count": 11
          }
        ]
      },
      {
        "state": "DC",
        "airport_count": 20,
        "by_county": [
          {
            "county": "WASHINGTON",
            "airport_count": 18
          },
          {
            "county": "ARLINGTON",
            "airport_count": 1
          },
          {
            "county": "LOUDOUN",
            "airport_count": 1
          }
        ]
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1525
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 964
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 61
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 18
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 7
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 3
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "by_state_and_county": [
      {
        "state": "WA",
        "airport_count": 484,
        "by_county": [
          {
            "county": "KING",
            "airport_count": 61
          },
          {
            "county": "PIERCE",
            "airport_count": 27
          },
          {
            "county": "SPOKANE",
            "airport_count": 26
          },
          {
            "county": "SNOHOMISH",
            "airport_count": 25
          }
        ]
      },
      {
        "state": "OR",
        "airport_count": 441,
        "by_county": [
          {
            "county": "CLACKAMAS",
            "airport_count": 34
          },
          {
            "county": "LINN",
            "airport_count": 26
          },
          {
            "county": "LANE",
            "airport_count": 24
          },
          {
            "county": "WASHINGTON",
            "airport_count": 24
          }
        ]
      },
      {
        "state": "CO",
        "airport_count": 425,
        "by_county": [
          {
            "county": "WELD",
            "airport_count": 40
          },
          {
            "county": "EL PASO",
            "airport_count": 26
          },
          {
            "county": "ADAMS",
            "airport_count": 23
          },
          {
            "county": "JEFFERSON",
            "airport_count": 21
          }
        ]
      },
      {
        "state": "MT",
        "airport_count": 259,
        "by_county": [
          {
            "county": "FLATHEAD",
            "airport_count": 20
          },
          {
            "county": "LEWIS AND CLARK",
            "airport_count": 16
          },
          {
            "county": "MISSOULA",
            "airport_count": 14
          },
          {
            "county": "GALLATIN",
            "airport_count": 11
          }
        ]
      },
      {
        "state": "ID",
        "airport_count": 238,
        "by_county": [
          {
            "county": "VALLEY",
            "airport_count": 27
          },
          {
            "county": "KOOTENAI",
            "airport_count": 21
          },
          {
            "county": "IDAHO",
            "airport_count": 18
          },
          {
            "county": "BONNER",
            "airport_count": 18
          }
        ]
      },
      {
        "state": "UT",
        "airport_count": 140,
        "by_county": [
          {
            "county": "SALT LAKE",
            "airport_count": 21
          },
          {
            "county": "SAN JUAN",
            "airport_count": 13
          },
          {
            "county": "UTAH",
            "airport_count": 10
          },
          {
            "county": "IRON",
            "airport_count": 9
          }
        ]
      },
      {
        "state": "WY",
        "airport_count": 115,
        "by_county": [
          {
            "county": "LARAMIE",
            "airport_count": 12
          },
          {
            "county": "CARBON",
            "airport_count": 9
          },
          {
            "county": "PARK",
            "airport_count": 9
          },
          {
            "county": "CAMPBELL",
            "airport_count": 9
          }
        ]
      }
    ],
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1524
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 527
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 25
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 13
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 1
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1,2,3) THEN
      airports.faa_region
      END as faa_region__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set IN (1,2) THEN
      airports.state
      END as state__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1,
    CASE WHEN group_set=2 THEN
      airports.county
      END as county__2,
    CASE WHEN group_set=2 THEN
      COUNT( 1)
      END as airport_count__2,
    CASE WHEN group_set=3 THEN
      airports.fac_type
      END as fac_type__3,
    CASE WHEN group_set=3 THEN
      COUNT( 1)
      END as airport_count__3
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,3,1)))
  GROUP BY 1,2,4,6,8
)
, __stage1 AS (
  SELECT 
    CASE WHEN group_set=2 THEN 1  ELSE group_set END as group_set,
    faa_region__0 as faa_region__0,
    ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count__0,
    CASE WHEN group_set IN (1,2) THEN
      state__1
      END as state__1,
    ANY_VALUE(CASE WHEN group_set=1 THEN airport_count__1 END) as airport_count__1,
    ARRAY_AGG(CASE WHEN group_set=2 THEN STRUCT(
      county__2 as county, 
      airport_count__2 as airport_count
      ) END IGNORE NULLS  ORDER BY  airport_count__2 desc  LIMIT 4) as by_county__1,
    CASE WHEN group_set=3 THEN
      fac_type__3
      END as fac_type__3,
    ANY_VALUE(CASE WHEN group_set=3 THEN airport_count__3 END) as airport_count__3
  FROM __stage0
  GROUP BY 1,2,4,7
)
SELECT
  faa_region__0 as faa_region,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    state__1 as state, 
    airport_count__1 as airport_count, 
    by_county__1 as by_county
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as by_state_and_county,
  ARRAY_AGG(CASE WHEN group_set=3 THEN STRUCT(
    fac_type__3 as fac_type, 
    airport_count__3 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__3 desc ) as by_facility_type
FROM __stage1
GROUP BY 1
ORDER BY 2 desc

Refining a Named Query

The refinement gesture {} extends an existing object, creating a new version with added properties

For example we can add a limit and an order by to by_state

query: airports -> by_state {
  order_by: state desc    // <-- add order by to query
  limit: 2
}

is the same as

query: airports -> {
  group_by: state
  aggregate: airport_count
  order_by: state desc
  limit: 2
}
QUERY RESULTS
stateairport_​count
WY115
WV116
[
  {
    "state": "WY",
    "airport_count": 115
  },
  {
    "state": "WV",
    "airport_count": 116
  }
]
SELECT 
   airports.state as state,
   COUNT( 1) as airport_count
FROM `malloy-data.faa.airports` as airports
GROUP BY 1
ORDER BY 1 desc
LIMIT 2

You can add a measure or dimension

query: airports -> by_facility_type {
  aggregate: avg_elevation
}
QUERY RESULTS
fac_​typeairport_​countavg_​elevation
AIRPORT13,9251,237.044
HELIPORT5,135950.513
SEAPLANE BASE473488.822
ULTRALIGHT125806.144
STOLPORT861,375.047
[
  {
    "fac_type": "AIRPORT",
    "airport_count": 13925,
    "avg_elevation": 1237.04416517057
  },
  {
    "fac_type": "HELIPORT",
    "airport_count": 5135,
    "avg_elevation": 950.5125608568657
  },
  {
    "fac_type": "SEAPLANE BASE",
    "airport_count": 473,
    "avg_elevation": 488.82241014799155
  },
  {
    "fac_type": "ULTRALIGHT",
    "airport_count": 125,
    "avg_elevation": 806.1439999999999
  },
  {
    "fac_type": "STOLPORT",
    "airport_count": 86,
    "avg_elevation": 1375.0465116279065
  }
]
SELECT 
   airports.fac_type as fac_type,
   COUNT( 1) as airport_count,
   AVG(airports.elevation) as avg_elevation
FROM `malloy-data.faa.airports` as airports
GROUP BY 1
ORDER BY 2 desc

You can nest another query

query: airports -> top_5_states {
  nest: by_facility_type
}
QUERY RESULTS
stateairport_​countby_​facility_​type
TX1,845
fac_​typeairport_​count
AIRPORT1,389
HELIPORT435
STOLPORT8
ULTRALIGHT8
GLIDERPORT5
CA984
fac_​typeairport_​count
AIRPORT569
HELIPORT396
SEAPLANE BASE12
GLIDERPORT3
STOLPORT2
ULTRALIGHT2
IL890
fac_​typeairport_​count
AIRPORT625
HELIPORT245
SEAPLANE BASE8
ULTRALIGHT6
BALLOONPORT2
STOLPORT2
GLIDERPORT2
FL856
fac_​typeairport_​count
AIRPORT511
HELIPORT280
SEAPLANE BASE43
STOLPORT13
ULTRALIGHT5
GLIDERPORT4
PA804
fac_​typeairport_​count
AIRPORT468
HELIPORT307
ULTRALIGHT13
SEAPLANE BASE10
STOLPORT3
GLIDERPORT3
[
  {
    "state": "TX",
    "airport_count": 1845,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1389
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 435
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 8
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 5
      }
    ]
  },
  {
    "state": "CA",
    "airport_count": 984,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 569
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 396
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 12
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "IL",
    "airport_count": 890,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 625
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 245
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 8
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 6
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 2
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 856,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 511
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 280
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 43
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 13
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 5
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "PA",
    "airport_count": 804,
    "by_facility_type": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 468
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 307
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 13
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 10
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 3
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1) THEN
      airports.state
      END as state__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set=1 THEN
      airports.fac_type
      END as fac_type__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  GROUP BY 1,2,4
)
SELECT
  state__0 as state,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    fac_type__1 as fac_type, 
    airport_count__1 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as by_facility_type
FROM __stage0
GROUP BY 1
ORDER BY 2 desc
LIMIT 5

Composing with Queries

Changing the inner and outer query in the example above reveals very different information.

query: airports-> by_facility_type {
  nest: top_5_states
}
QUERY RESULTS
fac_​typeairport_​counttop_​5_​states
AIRPORT13,925
stateairport_​count
TX1,389
IL625
CA569
OH537
FL511
HELIPORT5,135
stateairport_​count
TX435
CA396
PA307
FL280
NJ247
SEAPLANE BASE473
stateairport_​count
AK104
MN72
FL43
ME38
NY23
ULTRALIGHT125
stateairport_​count
LA18
IN17
PA13
TX8
AZ7
STOLPORT86
stateairport_​count
FL13
TN9
TX8
CO6
GA4
[
  {
    "fac_type": "AIRPORT",
    "airport_count": 13925,
    "top_5_states": [
      {
        "state": "TX",
        "airport_count": 1389
      },
      {
        "state": "IL",
        "airport_count": 625
      },
      {
        "state": "CA",
        "airport_count": 569
      },
      {
        "state": "OH",
        "airport_count": 537
      },
      {
        "state": "FL",
        "airport_count": 511
      }
    ]
  },
  {
    "fac_type": "HELIPORT",
    "airport_count": 5135,
    "top_5_states": [
      {
        "state": "TX",
        "airport_count": 435
      },
      {
        "state": "CA",
        "airport_count": 396
      },
      {
        "state": "PA",
        "airport_count": 307
      },
      {
        "state": "FL",
        "airport_count": 280
      },
      {
        "state": "NJ",
        "airport_count": 247
      }
    ]
  },
  {
    "fac_type": "SEAPLANE BASE",
    "airport_count": 473,
    "top_5_states": [
      {
        "state": "AK",
        "airport_count": 104
      },
      {
        "state": "MN",
        "airport_count": 72
      },
      {
        "state": "FL",
        "airport_count": 43
      },
      {
        "state": "ME",
        "airport_count": 38
      },
      {
        "state": "NY",
        "airport_count": 23
      }
    ]
  },
  {
    "fac_type": "ULTRALIGHT",
    "airport_count": 125,
    "top_5_states": [
      {
        "state": "LA",
        "airport_count": 18
      },
      {
        "state": "IN",
        "airport_count": 17
      },
      {
        "state": "PA",
        "airport_count": 13
      },
      {
        "state": "TX",
        "airport_count": 8
      },
      {
        "state": "AZ",
        "airport_count": 7
      }
    ]
  },
  {
    "fac_type": "STOLPORT",
    "airport_count": 86,
    "top_5_states": [
      {
        "state": "FL",
        "airport_count": 13
      },
      {
        "state": "TN",
        "airport_count": 9
      },
      {
        "state": "TX",
        "airport_count": 8
      },
      {
        "state": "CO",
        "airport_count": 6
      },
      {
        "state": "GA",
        "airport_count": 4
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1) THEN
      airports.fac_type
      END as fac_type__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set=1 THEN
      airports.state
      END as state__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  GROUP BY 1,2,4
)
SELECT
  fac_type__0 as fac_type,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    state__1 as state, 
    airport_count__1 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc  LIMIT 5) as top_5_states
FROM __stage0
GROUP BY 1
ORDER BY 2 desc

Joining

First let's model some simple tables... (Join Documentation)

Carrier table

simple source declaration used in example below

source: carriers is table('malloy-data.faa.carriers') {
  measure: carrier_count is count()
}

query: carriers-> {
    project: *
}
QUERY RESULTS
codenamenickname
EVAtlantic Southeast AirlinesAtlantic Southeast
NWNorthwest AirlinesNorthwest
AAAmerican AirlinesAmerican
FLAirtran Airways CorporationAirtran
B6Jetblue AirwaysJetblue
OOSky West AirlinesSky West
COContinental AirlinesContinental
OHComair (Delta Connections)Comair
TWTrans World AirlinesTWA
HAHawaiian AirlinesHawaiian
USUS AirwaysUSAir
RUContinental Express AirlinesContinental Express
WNSouthwest AirlinesSouthwest
AQAloha AirlinesAloha
ASAlaska AirlinesAlaska
HPAmerica West AirlinesAmerica West
DLDelta Air LinesDelta
DHAtlantic Coast AirlinesAtlantic Coast
TZAmerican Trans Air, Inc.ATA
UAUnited AirlinesUnited
MQAmerican Eagle AirlinesAmerican Eagle
[
  {
    "code": "EV",
    "name": "Atlantic Southeast Airlines",
    "nickname": "Atlantic Southeast"
  },
  {
    "code": "NW",
    "name": "Northwest Airlines",
    "nickname": "Northwest"
  },
  {
    "code": "AA",
    "name": "American Airlines",
    "nickname": "American"
  },
  {
    "code": "FL",
    "name": "Airtran Airways Corporation",
    "nickname": "Airtran"
  },
  {
    "code": "B6",
    "name": "Jetblue Airways",
    "nickname": "Jetblue"
  },
  {
    "code": "OO",
    "name": "Sky West Airlines",
    "nickname": "Sky West"
  },
  {
    "code": "CO",
    "name": "Continental Airlines",
    "nickname": "Continental"
  },
  {
    "code": "OH",
    "name": "Comair (Delta Connections)",
    "nickname": "Comair"
  },
  {
    "code": "TW",
    "name": "Trans World Airlines",
    "nickname": "TWA"
  },
  {
    "code": "HA",
    "name": "Hawaiian Airlines",
    "nickname": "Hawaiian"
  },
  {
    "code": "US",
    "name": "US Airways",
    "nickname": "USAir"
  },
  {
    "code": "RU",
    "name": "Continental Express Airlines",
    "nickname": "Continental Express"
  },
  {
    "code": "WN",
    "name": "Southwest Airlines",
    "nickname": "Southwest"
  },
  {
    "code": "AQ",
    "name": "Aloha Airlines",
    "nickname": "Aloha"
  },
  {
    "code": "AS",
    "name": "Alaska Airlines",
    "nickname": "Alaska"
  },
  {
    "code": "HP",
    "name": "America West Airlines",
    "nickname": "America West"
  },
  {
    "code": "DL",
    "name": "Delta Air Lines",
    "nickname": "Delta"
  },
  {
    "code": "DH",
    "name": "Atlantic Coast Airlines",
    "nickname": "Atlantic Coast"
  },
  {
    "code": "TZ",
    "name": "American Trans Air, Inc.",
    "nickname": "ATA"
  },
  {
    "code": "UA",
    "name": "United Airlines",
    "nickname": "United"
  },
  {
    "code": "MQ",
    "name": "American Eagle Airlines",
    "nickname": "American Eagle"
  }
]
SELECT 
   carriers.code as code,
   carriers.name as name,
   carriers.nickname as nickname
FROM `malloy-data.faa.carriers` as carriers

Flights table

simple source declaration used in example below

source: flights is table('malloy-data.faa.flights') {
  measure: flight_count is count()
}

query: flights -> {
  project: id2, tail_num, dep_time, carrier, origin, destination, distance, dep_delay
  limit: 10
}
QUERY RESULTS
id2tail_​numdep_​timecarrierorigindestinationdistancedep_​delay
4,999,524N509SW2000-09-06 09:50:00WNSANOAK4460
6,521,785N641SW2000-12-25 09:10:00WNPHXSAN3040
12,653,577N521SW2002-01-28 06:30:00WNSJCLAS3860
14,507,879N534AU2002-05-04 17:22:00USGSOPHL365-10
15,121,554N390US2002-07-09 21:21:00USPHLBOS280111
15,584,159N384US2002-08-06 19:28:00USPHLBOS280-2
3,260,909N709AS2000-06-30 11:04:00ASFAIANC2613
11,980,600N5EVAA2001-11-03 23:53:00AAOGGSJC2,356-3
13,786,638N639SW2002-04-28 18:22:00WNOAKBUR3257
15,030,219N5EDAA2002-06-30 22:54:00AAOGGSJC2,356-3
[
  {
    "id2": 4999524,
    "tail_num": "N509SW",
    "dep_time": {
      "value": "2000-09-06T09:50:00.000Z"
    },
    "carrier": "WN",
    "origin": "SAN",
    "destination": "OAK",
    "distance": 446,
    "dep_delay": 0
  },
  {
    "id2": 6521785,
    "tail_num": "N641SW",
    "dep_time": {
      "value": "2000-12-25T09:10:00.000Z"
    },
    "carrier": "WN",
    "origin": "PHX",
    "destination": "SAN",
    "distance": 304,
    "dep_delay": 0
  },
  {
    "id2": 12653577,
    "tail_num": "N521SW",
    "dep_time": {
      "value": "2002-01-28T06:30:00.000Z"
    },
    "carrier": "WN",
    "origin": "SJC",
    "destination": "LAS",
    "distance": 386,
    "dep_delay": 0
  },
  {
    "id2": 14507879,
    "tail_num": "N534AU",
    "dep_time": {
      "value": "2002-05-04T17:22:00.000Z"
    },
    "carrier": "US",
    "origin": "GSO",
    "destination": "PHL",
    "distance": 365,
    "dep_delay": -10
  },
  {
    "id2": 15121554,
    "tail_num": "N390US",
    "dep_time": {
      "value": "2002-07-09T21:21:00.000Z"
    },
    "carrier": "US",
    "origin": "PHL",
    "destination": "BOS",
    "distance": 280,
    "dep_delay": 111
  },
  {
    "id2": 15584159,
    "tail_num": "N384US",
    "dep_time": {
      "value": "2002-08-06T19:28:00.000Z"
    },
    "carrier": "US",
    "origin": "PHL",
    "destination": "BOS",
    "distance": 280,
    "dep_delay": -2
  },
  {
    "id2": 3260909,
    "tail_num": "N709AS",
    "dep_time": {
      "value": "2000-06-30T11:04:00.000Z"
    },
    "carrier": "AS",
    "origin": "FAI",
    "destination": "ANC",
    "distance": 261,
    "dep_delay": 3
  },
  {
    "id2": 11980600,
    "tail_num": "N5EVAA",
    "dep_time": {
      "value": "2001-11-03T23:53:00.000Z"
    },
    "carrier": "AA",
    "origin": "OGG",
    "destination": "SJC",
    "distance": 2356,
    "dep_delay": -3
  },
  {
    "id2": 13786638,
    "tail_num": "N639SW",
    "dep_time": {
      "value": "2002-04-28T18:22:00.000Z"
    },
    "carrier": "WN",
    "origin": "OAK",
    "destination": "BUR",
    "distance": 325,
    "dep_delay": 7
  },
  {
    "id2": 15030219,
    "tail_num": "N5EDAA",
    "dep_time": {
      "value": "2002-06-30T22:54:00.000Z"
    },
    "carrier": "AA",
    "origin": "OGG",
    "destination": "SJC",
    "distance": 2356,
    "dep_delay": -3
  }
]
SELECT 
   flights.id2 as id2,
   flights.tail_num as tail_num,
   flights.dep_time as dep_time,
   flights.carrier as carrier,
   flights.origin as origin,
   flights.destination as destination,
   flights.distance as distance,
   flights.dep_delay as dep_delay
FROM `malloy-data.faa.flights` as flights
LIMIT 10

Declare a Join

Join carriers to flights. Each flight has one carrier so we use join_one:. (Join Documentation)

source: carriers is table('malloy-data.faa.carriers') {
  measure: carrier_count is count()
}

source: flights is table('malloy-data.faa.flights') {

  join_one: carriers on carrier=carriers.code

  measure:
    flight_count is count()
    total_distance is distance.sum()
    avg_distance is distance.avg()
}

Query the joined tables

using the above declared flights source

query: flights -> {
  group_by: carriers.nickname
  aggregate:
    flight_count
    total_distance
    avg_distance
}
QUERY RESULTS
nicknameflight_​counttotal_​distanceavg_​distance
Southwest5,775,7773,164,821,254547.947
Delta4,477,9293,555,868,325794.088
American4,434,7274,600,336,7691,037.344
United3,654,6463,662,207,1081,002.069
USAir3,205,8102,028,654,008632.805
[
  {
    "nickname": "Southwest",
    "flight_count": 5775777,
    "total_distance": 3164821254,
    "avg_distance": 547.9472725487863
  },
  {
    "nickname": "Delta",
    "flight_count": 4477929,
    "total_distance": 3555868325,
    "avg_distance": 794.087696566874
  },
  {
    "nickname": "American",
    "flight_count": 4434727,
    "total_distance": 4600336769,
    "avg_distance": 1037.3438475468722
  },
  {
    "nickname": "United",
    "flight_count": 3654646,
    "total_distance": 3662207108,
    "avg_distance": 1002.0689029799347
  },
  {
    "nickname": "USAir",
    "flight_count": 3205810,
    "total_distance": 2028654008,
    "avg_distance": 632.8054401227822
  }
]
SELECT 
   carriers_0.nickname as nickname,
   COUNT( 1) as flight_count,
   COALESCE(SUM(flights.distance),0) as total_distance,
   AVG(flights.distance) as avg_distance
FROM `malloy-data.faa.flights` as flights
LEFT JOIN `malloy-data.faa.carriers` AS carriers_0
  ON flights.carrier=carriers_0.code
GROUP BY 1
ORDER BY 2 desc

Aggregates can be computed from anywhere in the Join Tree

(Aggregate Documentation)

using the above declared flights source

query: flights -> {
  limit: 10
  group_by: origin
  aggregate: carriers.carrier_count   // <-- calculation in joined table
  nest: top_3_carriers is {
    limit: 3
    group_by: carriers.nickname
    aggregate:
        flight_count
        total_distance
        avg_distance
  }
}
QUERY RESULTS
origincarrier_​counttop_​3_​carriers
PIT18
nicknameflight_​counttotal_​distanceavg_​distance
USAir365,242235,417,589644.552
Delta16,5848,076,264486.991
American Eagle13,7446,081,950442.517
LAS18
nicknameflight_​counttotal_​distanceavg_​distance
Southwest372,939229,891,249616.431
America West183,300170,982,747932.803
United73,56055,236,501750.904
LAX18
nicknameflight_​counttotal_​distanceavg_​distance
United272,209350,134,4831,286.271
Southwest244,123138,837,996568.721
American205,587322,929,2401,570.767
SFO18
nicknameflight_​counttotal_​distanceavg_​distance
United338,723389,424,4401,149.684
Sky West105,58129,036,546275.017
American82,468131,660,0221,596.498
MCO18
nicknameflight_​counttotal_​distanceavg_​distance
Southwest133,270106,890,437802.059
Delta127,579115,754,539907.317
USAir75,20257,936,063770.406
[
  {
    "origin": "PIT",
    "carrier_count": 18,
    "top_3_carriers": [
      {
        "nickname": "USAir",
        "flight_count": 365242,
        "total_distance": 235417589,
        "avg_distance": 644.5523488536362
      },
      {
        "nickname": "Delta",
        "flight_count": 16584,
        "total_distance": 8076264,
        "avg_distance": 486.99131693198274
      },
      {
        "nickname": "American Eagle",
        "flight_count": 13744,
        "total_distance": 6081950,
        "avg_distance": 442.5167345750872
      }
    ]
  },
  {
    "origin": "LAS",
    "carrier_count": 18,
    "top_3_carriers": [
      {
        "nickname": "Southwest",
        "flight_count": 372939,
        "total_distance": 229891249,
        "avg_distance": 616.4312367438112
      },
      {
        "nickname": "America West",
        "flight_count": 183300,
        "total_distance": 170982747,
        "avg_distance": 932.8027659574467
      },
      {
        "nickname": "United",
        "flight_count": 73560,
        "total_distance": 55236501,
        "avg_distance": 750.9040375203914
      }
    ]
  },
  {
    "origin": "LAX",
    "carrier_count": 18,
    "top_3_carriers": [
      {
        "nickname": "United",
        "flight_count": 272209,
        "total_distance": 350134483,
        "avg_distance": 1286.2707809073177
      },
      {
        "nickname": "Southwest",
        "flight_count": 244123,
        "total_distance": 138837996,
        "avg_distance": 568.7214887577161
      },
      {
        "nickname": "American",
        "flight_count": 205587,
        "total_distance": 322929240,
        "avg_distance": 1570.766828641889
      }
    ]
  },
  {
    "origin": "SFO",
    "carrier_count": 18,
    "top_3_carriers": [
      {
        "nickname": "United",
        "flight_count": 338723,
        "total_distance": 389424440,
        "avg_distance": 1149.684078140546
      },
      {
        "nickname": "Sky West",
        "flight_count": 105581,
        "total_distance": 29036546,
        "avg_distance": 275.01677385135577
      },
      {
        "nickname": "American",
        "flight_count": 82468,
        "total_distance": 131660022,
        "avg_distance": 1596.4983023718294
      }
    ]
  },
  {
    "origin": "MCO",
    "carrier_count": 18,
    "top_3_carriers": [
      {
        "nickname": "Southwest",
        "flight_count": 133270,
        "total_distance": 106890437,
        "avg_distance": 802.0592556464319
      },
      {
        "nickname": "Delta",
        "flight_count": 127579,
        "total_distance": 115754539,
        "avg_distance": 907.3165568001003
      },
      {
        "nickname": "USAir",
        "flight_count": 75202,
        "total_distance": 57936063,
        "avg_distance": 770.4058801627617
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1) THEN
      flights.origin
      END as origin__0,
    CASE WHEN group_set=0 THEN
      COUNT(DISTINCT carriers_0.__distinct_key)
      END as carrier_count__0,
    CASE WHEN group_set=1 THEN
      carriers_0.nickname
      END as nickname__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as flight_count__1,
    CASE WHEN group_set=1 THEN
      COALESCE(SUM(flights.distance),0)
      END as total_distance__1,
    CASE WHEN group_set=1 THEN
      AVG(flights.distance)
      END as avg_distance__1
  FROM `malloy-data.faa.flights` as flights
  LEFT JOIN (SELECT GENERATE_UUID() as __distinct_key, * FROM `malloy-data.faa.carriers`) AS carriers_0
    ON flights.carrier=carriers_0.code
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  GROUP BY 1,2,4
)
SELECT
  origin__0 as origin,
  ANY_VALUE(CASE WHEN group_set=0 THEN carrier_count__0 END) as carrier_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    nickname__1 as nickname, 
    flight_count__1 as flight_count, 
    total_distance__1 as total_distance, 
    avg_distance__1 as avg_distance
    ) END IGNORE NULLS  ORDER BY  flight_count__1 desc  LIMIT 3) as top_3_carriers
FROM __stage0
GROUP BY 1
ORDER BY 2 desc
LIMIT 10

More Complex Joins

The most common join pattern is a foreign key join. Malloy uses the with: to declare these and generates more efficient SQL when these joins are used.

In the example below, we use a with: join for carriers and then model the more complex relationship with the flights originating from each airport using on:.

Many flights have the same airport as their origin so we use join_many:.

source: carriers is table('malloy-data.faa.carriers') {
  primary_key: code
  measure: carrier_count is count()
}

source: flights is table('malloy-data.faa.flights') {

  join_one: carriers with carrier  // <-- each flight has 1 carrier

  measure:
    flight_count is count()
    total_distance is distance.sum()
    avg_distance is distance.avg()
}

source: airports is table('malloy-data.faa.airports') {

  join_many: flights on code = flights.origin  // <-- each airport has many flights

  measure: airport_count is count()
  dimension: elevation_in_meters is elevation * 0.3048
  measure: avg_elevation_in_meters is elevation_in_meters.avg()

  query: by_state is {
    group_by: state
    aggregate: airport_count
  }
}

Calculations work properly regardless of where you are in the graph

This query is very difficult to express in SQL. Malloy's understanding of source relationships allows it to compute aggregate computations at any node of the join path,unlike SQL which can only do aggregate computation at the. outermost level. (Aggregate Documentation)

using the above declared airports source

query: airports ->  {
  group_by: state
  aggregate:
    flights.carriers.carrier_count  // <-- 3 levels
    flights.flight_count
    flights.total_distance
    airport_count
    avg_elevation_in_meters         // <-- symmetric calculation
}
QUERY RESULTS
statecarrier_​countflight_​counttotal_​distanceairport_​countavg_​elevation_​in_​meters
CA214,502,4053,874,427,129984331.533
FL192,355,7391,993,257,62485620.533
NV191,043,575853,747,7681281,228.337
AZ181,174,6641,021,474,320319838.983
CO18977,541827,579,6124251,906.787
[
  {
    "state": "CA",
    "carrier_count": 21,
    "flight_count": 4502405,
    "total_distance": 3874427129,
    "airport_count": 984,
    "avg_elevation_in_meters": 331.5331902439024
  },
  {
    "state": "FL",
    "carrier_count": 19,
    "flight_count": 2355739,
    "total_distance": 1993257624,
    "airport_count": 856,
    "avg_elevation_in_meters": 20.533407476635514
  },
  {
    "state": "NV",
    "carrier_count": 19,
    "flight_count": 1043575,
    "total_distance": 853747768,
    "airport_count": 128,
    "avg_elevation_in_meters": 1228.33685625
  },
  {
    "state": "AZ",
    "carrier_count": 18,
    "flight_count": 1174664,
    "total_distance": 1021474320,
    "airport_count": 319,
    "avg_elevation_in_meters": 838.9825429467085
  },
  {
    "state": "CO",
    "carrier_count": 18,
    "flight_count": 977541,
    "total_distance": 827579612,
    "airport_count": 425,
    "avg_elevation_in_meters": 1906.787203764706
  }
]
SELECT 
   airports.state as state,
   COUNT(DISTINCT carriers_0.code) as carrier_count,
   COUNT( 1) as flight_count,
   COALESCE(SUM(flights_0.distance),0) as total_distance,
   COUNT(DISTINCT airports.__distinct_key) as airport_count,
   (CAST((
    (
      SUM(DISTINCT
        (CAST(ROUND(COALESCE((airports.elevation*0.3048),0)*(1*1.0), 9) AS NUMERIC) +
        (cast(cast(concat('0x', substr(to_hex(md5(CAST(airports.__distinct_key AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST(airports.__distinct_key AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001
      ))
      -
       SUM(DISTINCT (cast(cast(concat('0x', substr(to_hex(md5(CAST(airports.__distinct_key AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST(airports.__distinct_key AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001)
    )/(1*1.0)) as FLOAT64))/NULLIF(COUNT(DISTINCT airports.__distinct_key),0) as avg_elevation_in_meters
FROM (SELECT GENERATE_UUID() as __distinct_key, * FROM `malloy-data.faa.airports` as x) as airports
LEFT JOIN `malloy-data.faa.flights` AS flights_0
  ON airports.code=flights_0.origin
LEFT JOIN `malloy-data.faa.carriers` AS carriers_0
  ON carriers_0.code=flights_0.carrier
GROUP BY 1
ORDER BY 2 desc

Pipelines

The output of a query can be used as the source for the next query.

Assume the following query as a starting point.


source: airports is table('malloy-data.faa.airports') {
  measure: airport_count is count()
}

query: airports -> {
  where: fac_type = 'HELIPORT'
  group_by: state
  aggregate: airport_count
  nest: top_3_county is {
    limit: 3
    group_by: county
    aggregate: airport_count
  }
}
QUERY RESULTS
stateairport_​counttop_​3_​county
TX435
countyairport_​count
HARRIS110
TARRANT35
DALLAS32
CA396
countyairport_​count
LOS ANGELES151
ORANGE47
SAN BERNARDINO24
PA307
countyairport_​count
MONTGOMERY29
ALLEGHENY22
PHILADELPHIA22
FL280
countyairport_​count
PALM BEACH30
DADE27
ORANGE24
NJ247
countyairport_​count
MIDDLESEX26
MONMOUTH23
SOMERSET23
IL245
countyairport_​count
COOK44
DU PAGE17
LAKE12
LA229
countyairport_​count
PLAQUEMINES20
JEFFERSON17
VERMILION15
OH201
countyairport_​count
CUYAHOGA22
FRANKLIN20
LUCAS13
CO165
countyairport_​count
DENVER17
JEFFERSON17
LARIMER9
NY156
countyairport_​count
SUFFOLK18
NASSAU14
DUTCHESS12
WA130
countyairport_​count
KING38
PIERCE11
SNOHOMISH10
MO126
countyairport_​count
ST LOUIS23
JACKSON14
GREENE7
VA126
countyairport_​count
FAIRFAX10
VIRGINIA BEACH8
WISE7
MA126
countyairport_​count
MIDDLESEX35
WORCESTER16
ESSEX16
IN115
countyairport_​count
MARION20
LAKE7
ALLEN5
AZ106
countyairport_​count
MARICOPA68
COCONINO8
PIMA8
GA103
countyairport_​count
FULTON17
GWINNETT9
DE KALB9
OR100
countyairport_​count
MULTNOMAH17
WASHINGTON8
MARION8
OK92
countyairport_​count
OKLAHOMA18
TULSA14
DELAWARE7
CT89
countyairport_​count
HARTFORD30
NEW HAVEN20
FAIRFIELD18
TN87
countyairport_​count
DAVIDSON12
SHELBY12
KNOX7
MI87
countyairport_​count
OAKLAND18
WAYNE12
KENT8
IA85
countyairport_​count
LINN5
POLK5
WOODBURY3
WI85
countyairport_​count
MILWAUKEE5
DANE4
KENOSHA4
AR80
countyairport_​count
PULASKI8
BENTON4
CARROLL4
AL75
countyairport_​count
JEFFERSON14
DALE7
MOBILE6
NC70
countyairport_​count
MECKLENBURG9
CUMBERLAND4
WAKE4
MD64
countyairport_​count
BALTIMORE17
PRINCE GEORGES7
MONTGOMERY7
KY55
countyairport_​count
JEFFERSON9
FAYETTE5
WARREN2
NH52
countyairport_​count
MERRIMACK14
ROCKINGHAM13
HILLSBOROUGH11
MN51
countyairport_​count
HENNEPIN9
ST LOUIS5
OLMSTED3
MS49
countyairport_​count
HARRISON8
JACKSON6
HINDS6
UT43
countyairport_​count
SALT LAKE18
GRAND4
UTAH4
KS37
countyairport_​count
SEDGWICK6
JOHNSON3
WYANDOTTE3
ID36
countyairport_​count
KOOTENAI8
ADA5
BLAINE3
NE34
countyairport_​count
DOUGLAS9
LANCASTER5
MADISON2
WV33
countyairport_​count
RALEIGH5
KANAWHA5
MASON3
AK30
countyairport_​count
ANCHORAGE6
MATA-SUS BOROUGH4
JUNEAU3
NV29
countyairport_​count
CLARK18
WASHOE7
NYE2
MT29
countyairport_​count
MISSOULA4
CASCADE3
FLATHEAD3
SC27
countyairport_​count
RICHLAND5
BEAUFORT2
HAMPTON1
SD26
countyairport_​count
PENNINGTON3
CUSTER2
MINNEHAHA2
NM25
countyairport_​count
BERNALILLO6
SANTA FE3
SIERRA2
WY24
countyairport_​count
LARAMIE4
TETON4
PARK3
PR20
countyairport_​count
--PUERTO RICO20
VT19
countyairport_​count
CHITTENDEN4
RUTLAND2
ORLEANS2
ME18
countyairport_​count
YORK3
WASHINGTON3
SOMERSET2
DC18
countyairport_​count
WASHINGTON18
RI16
countyairport_​count
PROVIDENCE7
WASHINGTON5
NEWPORT3
HI16
countyairport_​count
HONOLULU8
HAWAII6
KAUAI1
ND16
countyairport_​count
BURLEIGH4
EMMONS3
MERCER1
DE13
countyairport_​count
NEW CASTLE7
KENT5
SUSSEX1
CQ7
countyairport_​count
N MARIANA ISLANDS7
VI4
countyairport_​count
-VIRGIN ISLANDS-4
GU1
countyairport_​count
GUAM1
[
  {
    "state": "TX",
    "airport_count": 435,
    "top_3_county": [
      {
        "county": "HARRIS",
        "airport_count": 110
      },
      {
        "county": "TARRANT",
        "airport_count": 35
      },
      {
        "county": "DALLAS",
        "airport_count": 32
      }
    ]
  },
  {
    "state": "CA",
    "airport_count": 396,
    "top_3_county": [
      {
        "county": "LOS ANGELES",
        "airport_count": 151
      },
      {
        "county": "ORANGE",
        "airport_count": 47
      },
      {
        "county": "SAN BERNARDINO",
        "airport_count": 24
      }
    ]
  },
  {
    "state": "PA",
    "airport_count": 307,
    "top_3_county": [
      {
        "county": "MONTGOMERY",
        "airport_count": 29
      },
      {
        "county": "ALLEGHENY",
        "airport_count": 22
      },
      {
        "county": "PHILADELPHIA",
        "airport_count": 22
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 280,
    "top_3_county": [
      {
        "county": "PALM BEACH",
        "airport_count": 30
      },
      {
        "county": "DADE",
        "airport_count": 27
      },
      {
        "county": "ORANGE",
        "airport_count": 24
      }
    ]
  },
  {
    "state": "NJ",
    "airport_count": 247,
    "top_3_county": [
      {
        "county": "MIDDLESEX",
        "airport_count": 26
      },
      {
        "county": "MONMOUTH",
        "airport_count": 23
      },
      {
        "county": "SOMERSET",
        "airport_count": 23
      }
    ]
  },
  {
    "state": "IL",
    "airport_count": 245,
    "top_3_county": [
      {
        "county": "COOK",
        "airport_count": 44
      },
      {
        "county": "DU PAGE",
        "airport_count": 17
      },
      {
        "county": "LAKE",
        "airport_count": 12
      }
    ]
  },
  {
    "state": "LA",
    "airport_count": 229,
    "top_3_county": [
      {
        "county": "PLAQUEMINES",
        "airport_count": 20
      },
      {
        "county": "JEFFERSON",
        "airport_count": 17
      },
      {
        "county": "VERMILION",
        "airport_count": 15
      }
    ]
  },
  {
    "state": "OH",
    "airport_count": 201,
    "top_3_county": [
      {
        "county": "CUYAHOGA",
        "airport_count": 22
      },
      {
        "county": "FRANKLIN",
        "airport_count": 20
      },
      {
        "county": "LUCAS",
        "airport_count": 13
      }
    ]
  },
  {
    "state": "CO",
    "airport_count": 165,
    "top_3_county": [
      {
        "county": "DENVER",
        "airport_count": 17
      },
      {
        "county": "JEFFERSON",
        "airport_count": 17
      },
      {
        "county": "LARIMER",
        "airport_count": 9
      }
    ]
  },
  {
    "state": "NY",
    "airport_count": 156,
    "top_3_county": [
      {
        "county": "SUFFOLK",
        "airport_count": 18
      },
      {
        "county": "NASSAU",
        "airport_count": 14
      },
      {
        "county": "DUTCHESS",
        "airport_count": 12
      }
    ]
  },
  {
    "state": "WA",
    "airport_count": 130,
    "top_3_county": [
      {
        "county": "KING",
        "airport_count": 38
      },
      {
        "county": "PIERCE",
        "airport_count": 11
      },
      {
        "county": "SNOHOMISH",
        "airport_count": 10
      }
    ]
  },
  {
    "state": "MO",
    "airport_count": 126,
    "top_3_county": [
      {
        "county": "ST LOUIS",
        "airport_count": 23
      },
      {
        "county": "JACKSON",
        "airport_count": 14
      },
      {
        "county": "GREENE",
        "airport_count": 7
      }
    ]
  },
  {
    "state": "VA",
    "airport_count": 126,
    "top_3_county": [
      {
        "county": "FAIRFAX",
        "airport_count": 10
      },
      {
        "county": "VIRGINIA BEACH",
        "airport_count": 8
      },
      {
        "county": "WISE",
        "airport_count": 7
      }
    ]
  },
  {
    "state": "MA",
    "airport_count": 126,
    "top_3_county": [
      {
        "county": "MIDDLESEX",
        "airport_count": 35
      },
      {
        "county": "WORCESTER",
        "airport_count": 16
      },
      {
        "county": "ESSEX",
        "airport_count": 16
      }
    ]
  },
  {
    "state": "IN",
    "airport_count": 115,
    "top_3_county": [
      {
        "county": "MARION",
        "airport_count": 20
      },
      {
        "county": "LAKE",
        "airport_count": 7
      },
      {
        "county": "ALLEN",
        "airport_count": 5
      }
    ]
  },
  {
    "state": "AZ",
    "airport_count": 106,
    "top_3_county": [
      {
        "county": "MARICOPA",
        "airport_count": 68
      },
      {
        "county": "COCONINO",
        "airport_count": 8
      },
      {
        "county": "PIMA",
        "airport_count": 8
      }
    ]
  },
  {
    "state": "GA",
    "airport_count": 103,
    "top_3_county": [
      {
        "county": "FULTON",
        "airport_count": 17
      },
      {
        "county": "GWINNETT",
        "airport_count": 9
      },
      {
        "county": "DE KALB",
        "airport_count": 9
      }
    ]
  },
  {
    "state": "OR",
    "airport_count": 100,
    "top_3_county": [
      {
        "county": "MULTNOMAH",
        "airport_count": 17
      },
      {
        "county": "WASHINGTON",
        "airport_count": 8
      },
      {
        "county": "MARION",
        "airport_count": 8
      }
    ]
  },
  {
    "state": "OK",
    "airport_count": 92,
    "top_3_county": [
      {
        "county": "OKLAHOMA",
        "airport_count": 18
      },
      {
        "county": "TULSA",
        "airport_count": 14
      },
      {
        "county": "DELAWARE",
        "airport_count": 7
      }
    ]
  },
  {
    "state": "CT",
    "airport_count": 89,
    "top_3_county": [
      {
        "county": "HARTFORD",
        "airport_count": 30
      },
      {
        "county": "NEW HAVEN",
        "airport_count": 20
      },
      {
        "county": "FAIRFIELD",
        "airport_count": 18
      }
    ]
  },
  {
    "state": "TN",
    "airport_count": 87,
    "top_3_county": [
      {
        "county": "DAVIDSON",
        "airport_count": 12
      },
      {
        "county": "SHELBY",
        "airport_count": 12
      },
      {
        "county": "KNOX",
        "airport_count": 7
      }
    ]
  },
  {
    "state": "MI",
    "airport_count": 87,
    "top_3_county": [
      {
        "county": "OAKLAND",
        "airport_count": 18
      },
      {
        "county": "WAYNE",
        "airport_count": 12
      },
      {
        "county": "KENT",
        "airport_count": 8
      }
    ]
  },
  {
    "state": "IA",
    "airport_count": 85,
    "top_3_county": [
      {
        "county": "LINN",
        "airport_count": 5
      },
      {
        "county": "POLK",
        "airport_count": 5
      },
      {
        "county": "WOODBURY",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "WI",
    "airport_count": 85,
    "top_3_county": [
      {
        "county": "MILWAUKEE",
        "airport_count": 5
      },
      {
        "county": "DANE",
        "airport_count": 4
      },
      {
        "county": "KENOSHA",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "AR",
    "airport_count": 80,
    "top_3_county": [
      {
        "county": "PULASKI",
        "airport_count": 8
      },
      {
        "county": "BENTON",
        "airport_count": 4
      },
      {
        "county": "CARROLL",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "AL",
    "airport_count": 75,
    "top_3_county": [
      {
        "county": "JEFFERSON",
        "airport_count": 14
      },
      {
        "county": "DALE",
        "airport_count": 7
      },
      {
        "county": "MOBILE",
        "airport_count": 6
      }
    ]
  },
  {
    "state": "NC",
    "airport_count": 70,
    "top_3_county": [
      {
        "county": "MECKLENBURG",
        "airport_count": 9
      },
      {
        "county": "CUMBERLAND",
        "airport_count": 4
      },
      {
        "county": "WAKE",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "MD",
    "airport_count": 64,
    "top_3_county": [
      {
        "county": "BALTIMORE",
        "airport_count": 17
      },
      {
        "county": "PRINCE GEORGES",
        "airport_count": 7
      },
      {
        "county": "MONTGOMERY",
        "airport_count": 7
      }
    ]
  },
  {
    "state": "KY",
    "airport_count": 55,
    "top_3_county": [
      {
        "county": "JEFFERSON",
        "airport_count": 9
      },
      {
        "county": "FAYETTE",
        "airport_count": 5
      },
      {
        "county": "WARREN",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "NH",
    "airport_count": 52,
    "top_3_county": [
      {
        "county": "MERRIMACK",
        "airport_count": 14
      },
      {
        "county": "ROCKINGHAM",
        "airport_count": 13
      },
      {
        "county": "HILLSBOROUGH",
        "airport_count": 11
      }
    ]
  },
  {
    "state": "MN",
    "airport_count": 51,
    "top_3_county": [
      {
        "county": "HENNEPIN",
        "airport_count": 9
      },
      {
        "county": "ST LOUIS",
        "airport_count": 5
      },
      {
        "county": "OLMSTED",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "MS",
    "airport_count": 49,
    "top_3_county": [
      {
        "county": "HARRISON",
        "airport_count": 8
      },
      {
        "county": "JACKSON",
        "airport_count": 6
      },
      {
        "county": "HINDS",
        "airport_count": 6
      }
    ]
  },
  {
    "state": "UT",
    "airport_count": 43,
    "top_3_county": [
      {
        "county": "SALT LAKE",
        "airport_count": 18
      },
      {
        "county": "GRAND",
        "airport_count": 4
      },
      {
        "county": "UTAH",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "KS",
    "airport_count": 37,
    "top_3_county": [
      {
        "county": "SEDGWICK",
        "airport_count": 6
      },
      {
        "county": "JOHNSON",
        "airport_count": 3
      },
      {
        "county": "WYANDOTTE",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "ID",
    "airport_count": 36,
    "top_3_county": [
      {
        "county": "KOOTENAI",
        "airport_count": 8
      },
      {
        "county": "ADA",
        "airport_count": 5
      },
      {
        "county": "BLAINE",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "NE",
    "airport_count": 34,
    "top_3_county": [
      {
        "county": "DOUGLAS",
        "airport_count": 9
      },
      {
        "county": "LANCASTER",
        "airport_count": 5
      },
      {
        "county": "MADISON",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "WV",
    "airport_count": 33,
    "top_3_county": [
      {
        "county": "RALEIGH",
        "airport_count": 5
      },
      {
        "county": "KANAWHA",
        "airport_count": 5
      },
      {
        "county": "MASON",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "AK",
    "airport_count": 30,
    "top_3_county": [
      {
        "county": "ANCHORAGE",
        "airport_count": 6
      },
      {
        "county": "MATA-SUS BOROUGH",
        "airport_count": 4
      },
      {
        "county": "JUNEAU",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "NV",
    "airport_count": 29,
    "top_3_county": [
      {
        "county": "CLARK",
        "airport_count": 18
      },
      {
        "county": "WASHOE",
        "airport_count": 7
      },
      {
        "county": "NYE",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "MT",
    "airport_count": 29,
    "top_3_county": [
      {
        "county": "MISSOULA",
        "airport_count": 4
      },
      {
        "county": "CASCADE",
        "airport_count": 3
      },
      {
        "county": "FLATHEAD",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "SC",
    "airport_count": 27,
    "top_3_county": [
      {
        "county": "RICHLAND",
        "airport_count": 5
      },
      {
        "county": "BEAUFORT",
        "airport_count": 2
      },
      {
        "county": "HAMPTON",
        "airport_count": 1
      }
    ]
  },
  {
    "state": "SD",
    "airport_count": 26,
    "top_3_county": [
      {
        "county": "PENNINGTON",
        "airport_count": 3
      },
      {
        "county": "CUSTER",
        "airport_count": 2
      },
      {
        "county": "MINNEHAHA",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "NM",
    "airport_count": 25,
    "top_3_county": [
      {
        "county": "BERNALILLO",
        "airport_count": 6
      },
      {
        "county": "SANTA FE",
        "airport_count": 3
      },
      {
        "county": "SIERRA",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "WY",
    "airport_count": 24,
    "top_3_county": [
      {
        "county": "LARAMIE",
        "airport_count": 4
      },
      {
        "county": "TETON",
        "airport_count": 4
      },
      {
        "county": "PARK",
        "airport_count": 3
      }
    ]
  },
  {
    "state": "PR",
    "airport_count": 20,
    "top_3_county": [
      {
        "county": "--PUERTO RICO",
        "airport_count": 20
      }
    ]
  },
  {
    "state": "VT",
    "airport_count": 19,
    "top_3_county": [
      {
        "coun