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

code	full_name	state	faa_region	fac_type	elevation
00A	TOTAL RF	PA	AEA	HELIPORT	11
00C	ANIMAS AIR PARK	CO	ANM	AIRPORT	6,684
00CA	GOLDSTONE /GTS/	CA	AWP	AIRPORT	3,038
00E	AT&T - APACHE JUNCTION	AZ	AWP	HELIPORT	2,527
00F	TCJC-NORTHEAST CAMPUS	TX	ASW	HELIPORT	600
00FD	RINGHAVER	FL	ASO	HELIPORT	25
00FL	RIVER OAK	FL	ASO	AIRPORT	35
00GA	LT WORLD	GA	ASO	AIRPORT	700
00I	CHAPMAN MEMORIAL FIELD	OH	AGL	AIRPORT	1,180
00II	BAILEY GENERATION STATION	IN	AGL	HELIPORT	600
00IL	HAMMER	IL	AGL	AIRPORT	840
00IN	ST MARY MEDICAL CENTER	IN	AGL	AIRPORT	634
00IS	HAYENGA'S CANT FIND FARMS	IL	AGL	AIRPORT	820
00J	GEORGIA-PACIFIC	GA	ASO	AIRPORT	133
00K	REDPATH RLA	IL	AGL	AIRPORT	470
00KS	HAYDEN FARM	KS	ACE	AIRPORT	1,100
00L	LYALL-ROBERTS	CA	AWP	AIRPORT	925
00LL	AC & R COMPONENTS	IL	AGL	HELIPORT	600
00M	THIGPEN FIELD	MS	ASO	AIRPORT	351
00MI	DOW CHEMICAL	MI	AGL	HELIPORT	588
00MN	BATTLE LAKE MUNI	MN	AGL	AIRPORT	1,365
00N	BUCKS	NJ	AEA	AIRPORT	105
00NC	NORTH RALEIGH	NC	ASO	AIRPORT	348
00NJ	COLGATE-PISCATAWAY	NJ	AEA	HELIPORT	78
00OI	MIAMI VALLEY HOSPITAL	OH	AGL	HELIPORT	905
00OR	STEEL SYSTEMS	OR	ANM	HELIPORT	195
00PA	R J D	PA	AEA	HELIPORT	402
00PS	THOMAS FIELD	PA	AEA	AIRPORT	800
00Q	THAYER AVIATION	CA	AWP	AIRPORT	54
00R	LIVINGSTON MUNI	TX	ASW	AIRPORT	151
00S	MC KENZIE BRIDGE STATE	OR	ANM	AIRPORT	1,620
00TA	SW REGION FAA	TX	ASW	HELIPORT	598
00TS	ALPINE RANGE	TX	ASW	AIRPORT	670
00V	MEADOW LAKE	CO	ANM	AIRPORT	6,874
00VA	CITY	VA	AEA	HELIPORT	12
00W	LOWER GRANITE STATE	WA	ANM	AIRPORT	719
00WA	HOWELL	WA	ANM	AIRPORT	160
00WI	NORTHERN LITE	WI	AGL	AIRPORT	860
00X	DORAL RESORT & COUNTRY CLUB	FL	ASO	HELIPORT	6
00XS	L P ASKEW FARMS	TX	ASW	AIRPORT	3,110
00Y	KAPAUN-WILSON FIELD	MN	AGL	AIRPORT	1,122
01A	PURKEYPILE	AK	AAL	AIRPORT	1,950
01AK	PROVIDENCE SEWARD MEDICAL CENTER	AK	AAL	HELIPORT	120
01AZ	YAT	AZ	AWP	HELIPORT	3,300
01B	WOODSTOCK	CT	ANE	AIRPORT	465
01C	GRANT	MI	AGL	AIRPORT	815
01CA	LUGO SUBSTATION	CA	AWP	HELIPORT	3,733
01CO	ST VINCENT GENERAL HOSPITAL	CO	ANM	HELIPORT	10,175
01CT	BERLIN FAIRGROUNDS	CT	ANE	HELIPORT	60
01FD	FLORIDA HOSPITAL-ALTAMONTE	FL	ASO	HELIPORT	86
01FL	CEDAR KNOLL FLYING RANCH	FL	ASO	AIRPORT	19
01G	PERRY-WARSAW	NY	AEA	AIRPORT	1,559
01GA	MEDICAL CENTER	GA	ASO	HELIPORT	319
01H	MC CHRISTY	IL	AGL	AIRPORT	630
01I	AUGUST ACRES	OH	AGL	AIRPORT	1,100
01II	MYERS FIELD	IN	AGL	AIRPORT	950
01IL	HOOPESTON COMMUNITY MEMORIAL HOSPITAL	IL	AGL	HELIPORT	583
01J	HILLIARD AIRPARK	FL	ASO	AIRPORT	59
01K	CALDWELL MUNI	KS	ACE	AIRPORT	1,157
01KS	FLYING N RANCH	KS	ACE	AIRPORT	1,485
01KY	LOURDES HOSPITAL	KY	ASO	HELIPORT	419
01LA	BARHAM	LA	ASW	AIRPORT	90
01LL	SCHUMAIER RLA	IL	AGL	AIRPORT	555
01M	TISHOMINGO COUNTY	MS	ASO	AIRPORT	578
01MA	COMPAQ ANDOVER	MA	ANE	HELIPORT	140
01ME	SAINT PETER'S	ME	ANE	SEAPLANE BASE	608
01MI	FLOW THROUGH TERMINAL	MI	AGL	HELIPORT	736
01MN	BARNES	MN	AGL	SEAPLANE BASE	1,358
01MO	HIGHWAY PATROL TROOP C HEADQUARTERS	MO	ACE	HELIPORT	615
01NC	TOPSAIL AIRPARK	NC	ASO	AIRPORT	65
01NE	DETOUR	NE	ACE	AIRPORT	3,000
01NH	MOORE AIRFIELD	NH	ANE	AIRPORT	835
01NJ	ALBERT GUIDO MEMORIAL	NJ	AEA	HELIPORT	10
01NY	VASSAR HOSPITAL	NY	AEA	HELIPORT	100
01OH	GILLMOR	OH	AGL	AIRPORT	665
01OI	GALION COMMUNITY HOSPITAL	OH	AGL	HELIPORT	1,140
01OK	LAWRENCE	OK	ASW	AIRPORT	1,525
01OR	RED & WHITE FLYING SERVICE	OR	ANM	AIRPORT	4,346
01PA	PINE	PA	AEA	HELIPORT	1,215
01PN	BIERLY(PERSONAL USE)	PA	AEA	AIRPORT	960
01PS	NORT'S RESORT	PA	AEA	AIRPORT	1,060
01TA	RED ADAIR	TX	ASW	HELIPORT	90
01TS	ST JOSEPH HOSPITAL	TX	ASW	HELIPORT	675
01TX	MIMS FARM	TX	ASW	ULTRALIGHT	610
01U	DUCKWATER	NV	AWP	AIRPORT	5,124
01V	AURORA AIRPARK	CO	ANM	AIRPORT	5,680
01VA	OEHDA	VA	AEA	AIRPORT	500
01W	SAWYER	VA	AEA	STOLPORT	13
01WA	WILLAPA HARBOR	WA	ANM	HELIPORT	154
01WI	PREHN CRANBERRY COMPANY	WI	AGL	AIRPORT	930
01X	JACKSON MEMORIAL HOSPITAL	FL	ASO	HELIPORT	53
01XS	MEADOWOOD RANCH	TX	ASW	HELIPORT	500
02A	GRAGG-WADE FIELD	AL	ASO	AIRPORT	586
02AK	RUSTIC WILDERNESS	AK	AAL	AIRPORT	190
02AZ	WESTCHESTER FARM AIRSTRIP	AZ	AWP	AIRPORT	4,200
02C	CAPITOL	WI	AGL	AIRPORT	850
02CA	SWEPI BETA PLATFORM ELLEN	CA	AWP	HELIPORT	122
02CO	MC CULLOUGH	CO	ANM	AIRPORT	7,615
02CT	STRANGERS POINT	CT	ANE	HELIPORT	540
02E	MESA LUTHERAN HOSPITAL	AZ	AWP	HELIPORT	1,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_type	airport_count
AIRPORT	569
HELIPORT	396
SEAPLANE BASE	12
GLIDERPORT	3
STOLPORT	2
ULTRALIGHT	2

[
  {
    "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

state	airport_count	avg_elevation_in_meters
TX	435	138.687
CA	396	276.402
PA	307	216.255
FL	280	18.101
NJ	247	48.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_county	airport_count
WY - WESTON	3
WY - WASHAKIE	4
WY - UINTA	3
WY - TETON	7
WY - SWEETWATER	4

[
  {
    "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

state	airport_count
TX	1,845
CA	984
IL	890
FL	856
PA	804

[
  {
    "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

state	airport_count
AK	104
MN	72
FL	43
ME	38
NY	23

[
  {
    "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

state	airport_count	heliport_count
TX	1,845	435
CA	984	396
IL	890	245
FL	856	280
PA	804	307

[
  {
    "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

state

airport_count

by_facility_type

1,845

fac_type	airport_count
AIRPORT	1,389
HELIPORT	435
STOLPORT	8
ULTRALIGHT	8
GLIDERPORT	5

984

fac_type	airport_count
AIRPORT	569
HELIPORT	396
SEAPLANE BASE	12
GLIDERPORT	3
STOLPORT	2
ULTRALIGHT	2

890

fac_type	airport_count
AIRPORT	625
HELIPORT	245
SEAPLANE BASE	8
ULTRALIGHT	6
BALLOONPORT	2
STOLPORT	2
GLIDERPORT	2

856

fac_type	airport_count
AIRPORT	511
HELIPORT	280
SEAPLANE BASE	43
STOLPORT	13
ULTRALIGHT	5
GLIDERPORT	4

804

fac_type	airport_count
AIRPORT	468
HELIPORT	307
ULTRALIGHT	13
SEAPLANE BASE	10
STOLPORT	3
GLIDERPORT	3

[
  {
    "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_region

airport_count

top_5_states

by_facility_type

AGL

4,437

state	airport_count
IL	890
OH	749
IN	643
WI	543
MN	507

fac_type	airport_count
AIRPORT	3,443
HELIPORT	826
SEAPLANE BASE	119
ULTRALIGHT	30
STOLPORT	11
GLIDERPORT	4
BALLOONPORT	4

ASW

3,268

state	airport_count
TX	1,845
LA	500
OK	443
AR	299
NM	181

fac_type	airport_count
AIRPORT	2,341
HELIPORT	861
ULTRALIGHT	32
SEAPLANE BASE	19
STOLPORT	9
GLIDERPORT	6

ASO

2,924

state	airport_count
FL	856
GA	440
NC	400
TN	285
AL	260

fac_type	airport_count
AIRPORT	2,038
HELIPORT	770
SEAPLANE BASE	57
STOLPORT	33
ULTRALIGHT	17
GLIDERPORT	8
BALLOONPORT	1

AEA

2,586

state	airport_count
PA	804
NY	576
VA	421
NJ	378
MD	229

fac_type	airport_count
AIRPORT	1,525
HELIPORT	964
SEAPLANE BASE	61
ULTRALIGHT	18
STOLPORT	8
GLIDERPORT	7
BALLOONPORT	3

ANM

2,102

state	airport_count
WA	484
OR	441
CO	425
MT	259
ID	238

fac_type	airport_count
AIRPORT	1,524
HELIPORT	527
SEAPLANE BASE	25
STOLPORT	13
ULTRALIGHT	8
GLIDERPORT	4
BALLOONPORT	1

[
  {
    "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_region

airport_count

by_state_and_county

by_facility_type

AGL

4,437

state

airport_count

by_county

890

county	airport_count
COOK	51
LA SALLE	39
MC HENRY	29
DE KALB	27

749

county	airport_count
CUYAHOGA	27
FRANKLIN	27
STARK	23
MONTGOMERY	22

643

county	airport_count
MARION	27
ALLEN	24
HAMILTON	20
MARSHALL	18

543

county	airport_count
DANE	30
WALWORTH	22
WINNEBAGO	17
ST CROIX	16

507

county	airport_count
ST LOUIS	28
HENNEPIN	23
DAKOTA	17
CROW WING	17

489

county	airport_count
OAKLAND	25
KENT	24
WAYNE	17
LIVINGSTON	15

436

county	airport_count
CASS	39
RICHLAND	20
MC LEAN	20
WARD	19

180

county	airport_count
PENNINGTON	7
MEADE	7
MINNEHAHA	7
BROWN	6

fac_type	airport_count
AIRPORT	3,443
HELIPORT	826
SEAPLANE BASE	119
ULTRALIGHT	30
STOLPORT	11
GLIDERPORT	4
BALLOONPORT	4

ASW

3,268

state

airport_count

by_county

1,845

county	airport_count
HARRIS	135
TARRANT	63
DENTON	53
DALLAS	42

500

county	airport_count
PLAQUEMINES	31
VERMILION	29
CALCASIEU	23
LAFOURCHE	21

443

county	airport_count
OKLAHOMA	31
TULSA	25
ROGERS	16
DELAWARE	13

299

county	airport_count
PULASKI	20
BENTON	19
LONOKE	13
MISSISSIPPI	10

181

county	airport_count
CATRON	13
LINCOLN	10
LUNA	9
SANTA FE	9

fac_type	airport_count
AIRPORT	2,341
HELIPORT	861
ULTRALIGHT	32
SEAPLANE BASE	19
STOLPORT	9
GLIDERPORT	6

ASO

2,924

state

airport_count

by_county

856

county	airport_count
PALM BEACH	45
DADE	44
POLK	43
MARION	37

440

county	airport_count
FULTON	22
PIKE	17
CARROLL	14
FAYETTE	12

400

county	airport_count
WAKE	15
MECKLENBURG	14
UNION	14
ROWAN	14

285

county	airport_count
SHELBY	24
DAVIDSON	17
KNOX	14
RUTHERFORD	10

260

county	airport_count
BALDWIN	25
JEFFERSON	19
MOBILE	17
MADISON	14

243

county	airport_count
WASHINGTON	15
HARRISON	12
HINDS	11
BOLIVAR	9

202

county	airport_count
JEFFERSON	13
FAYETTE	7
MC LEAN	6
BOONE	6

189

county	airport_count
GREENVILLE	13
LEXINGTON	9
BEAUFORT	9
CHARLESTON	9

county	airport_count
--PUERTO RICO	40

county	airport_count
-VIRGIN ISLANDS-	9

fac_type	airport_count
AIRPORT	2,038
HELIPORT	770
SEAPLANE BASE	57
STOLPORT	33
ULTRALIGHT	17
GLIDERPORT	8
BALLOONPORT	1

AEA

2,586

state

airport_count

by_county

804

county	airport_count
BUCKS	55
MONTGOMERY	44
ALLEGHENY	31
CHESTER	27

576

county	airport_count
SUFFOLK	34
ERIE	26
DUTCHESS	20
NIAGARA	20

421

county	airport_count
FAUQUIER	23
SHENANDOAH	13
FAIRFAX	12
ACCOMACK	12

378

county	airport_count
MONMOUTH	31
MIDDLESEX	29
BURLINGTON	29
HUNTERDON	27

229

county	airport_count
BALTIMORE	24
CARROLL	14
ANNE ARUNDEL	14
PRINCE GEORGES	14

116

county	airport_count
KANAWHA	8
MASON	7
RALEIGH	6
PRESTON	6

county	airport_count
KENT	17
SUSSEX	14
NEW CASTLE	11

county	airport_count
WASHINGTON	18
ARLINGTON	1
LOUDOUN	1

fac_type	airport_count
AIRPORT	1,525
HELIPORT	964
SEAPLANE BASE	61
ULTRALIGHT	18
STOLPORT	8
GLIDERPORT	7
BALLOONPORT	3

ANM

2,102

state

airport_count

by_county

484

county	airport_count
KING	61
PIERCE	27
SPOKANE	26
SNOHOMISH	25

441

county	airport_count
CLACKAMAS	34
LINN	26
LANE	24
WASHINGTON	24

425

county	airport_count
WELD	40
EL PASO	26
ADAMS	23
JEFFERSON	21

259

county	airport_count
FLATHEAD	20
LEWIS AND CLARK	16
MISSOULA	14
GALLATIN	11

238

county	airport_count
VALLEY	27
KOOTENAI	21
IDAHO	18
BONNER	18

140

county	airport_count
SALT LAKE	21
SAN JUAN	13
UTAH	10
IRON	9

115

county	airport_count
LARAMIE	12
CARBON	9
PARK	9
CAMPBELL	9

fac_type	airport_count
AIRPORT	1,524
HELIPORT	527
SEAPLANE BASE	25
STOLPORT	13
ULTRALIGHT	8
GLIDERPORT	4
BALLOONPORT	1

[
  {
    "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

state	airport_count
WY	115
WV	116

[
  {
    "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_type	airport_count	avg_elevation
AIRPORT	13,925	1,237.044
HELIPORT	5,135	950.513
SEAPLANE BASE	473	488.822
ULTRALIGHT	125	806.144
STOLPORT	86	1,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

state

airport_count

by_facility_type

1,845

fac_type	airport_count
AIRPORT	1,389
HELIPORT	435
STOLPORT	8
ULTRALIGHT	8
GLIDERPORT	5

984

fac_type	airport_count
AIRPORT	569
HELIPORT	396
SEAPLANE BASE	12
GLIDERPORT	3
STOLPORT	2
ULTRALIGHT	2

890

fac_type	airport_count
AIRPORT	625
HELIPORT	245
SEAPLANE BASE	8
ULTRALIGHT	6
BALLOONPORT	2
STOLPORT	2
GLIDERPORT	2

856

fac_type	airport_count
AIRPORT	511
HELIPORT	280
SEAPLANE BASE	43
STOLPORT	13
ULTRALIGHT	5
GLIDERPORT	4

804

fac_type	airport_count
AIRPORT	468
HELIPORT	307
ULTRALIGHT	13
SEAPLANE BASE	10
STOLPORT	3
GLIDERPORT	3

[
  {
    "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_type

airport_count

top_5_states

AIRPORT

13,925

state	airport_count
TX	1,389
IL	625
CA	569
OH	537
FL	511

HELIPORT

5,135

state	airport_count
TX	435
CA	396
PA	307
FL	280
NJ	247

SEAPLANE BASE

473

state	airport_count
AK	104
MN	72
FL	43
ME	38
NY	23

ULTRALIGHT

125

state	airport_count
LA	18
IN	17
PA	13
TX	8
AZ	7

STOLPORT

state	airport_count
FL	13
TN	9
TX	8
CO	6
GA	4

[
  {
    "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

code	name	nickname
EV	Atlantic Southeast Airlines	Atlantic Southeast
NW	Northwest Airlines	Northwest
AA	American Airlines	American
FL	Airtran Airways Corporation	Airtran
B6	Jetblue Airways	Jetblue
OO	Sky West Airlines	Sky West
CO	Continental Airlines	Continental
OH	Comair (Delta Connections)	Comair
TW	Trans World Airlines	TWA
HA	Hawaiian Airlines	Hawaiian
US	US Airways	USAir
RU	Continental Express Airlines	Continental Express
WN	Southwest Airlines	Southwest
AQ	Aloha Airlines	Aloha
AS	Alaska Airlines	Alaska
HP	America West Airlines	America West
DL	Delta Air Lines	Delta
DH	Atlantic Coast Airlines	Atlantic Coast
TZ	American Trans Air, Inc.	ATA
UA	United Airlines	United
MQ	American Eagle Airlines	American 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

id2	tail_num	dep_time	carrier	origin	destination	distance	dep_delay
4,999,524	N509SW	2000-09-06 09:50:00	WN	SAN	OAK	446	0
6,521,785	N641SW	2000-12-25 09:10:00	WN	PHX	SAN	304	0
12,653,577	N521SW	2002-01-28 06:30:00	WN	SJC	LAS	386	0
14,507,879	N534AU	2002-05-04 17:22:00	US	GSO	PHL	365	-10
15,121,554	N390US	2002-07-09 21:21:00	US	PHL	BOS	280	111
15,584,159	N384US	2002-08-06 19:28:00	US	PHL	BOS	280	-2
3,260,909	N709AS	2000-06-30 11:04:00	AS	FAI	ANC	261	3
11,980,600	N5EVAA	2001-11-03 23:53:00	AA	OGG	SJC	2,356	-3
13,786,638	N639SW	2002-04-28 18:22:00	WN	OAK	BUR	325	7
15,030,219	N5EDAA	2002-06-30 22:54:00	AA	OGG	SJC	2,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

nickname	flight_count	total_distance	avg_distance
Southwest	5,775,777	3,164,821,254	547.947
Delta	4,477,929	3,555,868,325	794.088
American	4,434,727	4,600,336,769	1,037.344
United	3,654,646	3,662,207,108	1,002.069
USAir	3,205,810	2,028,654,008	632.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

origin

carrier_count

top_3_carriers

PIT

nickname	flight_count	total_distance	avg_distance
USAir	365,242	235,417,589	644.552
Delta	16,584	8,076,264	486.991
American Eagle	13,744	6,081,950	442.517

LAS

nickname	flight_count	total_distance	avg_distance
Southwest	372,939	229,891,249	616.431
America West	183,300	170,982,747	932.803
United	73,560	55,236,501	750.904

LAX

nickname	flight_count	total_distance	avg_distance
United	272,209	350,134,483	1,286.271
Southwest	244,123	138,837,996	568.721
American	205,587	322,929,240	1,570.767

SFO

nickname	flight_count	total_distance	avg_distance
United	338,723	389,424,440	1,149.684
Sky West	105,581	29,036,546	275.017
American	82,468	131,660,022	1,596.498

MCO

nickname	flight_count	total_distance	avg_distance
Southwest	133,270	106,890,437	802.059
Delta	127,579	115,754,539	907.317
USAir	75,202	57,936,063	770.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

state	carrier_count	flight_count	total_distance	airport_count	avg_elevation_in_meters
CA	21	4,502,405	3,874,427,129	984	331.533
FL	19	2,355,739	1,993,257,624	856	20.533
NV	19	1,043,575	853,747,768	128	1,228.337
AZ	18	1,174,664	1,021,474,320	319	838.983
CO	18	977,541	827,579,612	425	1,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

state

airport_count

top_3_county

435

county	airport_count
HARRIS	110
TARRANT	35
DALLAS	32

396

county	airport_count
LOS ANGELES	151
ORANGE	47
SAN BERNARDINO	24

307

county	airport_count
MONTGOMERY	29
ALLEGHENY	22
PHILADELPHIA	22

280

county	airport_count
PALM BEACH	30
DADE	27
ORANGE	24

247

county	airport_count
MIDDLESEX	26
MONMOUTH	23
SOMERSET	23

245

county	airport_count
COOK	44
DU PAGE	17
LAKE	12

229

county	airport_count
PLAQUEMINES	20
JEFFERSON	17
VERMILION	15

201

county	airport_count
CUYAHOGA	22
FRANKLIN	20
LUCAS	13

165

county	airport_count
DENVER	17
JEFFERSON	17
LARIMER	9

156

county	airport_count
SUFFOLK	18
NASSAU	14
DUTCHESS	12

130

county	airport_count
KING	38
PIERCE	11
SNOHOMISH	10

126

county	airport_count
ST LOUIS	23
JACKSON	14
GREENE	7

126

county	airport_count
FAIRFAX	10
VIRGINIA BEACH	8
WISE	7

126

county	airport_count
MIDDLESEX	35
WORCESTER	16
ESSEX	16

115

county	airport_count
MARION	20
LAKE	7
ALLEN	5

106

county	airport_count
MARICOPA	68
COCONINO	8
PIMA	8

103

county	airport_count
FULTON	17
GWINNETT	9
DE KALB	9

100

county	airport_count
MULTNOMAH	17
WASHINGTON	8
MARION	8

county	airport_count
OKLAHOMA	18
TULSA	14
DELAWARE	7

county	airport_count
HARTFORD	30
NEW HAVEN	20
FAIRFIELD	18

county	airport_count
DAVIDSON	12
SHELBY	12
KNOX	7

county	airport_count
OAKLAND	18
WAYNE	12
KENT	8

county	airport_count
LINN	5
POLK	5
WOODBURY	3

county	airport_count
MILWAUKEE	5
DANE	4
KENOSHA	4

county	airport_count
PULASKI	8
BENTON	4
CARROLL	4

county	airport_count
JEFFERSON	14
DALE	7
MOBILE	6

county	airport_count
MECKLENBURG	9
CUMBERLAND	4
WAKE	4

county	airport_count
BALTIMORE	17
PRINCE GEORGES	7
MONTGOMERY	7

county	airport_count
JEFFERSON	9
FAYETTE	5
WARREN	2

county	airport_count
MERRIMACK	14
ROCKINGHAM	13
HILLSBOROUGH	11

county	airport_count
HENNEPIN	9
ST LOUIS	5
OLMSTED	3

county	airport_count
HARRISON	8
JACKSON	6
HINDS	6

county	airport_count
SALT LAKE	18
GRAND	4
UTAH	4

county	airport_count
SEDGWICK	6
JOHNSON	3
WYANDOTTE	3

county	airport_count
KOOTENAI	8
ADA	5
BLAINE	3

county	airport_count
DOUGLAS	9
LANCASTER	5
MADISON	2

county	airport_count
RALEIGH	5
KANAWHA	5
MASON	3

county	airport_count
ANCHORAGE	6
MATA-SUS BOROUGH	4
JUNEAU	3

county	airport_count
CLARK	18
WASHOE	7
NYE	2

county	airport_count
MISSOULA	4
CASCADE	3
FLATHEAD	3

county	airport_count
RICHLAND	5
BEAUFORT	2
HAMPTON	1

county	airport_count
PENNINGTON	3
CUSTER	2
MINNEHAHA	2

county	airport_count
BERNALILLO	6
SANTA FE	3
SIERRA	2

county	airport_count
LARAMIE	4
TETON	4
PARK	3

county	airport_count
--PUERTO RICO	20

county	airport_count
CHITTENDEN	4
RUTLAND	2
ORLEANS	2

county	airport_count
YORK	3
WASHINGTON	3
SOMERSET	2

county	airport_count
WASHINGTON	18

county	airport_count
PROVIDENCE	7
WASHINGTON	5
NEWPORT	3

county	airport_count
HONOLULU	8
HAWAII	6
KAUAI	1

county	airport_count
BURLEIGH	4
EMMONS	3
MERCER	1

county	airport_count
NEW CASTLE	7
KENT	5
SUSSEX	1

county	airport_count
N MARIANA ISLANDS	7

county	airport_count
-VIRGIN ISLANDS-	4

county	airport_count
GUAM	1

[
  {
    "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