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 }
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 }
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 inaggregate:
elements in a query stageA
dimension:
is a declared scalar calculation which that can be used ingroup_by:
orproject:
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 }
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 }
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
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
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 }
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 } }
state | airport_​count | by_​facility_​type | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
TX | 1,845 |
| ||||||||||||||||
CA | 984 |
| ||||||||||||||||
IL | 890 |
| ||||||||||||||||
FL | 856 |
| ||||||||||||||||
PA | 804 |
|
[ { "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 }
faa_​region | airport_​count | top_​5_​states | by_​facility_​type | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AGL | 4,437 |
|
| ||||||||||||||||||||||||||||
ASW | 3,268 |
|
| ||||||||||||||||||||||||||||
ASO | 2,924 |
|
| ||||||||||||||||||||||||||||
AEA | 2,586 |
|
| ||||||||||||||||||||||||||||
ANM | 2,102 |
|
|
[ { "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 }
faa_​region | airport_​count | by_​state_​and_​county | by_​facility_​type | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AGL | 4,437 |
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ASW | 3,268 |
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ASO | 2,924 |
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
AEA | 2,586 |
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ANM | 2,102 |
|
|
[ { "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 }
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 }
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 }
state | airport_​count | by_​facility_​type | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
TX | 1,845 |
| ||||||||||||||||
CA | 984 |
| ||||||||||||||||
IL | 890 |
| ||||||||||||||||
FL | 856 |
| ||||||||||||||||
PA | 804 |
|
[ { "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 }
fac_​type | airport_​count | top_​5_​states | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AIRPORT | 13,925 |
| ||||||||||||
HELIPORT | 5,135 |
| ||||||||||||
SEAPLANE BASE | 473 |
| ||||||||||||
ULTRALIGHT | 125 |
| ||||||||||||
STOLPORT | 86 |
|
[ { "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: * }
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 }
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 }
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
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 } }
origin | carrier_​count | top_​3_​carriers | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
PIT | 18 |
| ||||||||||||||||
LAS | 18 |
| ||||||||||||||||
LAX | 18 |
| ||||||||||||||||
SFO | 18 |
| ||||||||||||||||
MCO | 18 |
|
[ { "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 }
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 } }
state | airport_​count | top_​3_​county | ||||||||
---|---|---|---|---|---|---|---|---|---|---|
TX | 435 |
| ||||||||
CA | 396 |
| ||||||||
PA | 307 |
| ||||||||
FL | 280 |
| ||||||||
NJ | 247 |
| ||||||||
IL | 245 |
| ||||||||
LA | 229 |
| ||||||||
OH | 201 |
| ||||||||
CO | 165 |
| ||||||||
NY | 156 |
| ||||||||
WA | 130 |
| ||||||||
MO | 126 |
| ||||||||
VA | 126 |
| ||||||||
MA | 126 |
| ||||||||
IN | 115 |
| ||||||||
AZ | 106 |
| ||||||||
GA | 103 |
| ||||||||
OR | 100 |
| ||||||||
OK | 92 |
| ||||||||
CT | 89 |
| ||||||||
TN | 87 |
| ||||||||
MI | 87 |
| ||||||||
IA | 85 |
| ||||||||
WI | 85 |
| ||||||||
AR | 80 |
| ||||||||
AL | 75 |
| ||||||||
NC | 70 |
| ||||||||
MD | 64 |
| ||||||||
KY | 55 |
| ||||||||
NH | 52 |
| ||||||||
MN | 51 |
| ||||||||
MS | 49 |
| ||||||||
UT | 43 |
| ||||||||
KS | 37 |
| ||||||||
ID | 36 |
| ||||||||
NE | 34 |
| ||||||||
WV | 33 |
| ||||||||
AK | 30 |
| ||||||||
NV | 29 |
| ||||||||
MT | 29 |
| ||||||||
SC | 27 |
| ||||||||
SD | 26 |
| ||||||||
NM | 25 |
| ||||||||
WY | 24 |
| ||||||||
PR | 20 |
| ||||||||
VT | 19 |
| ||||||||
ME | 18 |
| ||||||||
DC | 18 |
| ||||||||
RI | 16 |
| ||||||||
HI | 16 |
| ||||||||
ND | 16 |
| ||||||||
DE | 13 |
| ||||||||
CQ | 7 |
| ||||||||
VI | 4 |
| ||||||||
GU | 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