What Is Malloy
Joins
Nested views are views included in other views. When a nested view is used in a query, it generates an aggregating subquery, which produces a subtable per row in the view in which it is embedded. The term nested query is often used to refer to a query which uses a nested view.
run: airports -> { group_by: state aggregate: airport_count nest: by_facility is { group_by: fac_type aggregate: airport_count } }
[ { "state": "TX", "airport_count": 1845, "by_facility": [ { "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": [ { "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": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "IL", "airport_count": 890, "by_facility": [ { "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": "STOLPORT", "airport_count": 2 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 2 } ] }, { "state": "FL", "airport_count": 856, "by_facility": [ { "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": [ { "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": "GLIDERPORT", "airport_count": 3 }, { "fac_type": "STOLPORT", "airport_count": 3 } ] } ]
WITH __stage0 AS ( SELECT group_set, base."state" as "state__0", CASE WHEN group_set=0 THEN COUNT(1) END as "airport_count__0", CASE WHEN group_set=1 THEN base."fac_type" END as "fac_type__1", CASE WHEN group_set=1 THEN COUNT(1) END as "airport_count__1" FROM '../data/airports.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2,4 ) SELECT "state__0" as "state", MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count", COALESCE(LIST({ "fac_type": "fac_type__1", "airport_count": "airport_count__1"} ORDER BY "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_facility" FROM __stage0 GROUP BY 1 ORDER BY 2 desc NULLS LAST
Nesting Nested Views
Aggregating subqueries can be nested infinitely, meaning that a nested view can contain another nested view.
run: airports -> { group_by: state aggregate: airport_count nest: top_5_counties is { limit: 5 group_by: county aggregate: airport_count nest: by_facility is { group_by: fac_type aggregate: airport_count } } }
[ { "state": "TX", "airport_count": 1845, "top_5_counties": [ { "county": "HARRIS", "airport_count": 135, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 110 }, { "fac_type": "AIRPORT", "airport_count": 25 } ] }, { "county": "TARRANT", "airport_count": 63, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 35 }, { "fac_type": "AIRPORT", "airport_count": 26 }, { "fac_type": "ULTRALIGHT", "airport_count": 1 }, { "fac_type": "STOLPORT", "airport_count": 1 } ] }, { "county": "DENTON", "airport_count": 53, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 47 }, { "fac_type": "HELIPORT", "airport_count": 6 } ] }, { "county": "DALLAS", "airport_count": 42, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 32 }, { "fac_type": "AIRPORT", "airport_count": 9 }, { "fac_type": "STOLPORT", "airport_count": 1 } ] }, { "county": "BEXAR", "airport_count": 40, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 24 }, { "fac_type": "HELIPORT", "airport_count": 16 } ] } ] }, { "state": "CA", "airport_count": 984, "top_5_counties": [ { "county": "LOS ANGELES", "airport_count": 176, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 151 }, { "fac_type": "AIRPORT", "airport_count": 23 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 } ] }, { "county": "SAN BERNARDINO", "airport_count": 71, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 47 }, { "fac_type": "HELIPORT", "airport_count": 24 } ] }, { "county": "ORANGE", "airport_count": 53, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 47 }, { "fac_type": "AIRPORT", "airport_count": 6 } ] }, { "county": "SAN DIEGO", "airport_count": 49, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 30 }, { "fac_type": "HELIPORT", "airport_count": 17 }, { "fac_type": "GLIDERPORT", "airport_count": 2 } ] }, { "county": "KERN", "airport_count": 49, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 41 }, { "fac_type": "HELIPORT", "airport_count": 7 }, { "fac_type": "ULTRALIGHT", "airport_count": 1 } ] } ] }, { "state": "IL", "airport_count": 890, "top_5_counties": [ { "county": "COOK", "airport_count": 51, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 44 }, { "fac_type": "AIRPORT", "airport_count": 7 } ] }, { "county": "LA SALLE", "airport_count": 39, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 35 }, { "fac_type": "HELIPORT", "airport_count": 4 } ] }, { "county": "MC HENRY", "airport_count": 29, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 20 }, { "fac_type": "HELIPORT", "airport_count": 7 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 } ] }, { "county": "DE KALB", "airport_count": 27, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 24 }, { "fac_type": "HELIPORT", "airport_count": 3 } ] }, { "county": "LEE", "airport_count": 24, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 18 }, { "fac_type": "HELIPORT", "airport_count": 5 }, { "fac_type": "ULTRALIGHT", "airport_count": 1 } ] } ] }, { "state": "FL", "airport_count": 856, "top_5_counties": [ { "county": "PALM BEACH", "airport_count": 45, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 30 }, { "fac_type": "AIRPORT", "airport_count": 14 }, { "fac_type": "GLIDERPORT", "airport_count": 1 } ] }, { "county": "DADE", "airport_count": 44, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 27 }, { "fac_type": "AIRPORT", "airport_count": 12 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 1 } ] }, { "county": "POLK", "airport_count": 43, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 18 }, { "fac_type": "HELIPORT", "airport_count": 16 }, { "fac_type": "SEAPLANE BASE", "airport_count": 9 } ] }, { "county": "MARION", "airport_count": 37, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 27 }, { "fac_type": "HELIPORT", "airport_count": 7 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 1 } ] }, { "county": "ORANGE", "airport_count": 36, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 24 }, { "fac_type": "AIRPORT", "airport_count": 8 }, { "fac_type": "SEAPLANE BASE", "airport_count": 4 } ] } ] }, { "state": "PA", "airport_count": 804, "top_5_counties": [ { "county": "BUCKS", "airport_count": 55, "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 32 }, { "fac_type": "HELIPORT", "airport_count": 19 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 1 }, { "fac_type": "GLIDERPORT", "airport_count": 1 } ] }, { "county": "MONTGOMERY", "airport_count": 44, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 29 }, { "fac_type": "AIRPORT", "airport_count": 14 }, { "fac_type": "SEAPLANE BASE", "airport_count": 1 } ] }, { "county": "ALLEGHENY", "airport_count": 31, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 22 }, { "fac_type": "AIRPORT", "airport_count": 8 }, { "fac_type": "SEAPLANE BASE", "airport_count": 1 } ] }, { "county": "CHESTER", "airport_count": 27, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 16 }, { "fac_type": "AIRPORT", "airport_count": 11 } ] }, { "county": "PHILADELPHIA", "airport_count": 26, "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 22 }, { "fac_type": "AIRPORT", "airport_count": 4 } ] } ] } ]
WITH __stage0 AS ( SELECT group_set, base."state" as "state__0", CASE WHEN group_set=0 THEN COUNT(1) END as "airport_count__0", CASE WHEN group_set IN (1,2) THEN base."county" END as "county__1", CASE WHEN group_set=1 THEN COUNT(1) END as "airport_count__1", CASE WHEN group_set=2 THEN base."fac_type" END as "fac_type__2", CASE WHEN group_set=2 THEN COUNT(1) END as "airport_count__2" FROM '../data/airports.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set ) as group_set GROUP BY 1,2,4,6 ) , __stage1 AS ( SELECT CASE WHEN group_set=2 THEN 1 ELSE group_set END as group_set, "state__0" as "state__0", FIRST("airport_count__0") FILTER (WHERE "airport_count__0" IS NOT NULL) as "airport_count__0", CASE WHEN group_set IN (1,2) THEN "county__1" END as "county__1", FIRST("airport_count__1") FILTER (WHERE "airport_count__1" IS NOT NULL) as "airport_count__1", COALESCE(LIST({ "fac_type": "fac_type__2", "airport_count": "airport_count__2"} ORDER BY "airport_count__2" desc NULLS LAST) FILTER (WHERE group_set=2),[]) as "by_facility__1" FROM __stage0 GROUP BY 1,2,4 ) SELECT "state__0" as "state", MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count", COALESCE(LIST({ "county": "county__1", "airport_count": "airport_count__1", "by_facility": "by_facility__1"} ORDER BY "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:5],[]) as "top_5_counties" FROM __stage1 GROUP BY 1 ORDER BY 2 desc NULLS LAST
Filtering Nested Views
Filters can be applied at any level within nested views.
run: airports -> { where: state ? 'CA' | 'NY' | 'MN' group_by: state aggregate: airport_count nest: top_5_counties is { limit: 5 group_by: county aggregate: airport_count nest: major_facilities is { where: major = 'Y' group_by: name is concat(code, ' - ', full_name) } nest: by_facility is { group_by: fac_type aggregate: airport_count } } }
[ { "state": "CA", "airport_count": 984, "top_5_counties": [ { "county": "LOS ANGELES", "airport_count": 176, "major_facilities": [ { "name": "BUR - BURBANK-GLENDALE-PASADENA" }, { "name": "LAX - LOS ANGELES INTL" }, { "name": "LGB - LONG BEACH /DAUGHERTY FIELD/" } ], "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 151 }, { "fac_type": "AIRPORT", "airport_count": 23 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 } ] }, { "county": "SAN BERNARDINO", "airport_count": 71, "major_facilities": [ { "name": "ONT - ONTARIO INTL" } ], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 47 }, { "fac_type": "HELIPORT", "airport_count": 24 } ] }, { "county": "ORANGE", "airport_count": 53, "major_facilities": [ { "name": "SNA - JOHN WAYNE AIRPORT-ORANGE COUNTY" } ], "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 47 }, { "fac_type": "AIRPORT", "airport_count": 6 } ] }, { "county": "SAN DIEGO", "airport_count": 49, "major_facilities": [ { "name": "SAN - SAN DIEGO INTL-LINDBERGH FLD" } ], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 30 }, { "fac_type": "HELIPORT", "airport_count": 17 }, { "fac_type": "GLIDERPORT", "airport_count": 2 } ] }, { "county": "KERN", "airport_count": 49, "major_facilities": [ { "name": "BFL - MEADOWS FIELD" }, { "name": "IYK - INYOKERN" } ], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 41 }, { "fac_type": "HELIPORT", "airport_count": 7 }, { "fac_type": "ULTRALIGHT", "airport_count": 1 } ] } ] }, { "state": "NY", "airport_count": 576, "top_5_counties": [ { "county": "SUFFOLK", "airport_count": 34, "major_facilities": [ { "name": "ISP - LONG ISLAND MAC ARTHUR" } ], "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 18 }, { "fac_type": "AIRPORT", "airport_count": 15 }, { "fac_type": "SEAPLANE BASE", "airport_count": 1 } ] }, { "county": "ERIE", "airport_count": 26, "major_facilities": [ { "name": "BUF - BUFFALO NIAGARA INTL" } ], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 18 }, { "fac_type": "HELIPORT", "airport_count": 8 } ] }, { "county": "DUTCHESS", "airport_count": 20, "major_facilities": [], "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 12 }, { "fac_type": "AIRPORT", "airport_count": 8 } ] }, { "county": "NIAGARA", "airport_count": 20, "major_facilities": [], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 15 }, { "fac_type": "HELIPORT", "airport_count": 5 } ] }, { "county": "ONEIDA", "airport_count": 18, "major_facilities": [], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 13 }, { "fac_type": "HELIPORT", "airport_count": 5 } ] } ] }, { "state": "MN", "airport_count": 507, "top_5_counties": [ { "county": "ST LOUIS", "airport_count": 28, "major_facilities": [ { "name": "DLH - DULUTH INTL" } ], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 13 }, { "fac_type": "SEAPLANE BASE", "airport_count": 10 }, { "fac_type": "HELIPORT", "airport_count": 5 } ] }, { "county": "HENNEPIN", "airport_count": 23, "major_facilities": [ { "name": "MSP - MINNEAPOLIS-ST PAUL INTL/WOLD-CHAMBERLAIN/" } ], "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 9 }, { "fac_type": "SEAPLANE BASE", "airport_count": 8 }, { "fac_type": "AIRPORT", "airport_count": 6 } ] }, { "county": "CROW WING", "airport_count": 17, "major_facilities": [], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 8 }, { "fac_type": "SEAPLANE BASE", "airport_count": 6 }, { "fac_type": "STOLPORT", "airport_count": 1 }, { "fac_type": "ULTRALIGHT", "airport_count": 1 }, { "fac_type": "HELIPORT", "airport_count": 1 } ] }, { "county": "DAKOTA", "airport_count": 17, "major_facilities": [], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 14 }, { "fac_type": "SEAPLANE BASE", "airport_count": 3 } ] }, { "county": "ANOKA", "airport_count": 14, "major_facilities": [], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 9 }, { "fac_type": "SEAPLANE BASE", "airport_count": 3 }, { "fac_type": "HELIPORT", "airport_count": 2 } ] } ] } ]
WITH __stage0 AS ( SELECT group_set, base."state" as "state__0", CASE WHEN group_set=0 THEN COUNT(1) END as "airport_count__0", CASE WHEN group_set IN (1,2,3) THEN base."county" END as "county__1", CASE WHEN group_set=1 THEN COUNT(1) END as "airport_count__1", CASE WHEN group_set=2 THEN CONCAT(base."code",' - ',base."full_name") END as "name__2", CASE WHEN group_set=3 THEN base."fac_type" END as "fac_type__3", CASE WHEN group_set=3 THEN COUNT(1) END as "airport_count__3" FROM '../data/airports.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,3,1)) as group_set ) as group_set WHERE (base."state" IN ('CA','NY','MN')) AND ((group_set NOT IN (2) OR (group_set IN (2) AND base."major"='Y'))) GROUP BY 1,2,4,6,7 ) , __stage1 AS ( SELECT CASE WHEN group_set=2 THEN 1 WHEN group_set=3 THEN 1 ELSE group_set END as group_set, "state__0" as "state__0", FIRST("airport_count__0") FILTER (WHERE "airport_count__0" IS NOT NULL) as "airport_count__0", CASE WHEN group_set IN (1,2,3) THEN "county__1" END as "county__1", FIRST("airport_count__1") FILTER (WHERE "airport_count__1" IS NOT NULL) as "airport_count__1", COALESCE(LIST({ "name": "name__2"} ORDER BY "name__2" asc NULLS LAST) FILTER (WHERE group_set=2),[]) as "major_facilities__1", COALESCE(LIST({ "fac_type": "fac_type__3", "airport_count": "airport_count__3"} ORDER BY "airport_count__3" desc NULLS LAST) FILTER (WHERE group_set=3),[]) as "by_facility__1" FROM __stage0 GROUP BY 1,2,4 ) SELECT "state__0" as "state", MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count", COALESCE(LIST({ "county": "county__1", "airport_count": "airport_count__1", "major_facilities": "major_facilities__1", "by_facility": "by_facility__1"} ORDER BY "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:5],[]) as "top_5_counties" FROM __stage1 GROUP BY 1 ORDER BY 2 desc NULLS LAST
Nesting Named Views
Any views defined in a source can be used by name in a nest: clause or a query.
source: airports_with_views is airports extend { view: by_facility is { group_by: fac_type aggregate: airport_count } view: major_facilities is { where: major = 'Y' group_by: name is concat(code, ' - ', full_name) } view: top_5_counties is { limit: 5 group_by: county nest: major_facilities by_facility } } run: airports_with_views -> top_5_counties
[ { "county": "--PUERTO RICO", "major_facilities": [ { "name": "BQN - RAFAEL HERNANDEZ" }, { "name": "SJU - LUIS MUNOZ MARIN INTL" } ], "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 20 }, { "fac_type": "AIRPORT", "airport_count": 19 }, { "fac_type": "SEAPLANE BASE", "airport_count": 1 } ] }, { "county": "-TRUST TERR. OF PAC-", "major_facilities": [], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 1 } ] }, { "county": "-VIRGIN ISLANDS-", "major_facilities": [ { "name": "STT - CYRIL E KING" }, { "name": "STX - HENRY E ROHLSEN" } ], "by_facility": [ { "fac_type": "HELIPORT", "airport_count": 4 }, { "fac_type": "SEAPLANE BASE", "airport_count": 3 }, { "fac_type": "AIRPORT", "airport_count": 2 } ] }, { "county": "ABBEVILLE", "major_facilities": [], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 3 }, { "fac_type": "HELIPORT", "airport_count": 1 } ] }, { "county": "ACADIA", "major_facilities": [], "by_facility": [ { "fac_type": "AIRPORT", "airport_count": 9 }, { "fac_type": "HELIPORT", "airport_count": 3 } ] } ]
WITH __stage0 AS ( SELECT group_set, base."county" as "county__0", CASE WHEN group_set=1 THEN CONCAT(base."code",' - ',base."full_name") END as "name__1", CASE WHEN group_set=2 THEN base."fac_type" END as "fac_type__2", CASE WHEN group_set=2 THEN COUNT(1) END as "airport_count__2" FROM '../data/airports.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set ) as group_set WHERE (group_set NOT IN (1) OR (group_set IN (1) AND base."major"='Y')) GROUP BY 1,2,3,4 ) SELECT "county__0" as "county", COALESCE(LIST({ "name": "name__1"} ORDER BY "name__1" asc NULLS LAST) FILTER (WHERE group_set=1),[]) as "major_facilities", COALESCE(LIST({ "fac_type": "fac_type__2", "airport_count": "airport_count__2"} ORDER BY "airport_count__2" desc NULLS LAST) FILTER (WHERE group_set=2),[]) as "by_facility" FROM __stage0 GROUP BY 1 ORDER BY 1 asc NULLS LAST LIMIT 5